Doesn't work with SQL Backup Encryption? Is that changing?

cnilsson

Hello.

We are a Electronic Medical Report company, so our backups need to be encrypted and compressed. We have been using Red Gate SQL Backup 6 to do that.

We have now started the 14 day trial of SQL Storage Compress 5. If we go with this, will it take major hoops to jump through in order to encrypt our backups?

I know we cannot restore to a compressed file from an encrypted SB6 backup file, but is it possible to use the encryption feature of SQL Backup 6 when backing up from a compressed file? How about Transaction Log backup file compression using SB6? We copy our TLog files across from one data center to another, so we really need those files encrypted.

Thoughts?

Thanks.

Chris Nilsson
Altos Solutions

Eddie D

Thank you for your post into the forum.

The simple answer to your question is yes, you can take an encrypted backup using SQL Backup 6 of a database using compressed files created using SQL Storage Compress.

SQL Backup relies upon SQL Server to collect the backup data. SQL Server is totally unaware that the database files are compressed. So when data is read from the database files, the data is uncompressed before being passed to SQL Server.

SQL Storage Compress uses the HyperBac Technology to compress and uncompress data to and from the database files. When SQL Server reads from the database to collect the backup data, the read process is intercepted by the HyperBac Technology, the required backup data held in the compressed database files is uncompressed and passed to SQL Server.

Once SQL Server has collected the backup data, via the Virtual Device Interface (VDI), SQL Server informs SQL Backup where to find the backup data. SQL Backup will then compress and encrypt the data before writing the backup data to disk.

I hope the above answers your question.

Many Thanks
Eddie

rbvdberg

Hi. We're evaluating SQL Storage compress, and are also concerned about the backup encruption.

If I read this correctly, this means that you can use SQLBackup to make encrypted backups from a Hyperbac compressed database, but you can't restore back to the same database (unless you decrypt the backup first, by converting this to a standard backup). Is this correct?

Eddie D

Hi

I have some further information about encrypted backup files from SQL Backup, and restoring to SQL Storage Compress database files.

Simply script out the restore task using the WITH MOVE command into files with extensions mdfx, ndfx and ldfx files (rather than mdf, ndf and ldf extensions).

For Example:

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [AdventureWorks] FROM DISK = ''<path to backup file>.sqb'' WITH RECOVERY, MOVE ''AdventureWorks_Data'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_data.mdfx'', MOVE ''AdventureWorks_Log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldfx'', PASSWORD = ''p@ssw0rd''"'

Note this same approach would work for a backup from any 3rd party SQL Server backup product – simply change the file extensions in the restore process as described above.

Please accept my apologies for not posting this information earlier.

Many Thanks
Eddie

RML51

We encrypted our production backups and we also split them into multiple .sqb files. The .sqb files are than copied and restored onto our development server. Would the following restore script work with SQL Storage Compress 5?

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [AdventureWorks]
FROM DISK = ''D:\My_Backups\AdventureWorks_Backup_Encrypted_1.sqb'',
DISK = ''D:\My_Backups\AdventureWorks_Backup_Encrypted_2.sqb'',
DISK = ''D:\My_Backups\AdventureWorks_Backup_Encrypted_3.sqb'',
DISK = ''D:\My_Backups\AdventureWorks_Backup_Encrypted_4.sqb''
WITH PASSWORD = ''My_pa$$w0rd'',
RECOVERY, DISCONNECT_EXISTING,
MOVE ''AdventureWorks_Data'' TO ''E:\My_Server\Data\AdventureWorks_Data.MDFX'',
MOVE ''AdventureWorks_Log'' TO ''E:\My_Server\Data\AdventureWorks_Log.LDFX'',
REPLACE"',

cnilsson

We have also restored to compressed file types from a set of multiple compressed and encrypted Red-Gate SQL Backup (.sqb) files.

Thanks.

Chris Nilsson
Senior Systems Engineer
Altos Solutions