Using the wizard to create compressed databases - SQL Storage Compress

Overview

The following worked example shows you how to create compressed databases by restoring from a backup with the Restore backup to compressed database wizard, which is included with the SQL Storage Compress user interface.

The wizard generates and runs a RESTORE DATABASE script that restores the data files (*.mdf and *.ndf) to the SQL Storage Compress compressed file formats (*.mdfx and *.ndfx). You can also use the wizard to generate a script to edit and run manually.

The wizard supports backups in any of the following formats:

• Native SQL Server backup file (*.bak)
• SQL HyperBac compressed backup files (*.hbc and *.hbc2)
• SQL HyperBac encrypted backup files (*.hbe)
• SQL Backup Pro compressed backup files (*.sqb)
• SQL Backup Pro encrypted backup files (*.sqb)
• Quest LiteSpeed™ backup files (unencrypted only) (*.sls and *.bak)

Note: this worked example uses a native SQL Server backup file (*.bak), but the method is similar for all supported backup formats. If you are restoring from one of the other supported formats, see also Restoring from other backup formats below.

Limitations of the wizard

• You can only restore on local SQL Server instances.
• The wizard only supports restores from a single, full backup file. If you want to create a compressed database by restoring from a backup split into multiple files or a combination of backups (for example, a full backup followed by a differential backup), you will need to write a T-SQL script.
• The wizard does not compress the transaction log files (*.ldf) unless you select encryption. This is because the transaction log file generally represents a small proportion of the storage footprint, so compression offers little benefit. Furthermore, in transaction-intensive databases, with frequent reads and writes to the log file, compressing the log file can impair performance. To compress the transaction log file without encrypting it, write a T-SQL script.

Worked example: using the wizard to create a compressed database from a native SQL Server backup file

1. Open the SQL Storage Compress user interface.

   If the HyperBac Control Service isn't running, an error message is shown beneath the database list. You must start the service before you can create or use SQL Storage Compress compressed databases: click the Start the service hyperlink.

   

2. Click Restore backup to compressed database to open the wizard.

   

3. Select a local SQL Server instance, and choose an authentication method.

   This is the instance that will host your compressed database. For this example, we have used (local).

4. Click Browse and select a native SQL Server backup file to restore to a compressed database.

   

   Native SQL Server backup files usually have a .bak extension, but any extension will work, provided the file is a valid native SQL Server backup. For this example we have selected AdventureWorks_Full.bak. For details about restoring from other supported backup formats, see Restoring from other backup formats below.

   Note that only single, full backup files are supported by the wizard. If you want to restore to a compressed database from a backup split into multiple files or a combination of backups (for example, full, differential, and log backups), you will need to write a T-SQL script.

5. Click Next in the wizard.

   The backup file is checked to ensure it is correctly formatted. If the file fails validation, an error message is displayed, (for example, if the backed-up database is incompatible with the local SQL Server version).

   If the file is valid, the wizard automatically moves on to the next step.

6. Specify a name for the compressed database. By default this is the original name of the backed-up database, with _SSC appended. If this name already exists, a number is also appended so that the name is unique. Alternatively, you can enter a name in the Database name box.

   For this example, we have used the default database name AdventureWorks_SSC.

   Note: SQL Storage Compress does not automatically delete the original database when you restore from a backup. To overwrite the original database:

   1. Rename the database to match the original name by deleting the suffix that SQL Storage Compress has added.
   2. Select the checkbox underneath the Database name box to force overwriting of the original database.

   

7. Specify the location for the database files. The files will be restored to new file names, formed of the new database name followed by the existing file name. In this example, the database files will be restored to AdventureWorks_SSC_AdventureWorks_Data.mdfx and AdventureWorks_SSC_AdventureWorks_Log.ldf.

   If you opt to overwrite the original database, the original database files will be deleted.

8. Choose whether to encrypt the database. Selecting this option will result in compression and encryption of the database's transaction log files, as well as the data files.

   Note: the SQL Storage Compress wizard does not compress the transaction log files unless you opt to encrypt the database. In transaction-intensive databases, with frequent reads and writes to the log file, compressing the log file can impair performance. You can read more about encrypting SQL Storage Compress databases by clicking the help icon

9. The wizard includes the option to run a database integrity check after the restore. The results are displayed on the final step of the wizard.

   For this example we will use all the default settings on step 2.

10. Click Next to move on to the next step.

    

    • The Summary tab presents information about the backup file and the new compressed database, including database file names.

      Note that the data file for the new database has extension .mdfx; this is the default file extension for SQL Storage Compress compressed data files.

    • The Script tab shows the T-SQL RESTORE DATABASE script that will be run on the local instance when you click Finish.

      If you want to use the T-SQL script as the basis for a more complex "restore to compressed database" job (for example, if you want to compress only a single data file out of multiple data files), you can copy or save the script for editing in your preferred editor.

11. Click Finish to start restoring the backup file to a new compressed database.

    

    • If the restore process completes successfully, the space savings for the compressed database are shown under Compression results.
    • If there is a problem during the restore process, the details are below the list of steps.

    In this example, the restore process has completed successfully, and we can see that the compressed database has saved us just over 120MB of disk space (a 71% saving) compared to the uncompressed version.

12. Click Close to return to the main SQL Storage Compress user interface.

    The new compressed database, AdventureWorks_SSC, is added to the Database sizes tab, along with the space savings figures from the the final step of the wizard. The data on the Summary of savings tab is updated accordingly.

Restoring from other backup formats

You can create a compressed database by restoring from a full backup in any of the following formats. Select the appropriate backup on step 1 of the wizard (see 4 above).