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

Silent data compression to optimize SQL Server Storage

Moderators: eddie davis, Colin Millerchip, javen

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

Postby cnilsson » Mon Sep 20, 2010 7:21 am

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
cnilsson
 
Posts: 6
Joined: Wed Sep 16, 2009 2:37 am

Postby eddie davis » Wed Sep 29, 2010 3:07 pm

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
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Postby rbvdberg » Tue Oct 05, 2010 9:01 am

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?
rbvdberg
 
Posts: 10
Joined: Fri May 25, 2007 10:16 am

Postby eddie davis » Thu Oct 07, 2010 1:01 pm

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
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software

Does this product work with multiple .sqb backup files

Postby RML51 » Fri Oct 22, 2010 4:17 pm

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"',
RML51
 
Posts: 16
Joined: Mon Oct 31, 2005 2:57 pm

Does this product work with multiple backup files?

Postby cnilsson » Mon Feb 28, 2011 11:12 pm

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
cnilsson
 
Posts: 6
Joined: Wed Sep 16, 2009 2:37 am


Return to SQL Storage Compress 5

Who is online

Users browsing this forum: No registered users and 0 guests