Overview

These examples show you how to create compressed databases by restoring from different types of backup file using T-SQL scripts. You can create a compressed database with a normal RESTORE DATABASE command: use MOVE 'logical_name' TO 'operating_system_file_name' statements to restore those files you want to compress to SQL Storage Compress compressed formats (*.mdfx and *.ndfx for data files; *.ldfx for transaction log files).

To create an encrypted and compressed database, restore the database files to the SQL Storage Compress encrypted formats: *.mdfe and *.ndfe for data files; *.ldfe for transaction log files.

Worked examples

• Creating a compressed database from a native SQL Server backup file (*.bak)
• Creating a compressed database from a SQL HyperBac compressed backup file (*.hbc, *.hbc2)
• Creating a compressed database from a SQL HyperBac encrypted backup file (*.hbe)
• Creating a compressed database from a SQL Backup Pro compressed backup file (*.sqb)
• Creating a compressed database from a SQL Backup Pro encrypted backup files (*.sqb)
• Creating a compressed database from a Quest LiteSpeed backup file (*.bak, *.sls)

Notes

• In the following examples, only the data file (AdventureWorks_Data.mdf) of the AdventureWorks database is compressed. To compress the log file, restore it using the extension .ldfx. For example:

  RESTORE DATABASE AdventureWorks_New FROM DISK = 'D:\Backups\AdventureWorks.bak'
  WITH
  MOVE AdventureWorks_Data TO C:\MSSQL\DATA\AdventureWorks_New_Data.mdfx
  MOVE AdventureWorks_Log TO C:\MSSQL\DATA\AdventureWorks_New_Log.ldfx

  In most databases, the transaction log file 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.

• If you encounter "Insufficient free space" errors when running these examples, you may need to add a parameter to thehyperbac.conf configuration file.

  For more information, see SQL Server error 3257 - Insufficient free space.

Worked example: creating a compressed database from a native SQL Server backup file (*.bak)

Restoring from a full backup

In this example we have taken a full backup of the AdventureWorks database, using native SQL Server backup format:

BACKUP DATABASE [AdventureWorks] TO DISK = 'C:\Backups\AdventureWorks.bak'

To create a compressed database from this backup, use MOVE statements in the RESTORE command to create the data file with an .mdfx extension, and the log file with an .ldf extension:

RESTORE DATABASE [AdventureWorks_New] FROM DISK = 'C:\Backups\AdventureWorks.bak'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf',
RECOVERY

When you run the above T-SQL statement, SQL Storage Compress will compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Restoring from a full backup split into multiple files

In this example we have taken a full backup of the AdventureWorks database, split into three backup files:

BACKUP DATABASE [AdventureWorks] TO
DISK = 'C:\Backups\AdventureWorks_1.bak',
DISK = 'C:\Backups\AdventureWorks_2.bak',
DISK = 'C:\Backups\AdventureWorks_3.bak'

To create a compressed database from these backups, use MOVE statements in the first RESTORE DATABASE command, as described above:

RESTORE DATABASE [AdventureWorks_New] FROM
DISK = 'C:\Backups\AdventureWorks_1.bak',
DISK = 'C:\Backups\AdventureWorks_2.bak',
DISK = 'C:\Backups\AdventureWorks_3.bak'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf',
RECOVERY

When you run the above T-SQL statement, SQL Storage Compress will compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Restoring from a set of full, differential and transaction log backups

