Worked examples: backing up with compression or encryption

Overview

These examples show you how to back up a database using SQL HyperBac's compression or encryption. The examples use Microsoft's "AdventureWorks" sample database, but you can substitute a database of your own.

SQL HyperBac is very easy to use; you don't need to make substantial changes to your existing backup jobs to start compressing and encrypting your backup files. By simply changing the extension of your backup files, SQL HyperBac will start applying the compression or encryption processing you require.

By default, SQL HyperBac automatically recognizes backup files on any path with the following extensions, and applies the processing shown:

You can add, modify, and delete extensions and associated processing options using HyperBac Configuration Manager. For example, you might choose to create a new ".bak" extension to use SQL HyperBac with your existing backup jobs. See Working with file paths, extensions, and processing options for further information.

For these examples, we'll use the default extensions.

Worked example: backing up with SQL HyperBac compression

First, consider the T-SQL command that you would normally use to back up the "AdventureWorks" database:

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

To back up AdventureWorks with SQL HyperBac standard compression:

In the T-SQL statement, simply change the extension of the backup file to .hbc:

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_backup.hbc'

That's all you need to do. SQL HyperBac will automatically apply compression to 'AdventureWorks_backup.hbc' when you run this T-SQL statement.

Whether you run the T-SQL statement from SQL Server Management Studio (either by explicitly typing the statement, or using the GUI options), using SQLCMD, or with any other tool, SQL HyperBac will automatically intercept the backup data, and compress it.

To back up AdventureWorks with SQL HyperBac fast compression:

In the T-SQL statement, simply change the extension of the backup file to .hbc2:

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_backup.hbc2'

To back up AdventureWorks with SQL HyperBac ZIP-compatible compression:

In the T-SQL statement, simply change the extension of the backup file to .zip or .rar:

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_backup.zip'

or,

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_backup.rar'

Exporting data from AdventureWorks.Sales.customer with SQL HyperBac standard compression

You can apply SQL HyperBac compression to the output of export operations (using 'bcp') in exactly the same way as for 'BACKUP DATABASE' commands.

Consider the bcp command that you would normally use to export data from the AdventureWorks.Sales.customer table (on a local instance named "PRODUCTION"):

bcp AdventureWorks.Sales.customer out D:\Backups\Export.dmp ‑Slocalhost\PRODUCTION ‑T ‑n ‑b1000

In the bcp command, simply change the extension of the export file to .hbc:

bcp AdventureWorks.Sales.customer out D:\Backups\Export.hbc ‑Slocalhost\PRODUCTION ‑T ‑n ‑b1000

Changing the .dmp extension to any of the other default extensions will cause SQL HyperBac to automatically process the export data as described.

Worked example: backing up with SQL HyperBac encryption

First, consider the T-SQL command that you would normally use to back up the "AdventureWorks" database:

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

To back up AdventureWorks with SQL HyperBac AES-256 encryption:

In the T-SQL statement, simply change the extension of the backup file to .hbe:

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\Backups\AdventureWorks_backup.hbe'

That's all you need to do. SQL HyperBac will automatically apply encryption to 'AdventureWorks_backup.hbe' when you run this T-SQL statement.

Whether you run the T-SQL statement from SQL Server Management Studio (either by explicitly typing the statement, or using the GUI options), using SQLCMD, or with any other tool, SQL HyperBac will automatically intercept the backup data, and encrypt it.

Note that if SQL HyperBac does not find a key file in the 'keys' folder (located at "C:\Program Files\Red Gate\HyperBac\keys" by default), it will generate a random key, and write this to a new key file.

Read Managing key files for encrypted backups for important information about managing and backing up key files.

Exporting data from AdventureWorks.Sales.customer with SQL HyperBac AES-256 encryption

You can apply SQL HyperBac encryption to the output of export operations (using 'bcp') in exactly the same way as for 'BACKUP DATABASE' commands.

Consider the bcp command that you would normally use to export data from the AdventureWorks.Sales.customer table (on a local instance named "PRODUCTION"):

bcp AdventureWorks.Sales.customer out D:\Backups\Export.dmp -Slocalhost\PRODUCTION -T -n -b1000

In the bcp command, simply change the extension of the export file to .hbe:

bcp AdventureWorks.Sales.customer out D:\Backups\Export.hbe -Slocalhost\PRODUCTION -T -n -b1000

SQL HyperBac will automatically encrypt the exported data as for the BACKUP DATABASE example above.