Red Gate forums :: View topic - Doesn't work with SQL Backup Encryption? Is that changing?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Storage Compress 5
SQL Storage Compress 5 forum

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

Search in SQL Storage Compress 5 forum
Post new topic   Reply to topic
Jump to:  
Author Message
cnilsson



Joined: 16 Sep 2009
Posts: 6

PostPosted: Mon Sep 20, 2010 7:21 am    Post subject: Doesn't work with SQL Backup Encryption? Is that changing? Reply with quote

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
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 939
Location: Red Gate Software

PostPosted: Wed Sep 29, 2010 3:07 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
rbvdberg



Joined: 25 May 2007
Posts: 10

PostPosted: Tue Oct 05, 2010 9:01 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
eddie davis



Joined: 14 Jun 2006
Posts: 939
Location: Red Gate Software

PostPosted: Thu Oct 07, 2010 1:01 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
RML51



Joined: 31 Oct 2005
Posts: 16

PostPosted: Fri Oct 22, 2010 4:17 pm    Post subject: Does this product work with multiple .sqb backup files Reply with quote

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"',
Back to top
View user's profile Send private message
cnilsson



Joined: 16 Sep 2009
Posts: 6

PostPosted: Mon Feb 28, 2011 11:12 pm    Post subject: Does this product work with multiple backup files? Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group