Restore a Database as Compressed - A How to Guide

LiteSpeed for SQL Server 7.0 now includes the ability to restore a database in compressed, read-only form. In this blog post, I'll describe how to leverage this feature in your environment and why you'll probably find this a very useful feature.

Problems DBAs Have with Large Databases

As DBAs, we've all run into this situation. A large production database needs to be reviewed by the DBA team in a non-production environment. But there's no room for that 2 TB production database in the non-prod environment. DBAs are then forced to either perform their work on the production database or need to selectively export the data they need to examine using the Import/Export Data feature in SQL Server. Neither is a good solution.

Another issue arises when DBAs need to run database consistency checks on a production database. Running a DBCC CHECKDB, something that all DBAs should run on all databases on schedule, is a time consuming, CPU consuming, and Disk IO consuming process. Maybe you don't have adequate maintenance windows to check the database. Or maybe the IO is too high and affects production workloads. Whatever the reason, we know running database consistency checks is extremely important. Skipping that process is never a good idea.

Both of these use cases could be solved if a database could be restored in compressed form.

How do I Compress my Database with SQL Server

Microsoft supports compressed databases. I don't mean using ROW and PAGE compression inside the database. What I'm referring to is compressing the entire database using NTFS Compression. If you haven’t ever played around with NTFS Compression, it’s a pretty cool feature. Simply flag a file, set of files, or a folder in Windows with the NTFS Compression option, and Windows automatically compresses the data in those files at the file system level. The great thing is that this is completely transparent to applications which access the data.

Some data is more compressible than other data. A folder of Office 2007-2010 files is not going to compress well since those files are actually ZIP files underneath. And ZIP files are already compressed. A folder filled with XML data, however, is highly compressible and you could easily see savings of 70-90% or more in disk space. In the example below, I compressed a folder with 230 HTML and XML files and reduced the storage on disk from 154MB to 44.6MB; a savings of over 70%.

But what does this have to do with SQL Server databases?

Microsoft allows you to place a database in a compressed NTFS folder, but there's a catch. The database must be read-only. If you were to do this manually, you would have to:

  • Create the database (read-write at this point)
  • Create the objects and data
  • Change the database properties to Read Only
  • Detach the database
  • Compress the database files on disk using NTFS Compression
  • Attach the database in SQL Server
  • You can then back up the database and restore it to a compressed NTFS folder on another instance

That’s a fine solution when you have a database that no longer experience writes and needs to be keep around for historical reasons, but what to do with those production databases that are still in use? Couldn’t you just restore the read-write database to an NTFS Compressed folder? Simply put, you can't. SQL Server will not allow a database that was read-write at the time of backup to be restored to a compressed folder. Catch-22. You can't restore compressed unless you're read-only at backup time, but production databases are read-write by nature.

How do I Restore as Compressed with LiteSpeed for SQL Server

With LiteSpeed, the problem preventing a restore to a compressed NTFS volume with native tools is no longer an issue. LiteSpeed can seamlessly restore a database that was read-write at the time of backup to a compressed NTFS folder. What you get is a fully accessible, compressed, read-only database that consumes a fraction of the disk space previously required. You can run queries, examine data and objects, and run important DBCC CHECKDB operations to ensure there are no production database consistency issues, without any impact to the production database.

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

If you want to restore to an instance where LiteSpeed is not installed, you can use Double-Click Restore and its command-line interface:

C:\>foo.exe -R d -D foo2 -W"MOVE 'foo' TO 'C:\compressed\foo.mdf'" -W"MOVE 'foo_log' TO 'C:\compressed\foo_log.LDF'" --RestoreAsReadOnly 1

And that’s all. Restoring your backups as compressed is now something you can easily do. How will you leverage this new feature?

Anonymous