Shrink SQL Database During Restore

Article
1/17/2024

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!
* Please do not add any of these app, as we are not here to advertise them, but to explain our workaround solution :-)

Resources

Share via