How do I Restore as Compressed with LiteSpeed for SQL Server?
Restoring as compressed in LiteSpeed 7.0 requires you run the LiteSpeed restore script from Management Studio (or use the LiteSpeed command-line). We are planning full user-interface integration in the 7.1 release, but until then it’s a simple matter to restore via script. Here are steps involved:
Create a folder where you want the compressed data files for the restored database to reside. It’s best to allocate a folder specifically for these compressed databases in order to keep them from mingling with read-write databases on the same instance.
Flag the folder for NTFS Compression. Right-click the folder, select Properties, click Advanced, and check the Compress Contents to Save Disk Space option.
Restore the LiteSpeed backup using the new @RestoreAsReadOnly option and use the MOVE parameter to relocate the data/log files to the newly created compressed folder. If you need help creating the restore script, simply use the LiteSpeed Restore Wizard with the correct settings and click the Script option on the last page of the wizard before running the restore. Using that script, simply add the @RestoreAsReadOnly flag as follows:
exec master.dbo.xp_restore_database @database = N'MyDB-Compressed' ,
@filename = N'D:\Backup\MyDB.bak',
@filenumber = 1,
@with = N'STATS = 10',
@with = N'MOVE N''MyDB_Data'' TO N''E:\SQL Server\Compressed\MyDB_Data.mdf''',
@with = N'MOVE N''MyDB_Log'' TO N''E:\SQL Server\Compressed\MyDB_Log.ldf''',
@affinity = 0,
@logging = 0,
@restoreasreadonly=1;
GO
LiteSpeed then restores the backup directly to the compressed NTFS folder. Compression is performed in memory prior to hitting disk. Once the restore is complete, you can check your actual disk savings from Windows. Simply highlight all the data/log files in the folder, right-click and select Properties. In this example, I restored a 33GB+ TPC-E database and storage requirements dropped from 33.5GB down to 7.1GB; a savings of 79%.
PDF document of article attached.
Link to full blog post on Quest Software LiteSpeed for SQL Server Communities site:
