SQL Storage Compress 6.0

Walkthrough: Optimizing SQL Server storage with SQL Storage Compress

This walkthrough of SQL Storage Compress will show you how to run a live database from compressed files.

When we open SQL Storage Compress, we get an interface similar to the one below. Under the Database sizes tab, there is a list of all the databases on our local SQL Server, and their sizes. If any of these databases have already been compressed with SQL Storage Compress their compressed size and the space saving is also listed. At the moment, no databases are running from compressed files.

If we switch to the Summary of savings tab, we get a summary of the space savings that SQL Storage Compress has already given us and the potential savings we could make by compressing the uncompressed databases on our server. This is shown as a graph and as a set of figures. Potential savings assume an average compression rate of 80%.

To run a database from compressed files, we have to restore a recent backup of it through SQL Storage Compress. For this walkthrough, we’ll use a backup of our SQLBackupDemo database in SQL Backup's .sqb file format.

To begin restoring a backup to a compressed database, we click the Restore backup to compressed database… button in the top right corner, or the menu above. This starts the Restore Backup to Compressed Database Wizard.

In the Wizard’s first step, we select our SQL Server instance – (local) is the default – and our backup file.

We want to use the (local) SQL Server instance, so we click the Browse... button to select our SQLBackupDemo.sqb backup file, then click Next >. This takes us to the second step of the Wizard. Here, we choose the database name, the directory to which compressed files should be restored, whether to encrypt our compressed files, and whether to run DBCC CHECKDB as part of the restore process.

SQL Storage Compress suggests SQLBackupDemo_SSC as the default name. We’ll stick with this. We’ll also write our compressed files to the Default SQL Server data files. For this walkthrough, we won’t encrypt or verify our database, so all we have to do is click Next >.

This takes us to the third step in the Wizard, where we can review our settings and view the SQL script that SQL Storage Compress will run to restore our backup.

We then click Finish to move to the fourth step of Wizard, which runs our restore.

Once the restore process is complete, the Wizard shows us the sizes of the original and compressed versions of the database, and calculates the space saving as a percentage and as an absolute figure. Here, we’ve achieved a saving of 1.056GB, or 84%.

The restore process is now complete, so we click Close to close the Wizard.

Returning to the Database sizes tab, our information is updated automatically. We now have one compressed database, saving us just over 1GB of storage.

Finally, we’ll open SSMS, to double check that our compressed database is accessible. As we can see, it appears alongside all our other, uncompressed databases, and we can query it just like any other database.

To try SQL Storage Compress for yourself, download a free, 14-day trial.