יול10

Written by: ronen ariely
10/07/2013 22:56 

Case Study:

We need to restore a database which original file size was 100GB. Our disks on the target computer hold 80GB. We know that the database can be shrink to less than 80GB. The problem is that restore operation always restores the files to the same sizes they were before the 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 unofficial and not supported workaround way :-)

DO NOT EVER DO IT IN LIVE!!!

* The following process should work and I used it several times. I will guide you step by step with images for each step. For this demo I am using windows server 2012, SQL server 2012 Enterprise Edition, Database AdventureWorksDW2012.

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 appear 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. 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 before they can be used.

How to Compress Folders

* Create new folder under the SQL server backup folder (in this place we know that we will have the right permissions)

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\CompressFolder

* Right-click the folder that need 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 get blue color 

Remark: Folders and files can be uncompressed the same way. The only different is that the checkmark in third step of the process needs to be removed.

Step 2: backup your database

Looking on the next image we can see that the backup file size is 200MB on both folders (the compress and the regular). But closer look at the "size on disk" can show us the different. The compress folder is only 82.7 MB.

Backup our database directly to the compress folder working great, but will restore into the compress folder work?

Let's detach the database and try to restore our database firstly to the UnCompress folder, just as checkpoint that we don’t have any problem (I create folder named "UnCompress"):

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 Compress folder:

Ops … we get 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 cheating :-)

* 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 inorder to make sure 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, Attach

* 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 application 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 try this!

Resources