We need to restore a database with the original file size of 100 GB. Our disks on the target computer hold 80 GB. We know that the database can be shrank to less than 80 GB.
The problem is that restore operation always restores the files to the same sizes they were before they were backed up. There is currently no build-in way to "restore with shrink". But… There is
always a BUT :-)
This is an Advanced Action, unofficial and not a supported workaround :-)
We are going to actually cheat the SQL Server in order to get what we need.
DO NOT EVER DO IT IN LIVE PRODUCTION!!!
* Although I did use it several times, but do not tell anyone… This was necessitated by the situation, and it did work for me great. The following process should work (step
by step with images can be seen on this blog).
File compression is one of the features of the NTFS file system. Compression is handled different from archive Zip file. Compressed folder appears like regular folder in the operating system (on some OS the folder name get the color blue) and it can be used
normally by the user, and the operating system.
NTFS compression is only available on volumes that use the NTFS file system, using cluster sizes up to 4 KB. It can be used to compress files and folders. Files are decompressed automatically, and they appear as before in Windows Explorer. By default, NTFS
compressed files and folders use blue color to distinguish them from standard files and folders. Users may experience a performance decrease when working with compressed files, as Windows needs to decompress them before they can be used.
How to Compress Folders
* Create new folder under the SQL server backup folder (by doing so we would know that we have the right permissions)
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder
* Right-click the folder that needs to be compressed
* Select Properties from the context menu.
* Click on the Advanced button in the General tab
* Select Compress contents to save disk space
* Click OK
* Select to apply the changes.
The folder name gets blue color
Remark: Folders and files can be uncompressed the same way. The only difference is that the checkmark in the third step of the process needs to be removed.
Copy the backup file to the compress folder temporary. We can see on both folders (the compressed and the regular) that the file size is the same 200 MB. But closer look at the "size on disk" can show us the difference. The compressed folder is only 82.7
Backup our database directly to the compressed folder working great, but will restore into the compressed folder work?
Let's detach the database and try to restore our database firstly to the
UnCompressed folder, just as checkpoint that we don’t have any problem (I created folder named "UnCompress"):
DISK = N
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'
FILE = 1,
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_Data.mdf'
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_log.ldf'
NOUNLOAD, STATS = 5
Processed 25584 pages for database 'AdventureWorksDW2012', file 'AdventureWorksDW2012_Data' on file 1.
Processed 2 pages for database 'AdventureWorksDW2012', file 'AdventureWorksDW2012_Log' on file 1.
RESTORE DATABASE successfully processed 25586 pages in 5.108 seconds (39.131 MB/sec).
Now let's detach the database and try to restore our database to the Compressed folder:
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_Data.mdf'
'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_log.ldf'
Oops … we get this ERROR:
* Open command shell. Creating a symbolink is done using the command "mklink". Take into consideration that PowerShell is not a complete replacement for CMD. Many CMD functions do not work in PowerShell. Switch to CMD to run mklink!
* use this command:
mklink /D "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\MySymboliclinkFolder" "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder"
Try to navigate to the new symbolink in order to verify it is working OK.
WOW… this is working great :-)
Now we can do what we came to do…
* You can use VSS to copy without Detach/Attach
* Theoretically we can work on the data now but this is very bad idea for live database as each read or write to the file will need to do a compression / decompression action.
There are several third party applications that can let us work on a backup SQL file directly. If those applications allow us to run queries on the database it is probably enable us to run compress. So we can open the backup file -> compress the database
-> restore the database after the compression.* I have never tried this!
Congratulations on winning the gold medal! blogs.technet.com/.../technet-guru-awards-july-2013.aspx
Congratulations on being featured on the home page of TechNet Wiki! social.technet.microsoft.com/.../default.aspx
So, everyone have the right to edit this kind of wiki articles?
Joe, yes. Is there a particular change you wanted to make in this article?
you are more then welcome to add/comment/edit to the article. this is the idea of WIKI, all can edit and not just the original author..
As any WIKI this has disadvantages, for example people who edit might insert mistakes in one hand, but on the other hand this has the advantage of EVOLUTION of the article, and the with time the article comes to be optimal (assuming editing was appropriate).
* If you are not sure you can put a simple note/comment with the Fix/Edit/Add and then someone else (like me, original author) will see the note and decide how and if to change the article according to the note.
Thanks for the feedback and comments
Nice to see the article on the front page of the WIKI :-)
Second, this article was featured on MSDN blogs here: blogs.msdn.com/.../sql-server-database-guru-shrink-sql-database-during-restore.aspx
Thank You ED
i already thanks you there when you post it, but this a good place too :-)