Shrink SQL Database During Restore

Shrink SQL Database During Restore


 

Case Study:

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 :-)

First option: using compressed folder & symbolic link

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).

Step 1: Create a compressed folder

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.

Step 2: Backup your database

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 MB.

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"):

USE [master]
RESTORE DATABASE [AdventureWorksDW2012]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'
 WITH  FILE = 1, 
 MOVE N'AdventureWorksDW2012_Data'
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_Data.mdf',
  MOVE N'AdventureWorksDW2012_Log'
  TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\UnCompressFolder\AdventureWorksDW2012_log.ldf'
  NOUNLOAD,  STATS = 5
GO

Our results:
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:

USE [master]
RESTORE DATABASE [AdventureWorksDW2012]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\AdventureWorksDW2012.bak'
 WITH  FILE = 1, 
 MOVE N'AdventureWorksDW2012_Data'
 TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_Data.mdf',
  MOVE N'AdventureWorksDW2012_Log'
  TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_log.ldf'
  NOUNLOAD,  STATS = 5
GO

Oops … we get this ERROR:

Msg 5118, Level 16, State 3, Line 2The file "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_Data.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Msg 5118, Level 16, State 3, Line 2
The file "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder\AdventureWorksDW2012_log.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Step 3: Create symbolic link to the compressed folder


 SQL Server will not let us work directly with compressed folder.  This is our workaround...

* 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.

Step 4: Restore your database using the symbolic link. 

WOW… this is working great :-)

Step 5: Shrink the Database

Now we can do what we came to do…

Step 6: Detach the database, move files to an uncompressed folder or just uncompressed the current folder, Attach Database.

* 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.

Another option

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!

Resources

This article is based on the Ronen Ariely blog at 

http://ariely.info/Blog/tabid/83/EntryId/118/shrink-SQL-database-during-restore.aspx

Compress Folders and Files

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Carsten Siemens edited Revision 7. Comment: Added tag: has comment

  • Naomi  N edited Revision 3. Comment: Some grammar edits, may need more

Page 1 of 1 (2 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Revision 3. Comment: Some grammar edits, may need more

  • 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?

    Thanks!

  • Hi joe,

    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.

  • Ed Price,

    Thanks for the feedback and comments

    Nice to see the article on the front page of the WIKI :-)

  • Carsten Siemens edited Revision 7. Comment: Added tag: has comment

  • You're welcome!

    Second, this article was featured on MSDN blogs here: blogs.msdn.com/.../sql-server-database-guru-shrink-sql-database-during-restore.aspx

Page 1 of 1 (9 items)