I need to replace the copy of my PROD db in a DEV system on a daily basis.
The problem is that I haven't enough space in my DEV system to restore the PROD db. Is there any way to create a shrinked .bak without make changes on original DB?
Thank you
1
-
What is the version of SQL server you are referring to? Just to add you cannot shrink the backup, it can be compressed though.. – KASQLDBA Jan 11 '16 at 10:23
-
SQLServer 2014.. the problem is not the size of .bak file.. but the size of mdf and ldf that are restored with the original PROD size.. Thank you!! – gipinani Jan 11 '16 at 10:27
-
1Size of MDF being large, you cannot do much about, but if LDF being on the larger side, you can do the shrinking (not recommended, but Ok as this being the DEV one). So please share more details on the size of those MDF and LDF – KASQLDBA Jan 11 '16 at 10:35
-
They are created at a size of 80GB (mdf) / 90GB (ldf). I cannot reduce them in PROD by policy. The bak compressed is 1GB but I cannot restore it in DEV because I have only 50GB of free space. – gipinani Jan 11 '16 at 10:40
-
I know this being a stupid question , but what's the size of the database on prod which you want to restore on DEV (which as per you have backup compressed to 1 GB, if i got that right?) – KASQLDBA Jan 11 '16 at 10:54
2
I just want to avoid to spare disk space if possible. I'm searching for a way to "shrink on the fly" and then create the bak.. but seems to be impossible
Well, general answer here will be NO, there is no easy/simple way to get this done seeing the amount of free space , which is 50GB compared to the database (200GB) whose MDF is 80 GB.
Shrinking on FLY, No again. You have to shrink the file on prod before backing it up or later once restore (which is not the case here, as destination dev is only 50 GB).
It better to go for the option of more space , rather than going a very tedious and complex way ( will be OK, if we can be assured if it will work).
Also, refer to some suggestions/ answered at How to restore a SQL Server database and shrink its files at the same time?
-
I'm solving by restoring the .bak file in a fake compressed VHD drive.. I know it's not the best way, anyway It's going to take about 10 minutes. In that way I decompress my bak file, shrink the db created in the VHD, create .bak again and then restore it in my real drive. – gipinani Jan 11 '16 at 11:43