In this example we have taken a series of backups of the AdventureWorks database. These would normally be taken over a period of several hours or more.

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_Full.bak'
BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_Diff.bak' WITH DIFFERENTIAL
BACKUP LOG [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_Log_01.bak'
BACKUP LOG [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_Log_02.bak'
BACKUP LOG [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_Log_03.bak'

To create a compressed database from these backups, use MOVE statements in the first RESTORE DATABASE command, as described above. Use NORECOVERY on all but the last backup, so that subsequent backups can be restored to the database:

RESTORE DATABASE [AdventureWorks_New] FROM DISK = 'D:\Backups\AdventureWorks_Full.bak'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf',
NORECOVERY


RESTORE DATABASE [AdventureWorks_New] FROM DISK = 'D:\Backups\AdventureWorks_Diff.bak'
WITH NORECOVERY


RESTORE LOG [AdventureWorks_New] FROM DISK = 'D:\Backups\AdventureWorks_Log_01.bak'
WITH NORECOVERY


RESTORE LOG [AdventureWorks_New] FROM DISK = 'D:\Backups\AdventureWorks_Log_02.bak'
WITH NORECOVERY


RESTORE LOG [AdventureWorks_New] FROM DISK = 'D:\Backups\AdventureWorks_Log_03.bak'
WITH RECOVERY

When you run the above T-SQL statement, SQL Storage Compress will compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Worked example: creating a compressed database from a SQL HyperBac compressed backup file (*.hbc, *.hbc2)

SQL Storage Compress works with SQL HyperBac compressed backup files (*.hbc and *.hbc2); you don't need a SQL HyperBac license to restore from these files using SQL Storage Compress.

For this example, assume that a SQL HyperBac compressed backup of the AdventureWorks database is available asAdventure_Works.hbc. We'll use SQL Storage Compress to create a new compressed database, AdventureWorks_New, from this backup file.

To create AdventureWorks_New as a compressed database, use MOVE statements in the RESTORE command to create the data file with an .mdfx extension, and the log file with an .ldf extension:

RESTORE DATABASE [AdventureWorks_New] FROM DISK = 'C:\Backups\AdventureWorks.hbc'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf'

When you run the above T-SQL statement, SQL Storage Compress will automatically decompress the data in the SQL HyperBac backup file, and will then apply compression to AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Worked example: creating a compressed database from a SQL HyperBac encrypted backup file (*.hbe)

SQL Storage Compress works with SQL HyperBac encrypted backup files (*.hbe); you don't need a SQL HyperBac license to restore from these files using SQL Storage Compress.

For this example, assume that a SQL HyperBac encrypted backup of the AdventureWorks database is available asAdventure_Works.hbe. We'll use SQL Storage Compress to create a new compressed database, AdventureWorks_New, from this backup file.

As we are working with an encrypted backup file, it is essential that the correct key file is available on the SQL Server that will run the new compressed database. The default location for key files is %ProgramFiles%\Red Gate\HyperBac\keys on 32-bit machines and %ProgramFiles(x86)%\Red Gate\HyperBac\keys on 64-bit machines. For more information, see Working with key files for encrypted data.

To create AdventureWorks_New as a compressed database, use MOVE statements in the RESTORE command to create the data file with an .mdfx extension, and the log file with an .ldf extension:

RESTORE DATABASE [AdventureWorks_New] FROM DISK = 'C:\Backups\AdventureWorks.hbe'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf'

When you run the above T-SQL statement, SQL Storage Compress will decrypt the data in the SQL HyperBac backup file using the key file you supplied, then compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Worked example: creating a compressed database from a SQL Backup Pro compressed backup file (*.sqb)

SQL Storage Compress works with SQL Backup Pro backup files; you don't need to have the SQL Backup Pro installed on your SQL Server to restore from these backup files with SQL Storage Compress.

In this example, we have backed up the AdventureWorks database to a compressed SQL Backup Pro backup file,AdventureWorks.sqb:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [AdventureWorks]
TO DISK = ''C:\Backups\AdventureWorks.sqb''
WITH COMPRESSION = 4, THREADCOUNT = 2"'

To create a compressed database from this backup, use MOVE statements in the RESTORE command to create the data file with an .mdfx extension, and the log file with an .ldf extension.

You must include one DISK statement for each thread that was used to create the backup. In the above example, the backup command used THREADCOUNT = 2, so the RESTORE command must include two identical DISK = 'C:\Backups\AdventureWorks.sqb' statements:

RESTORE DATABASE [AdventureWorks_New] FROM
DISK = 'C:\Backups\AdventureWorks.sqb',
DISK = 'C:\Backups\AdventureWorks.sqb'
WITH
MOVE 'AdventureWorks_Data' TO 'C:\MSSQL\Data\AdventureWorks_New_Data.mdfx',
MOVE 'AdventureWorks_Log' TO 'C:\MSSQL\Data\AdventureWorks_New_Log.ldf'

To find out how many threads were used to create a backup file, use the RESTORE LABELONLY command:

RESTORE LABELONLY FROM DISK = 'C:\Backups\AdventureWorks.sqb'

This command returns information about the backup file. The value for FamilyCount represents the number of threads that were used to create the backup file.

When you run the above RESTORE statement, SQL Storage Compress will compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.

Worked example: creating a compressed database from a SQL Backup Pro encrypted backup file (*.sqb)

SQL Storage Compress works with SQL Backup Pro encrypted backup files; you don't need to have SQL Backup Pro installed on your SQL Server to restore from these backup files with SQL Storage Compress.

In this example, we have backed up the AdventureWorks database to an encrypted SQL Backup Pro backup file,AdventureWorks.sqb:

EXECUTE master..sqlbackup '-SQL "
BACKUP DATABASE [AdventureWorks] TO DISK = ''C:\Backups\AdventureWorks.sqb''
WITH PASSWORD = ''<password>'', COMPRESSION = 4, THREADCOUNT = 2"'

Decrypting SQL Backup Pro backups

Before you restore a SQL Backup Pro encrypted backup file to a compressed database, you must call an extended stored procedure (xp_validate_sqb_backup) to associate the encrypted backup file with a password:

EXEC master..xp_validate_sqb_backup
@filename=N'C:\Backups\AdventureWorks.sqb',
@password=N'<password>'

If the xp_validate_sqb_backup extended stored procedure isn't available, you'll need to add it to the master database for the local SQL Server instance. To do this:

1. Copy the xpHyperBac.dll file from %Program Files%\Red Gate\HyperBac\bin on 32-bit machines or %Program Files (x86)%\Red Gate\HyperBac\bin on 64-bit machines to the installation directory of your SQL Server instance (for example%Program Files%\Microsoft SQL Server\MSSQL10\MSSQL\Binn, for SQL Server 2008).

2. Run the following command to install the stored procedure:

   EXEC dbo.sp_addextendedproc N'xp_validate_sqb_backup', 'xpHyperBac.dll';

Restoring SQL Backup Pro backups

Now, you can create a compressed database from the encrypted backup file, as described above. Ensure that you include oneDISK statement for each thread that was used to create the backup.

Worked example: creating a compressed database from a Quest LiteSpeed backup file (*.bak, *.sls)

SQL Storage Compress works with Quest LiteSpeed™ backup files, provided you have the necessary LiteSpeed components installed on your SQL Server.

For this example, we'll assume that we already have a backup of the AdventureWorks database in LiteSpeed format (namedAdventureWorks.sls).

To restore the AdventureWorks.sls backup file to a new compressed database, AdventureWorks_New, use MOVE statements in the RESTORE command to create the data file with an .mdfx extension, and the log file with an .ldf extension:

EXEC master.dbo.xp_restore_database
@database = [AdventureWorks_New],
@filename = 'C:\Backups\AdventureWorks.sls',
@with = 'MOVE ''AdventureWorks_Data'' TO ''C:\AdventureWorks_New_Data.mdfx''',
@with = 'MOVE "AdventureWorks_Log'' TO ''C:\AdventureWorks_New_Log.ldf'''

You can also use a T-SQL script to restore from an encrypted LiteSpeed backup file. For example:

EXEC master.dbo.xp_restore_database
@database = [AdventureWorks_New],
@filename = 'C:\Backups\AdventureWorks.sls',
@encryptionkey='<password>',
@with = 'MOVE ''AdventureWorks_Data'' TO ''C:\AdventureWorks_New_Data.mdfx''',
@with = 'MOVE ''AdventureWorks_Log'' TO ''C:\AdventureWorks_New_Log.ldf'''

When you run the above T-SQL statement, SQL Storage Compress will compress AdventureWorks_New_Data.mdfx. When the database is online, SQL Storage Compress will compress any data that is added to the file and decompress data that is read from the file.