How to add Restore Password for a Database Backup

Hi, i have done the full backup and would like to know how to protect the backup to avoid any one to restore it. Previously i am using WinZip / 7 Zip to compress the Backup file and add in the password for UnCompress, can i do the same in SQL Server 2008 without using the WinZip / 7 zip ?

from http://msdn.microsoft.com/en-us/library/ms190964.aspx
SQL Server supports password protection for backup media and backup sets.

Important

The protection provided by this password is weak. It is intended to prevent an incorrect restore using SQL Server tools by authorized or unauthorized users. It does not prevent the reading of the backup data by another method or the replacement of the password. This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


thanks.

add on.... since the password protection will be remove, what is the replacement for this feature ? if my backup file is on other people hand, would like to block them to access or restore it.

thanks

Hi, thanks for you info. i have manage to enable the TDE. but got below question :

i also use the Data Compression ( for those big table ) + Backup Compression ( to cover those small table which is not use Data Compression ) in my production DB. Adding in the TDE, notice that the backup compression will become useless.

would like to double check:

if a table got Data Compression, is the Compression come first then Encryption and final IO write ? means

SQL Server step for Data Compression + Encryption
step 1) Data Compression
step 2) Encryption
step 3) IO Write

or

step 1) Encryption
step 2) Data Compression
step 3) IO Write

which 1 is the correct sequence ?

and also i am using Log Shipping, would like to check the Trx Log Backup will return back to the same size as before compress ? eg Previously i have a TrxLog backup file which size around 100MB ( before compress ) and after i enable the Backup Compression, the size is become 10MB only. If i enable the TDE, the Trx Log backup size will become 100MB ?

Thanks.

i have read the BOL, i know Backup Compression will be useless for TDE, but TDE is working well with Data Compression. What i need to clarify is the Data Compression with TDE, not the Backup Compression with TDE.

* in SQL server, backup compression is not same as data compression. Data compression is done on page leve or row level.

I would like to ask few questions here before commenting on this:
1) What is your main goal, you want to get your data be protected? or compressed? or both?

And my comment about these concepts:
To add along with everyone's point, while you compress the backup, the overhead is on compression, but you have advantage on data movement like copying into Tape or any backup device with less time compare to copying the whole data. I got a result of upto 50 to 60% compression and it saves lots of time for copying. So compression will compensate well, My take is, go for compression while backup.

and about encryption, This also works perfectly well, but (but is always annoying hhaa), whenever you try for encryption, your main goal is to secure the data so that, so you need to compensate with the overhead to encrypt and decryption of the data. Protection with Key along with Certificate is always secured than the password protection in Winzip.

Now its your call, which method you want to apply.

-Johnson