July 4, 2017 at 2:41 pm
The situation:
SQL Server 2016 SP1.
My client has big databases in Production.
Of course, DBAs don't want to shrink the databases in Prod.
As they could always restore the backups, they don't even want the backups to contain shrank DBs.
So they backup the databases and the BI Devs copy and restore those when needed.
The Devs are working in VMs.
Often, the databases contains lots of unused space.
The Devs don't want to manage that unused space in VMs, they don't have space anyway.
The example:
Let's say database Alpha is 500GB in Prod.
Restoring a 500GB database in a VM is time and resource consuming.
When restore is done, the devs have to shrink database Alpha, which would fall down to, let's say, 250GB.
But they often have to restore again when a bug occurs in Prod.
Plus, there are multiple databases, the sum of which don't fit on regular dev disks.
So they have to switch databases based on current development needs.
The questions:
Is it possible to shrink the database files inside a copy of the backup?
Is it possible to shrink a database while restoring it?
Do you have other ideas to manage this situation?
Thanks,
Vince
July 4, 2017 at 4:31 pm
I may be proven wrong, but I am fairly confident that the restore of the database puts it in the exact same state that it was backed up in. That is, there are no options to shrink it while restoring it.
The idea of the backup is to put it back how it is now. So shrinking it (especially if you had compression on the backup) during a restore would be difficult, if not impossible to do. And even if it were possible, the processing required for that would cause the restore to be even slower.
The only thing I am aware of to improve restore times is:
1 - restore from a FULL backup (ie don't restore the TLOGs or differentials). Each extra restore that you do will result in a slower restore.
2 - use instant file initialization
Now I am not saying don't do TLOG or differential backups; those are important. And I wouldn't recommend a full backup during company uptime (if you can avoid it) as it does impact performance. I am just saying when you are doing restores, try to keep the number of files you restore low.
As for post-restore shrinking, what we do where I work is after a restore from live to test, all databases are set to simple recovery and then shrunk. It is a slow process, but it saves us a few hundred GB of disk space.
We have servers set up with similar specs to our LIVE servers for hosting our test/development SQL instances. Nothing worse than having somebody develop a SQL query that runs slow in test/dev and hope that it runs better on live.
RedGate has a tool called SQL Clone that sounds like it would fit your situation nicely though.
(RedGate experts correct me here if I am wrong) I believe it takes a snapshot of your database (which will eat up 500GB of disk space which you can later shrink to 250GB using your example) then you give end users access to that through SQL Clone. Each user can then gets their own version of this snapshot. In their version of the snapshot, they can manipulate the database without affecting each others data. The changes the developer does persist for only them. They get what is essentially a snapshot + THEIR tlogs.
Where I work, this isn't a good solution, but it sounds like it would fit your scenario quite nicely.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply