0

This question already has an answer here:

I don't believe this is possible but wanted to check.

I've got a bi-weekly job running to restore the full backup to a test environment so the devs have something fresh to destroy without damaging the actual environment. The problem is cropping up that the test environment has less space than the production environment (it's stuck on one drive instead of a number of drives).

When copied across the database is now using over 95% of the hard disk allocation that it has. Is there any way along side the Restore Database WITH MOVE to resize those files? There is free space in a number of the files that could be shrunk and the file size is never going to change from the backup as it doesn't have data getting thrown at it so technically they can get cut all the way back to their data size.

The alternative, which I don't really want to do, is shrink the files on the production environment that have a bit too much space available at the minute.

Any tips or ideas of anything to do? Adding a new hard disk unfortunately isn't an option as the drive bay attached is full.

Ste

marked as duplicate by Paul White Sep 8 '15 at 11:57

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

No, there is no option to adjust the file size of database files during the restore. You would have to shrink the files in production before taking the backup.

If this is a copy of the database on the development instance you can always tell the developers if they want it to give up space (aka start dropping the other databases on that instances they don't need). Outside of that I would suggest letting the developers tells you what tables they actually need "fresh" data from in production, then just pull that out or a sampling of it out. However, depending on the method they use to develop and test their code they could always come up with their own test data.

  • Yea that's what I thought. unfortunately it is the only database that exists so I can't get any space from anywhere else, guess shrinking files on the Prod server is what I'm going to have to do – Ste Bov Sep 8 '15 at 11:13
1

You are stuck there. There is no option to re-size the files during restore. The backup is made by dumping the data pages directly to the backup device.

There is the option of exporting/importing the data using SSIS but that takes time and it would probably be cheaper to buy a new larger hard drive and take the test server down and exchange it's drive.

Not the answer you're looking for? Browse other questions tagged or ask your own question.