Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
J4CKO
Starting Member
7 Posts |
 Posted - 2015-03-16 : 06:04:31
|
| Hi, after a bit of advice, we have a production Dynamics AX DB which is in total around a TB. Moving a copy for dev and test purposes is quite a big job and we dont have enough spare resources for 1TB databases despite needing them for various purposes.So, I have an automated procedure I am writing that takes a Copy only backup and restores it onto another server, it then does the following,Sets the restored db to simple recoveryShrinks the logfiles down to the minimumTruncates certain tables that are no longer requiredCompresses every table and index with Page compressionShrinks the resultant DB Backs up the newly slimmed down db This takes the db from taking 1TB to restore to 94 GB which means we can have multiple copies in dev !However, though this works it is pretty long winded, it doesnt help that the storage this machine is on is pretty slow.We will be looking at compression for the prod db which means we may be able to avoid at least some pf that step but need to be careful, that will be a much more targeted solution using row, page or no compression based on the read/write balance. Also, we wouldn't shrink the prod db to release any space it frees, we would just leave it as free space in the data file and eventually I guess it will fill.I suppose the time doesn't matter if it is predictable but just wondering if I am missing any better ways to provide these bite size dev databases ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-03-18 : 08:57:24
|
| If you find a better way I'd love to know about it, because what you describe is what we do too ...Either way, it has to be SHRINK'd before being copied / restored to DEV because RESTORE will make/GROW the database to the same size as the original database/log from which the backup was made - even if the database is empty ... |
 |
|
|
|
|
|
|
|
Compress and Shrink process, any suggestions ?