Working with key files for encrypted backups - SQL Virtual Restore

SQL Virtual Restore works with encrypted backup files created by SQL HyperBac and SQL Backup (version 6.0 and above). These encrypted backup file formats are handled differently by SQL Virtual Restore.

About SQL HyperBac encryption and key files

When SQL Virtual Restore detects a request from a SQL Server instance to read from or write to a SQL HyperBac encrypted backup file or data file, it will first check the 'keys' directory on the SQL Server (located at 'C:\Program Files\Red Gate\HyperBac\keys' by default) to look for a valid key file:

• If SQL Virtual Restore finds a key file, it uses this key file to encrypt and decrypt the data as it passes through the Windows file system
• If a key file does not exist, or the wrong key file is present, the SQL Server instance will report an error accessing the associated backup files or data files.

You can think of the key file as equivalent to a password for accessing the encrypted backup data. If you lose the key file associated with a SQL HyperBac encrypted backup, the original backup data cannot be recovered, or used to virtually restore the database.

About SQL Backup encryption and passwords

When you virtually restore a database from encrypted SQL Backup backup files, SQL Virtual Restore uses an extended stored procedure ('xp_validate_sqb_backup') to associate the backup files with a password that you provide.

If you use the SQL Virtual Restore Wizard to create your virtual database, you will be given the option to allow the wizard to install or upgrade the stored procedure for you, and then to enter the password that was used to encrypt your backup file:

If you use native T-SQL commands to script the creation of your virtual database, you may first have to manually install or upgrade the 'xp_validate_sql_backup' stored procedure.

Manually installing or upgrading the 'xp_validate_sql_backup' stored procedure

1. Check whether an earlier version of 'xp_validate_sql_backup' is already installed on your SQL Server instance.

   You can do this using the Object Explorer in SQL Server Management Studio, by browsing to the Databases > System Databases > master > Programmability > Extended Stored Procedures folder.

   

2. If the 'xp-validate_sql_backup' stored procedure is listed, delete it. If the stored procedure isn't listed, go to step 4.
3. Run the following command to unload the stored procedure DLL from memory:

   DBCC xpHyperBac (FREE);

4. In Windows Explorer, browse to the Red Gate HyperBac installation directory (for example, C:\Program Files\Red Gate\HyperBac\bin), and find the 'xpHyperBac.dll' file.

   Copy this file to the installation directory of your SQL Server instance (for example C:\Program Files\Microsoft SQL Server\MSSQL10\MSSQL\Binn, for SQL Server 2008).

5. Run the following command to install the new stored procedure :

   EXEC dbo.sp_addextendedproc N'xp_validate_sqb_backup', '<path to DLL>';

   where <path to DLL> is the location of the 'xpHyperBac.dll' file that you copied in step 4.

   The stored procedure is now installed on your SQL Server instance

Using the 'xp_validate_sql_backup' stored procedure

Once you have installed the 'xp_validate_sql_backup' stored procedure on your SQL Server instance, you can use it in scripts to associate a password with each encrypted SQL Backup backup file you want to use with SQL Virtual Restore:

EXEC master..xp_validate_sqb_backup

@filename = 'C:\MSSQL\Backup\encrypted_sql_backup_file.sqb',

@password = 'PASSWORD';