SQL Server Performance

How to add Restore Password for a Database Backup

Discussion in 'SQL Server 2008 General DBA Questions' started by yhchan2005, Sep 26, 2011.

  1. yhchan2005 Member

    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.



    [IMG] 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.
  2. yhchan2005 Member

    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
  3. satya Moderator

    You can go to use Transparent Data Encryption where the security of obtaining the database backup or file can be restricted if the supported master key certification is not available. Have a look at hte books online for TDE in thsi case.
  4. Shehap MVP, MCTS, MCITP SQL Server

    Yes, as Satya stated , it could be done using TDE (Transparent Data Encryption) . through the below consecutive commands :

    USEmaster;
    GO
    CREATEMASTERKEYENCRYPTIONBYPASSWORD='P@ssw0rd';
    go

    CREATECERTIFICATE MyServerCert WITHSUBJECT='P@ssw0rd';
    go

    USE DB name;
    GO
    CREATEDATABASEENCRYPTIONKEY
    WITHALGORITHM=AES_128
    ENCRYPTIONBYSERVERCERTIFICATE MyServerCert;
    GO
    ALTERDATABASE DB name
    SETENCRYPTIONOn;
    GO

    .But take with consider the high cautions below:

    · TDE has some performance impact while Encryption /Decryption each time read /write process access this DB.

    · Saving well all DMK (Database Master Key) + UDK (User Database Key if needed to restore backup taken on another DB Server

    · Much preferred to use SQL server 2008 R2 or SQL Server 2008 SP2 since it was a previous SQL Server 2008 editions where if needed to remove encryption through the command :

    , you couldn’t even though it looks like apparently for you it succeeded …. but unfortunately it still appears within Select * from sys.databases as encrypted one

    Therefore,You have to read more as Satya stated about the 5 layers of Encryption + Decryption
  5. satya Moderator

    In any case stick with SQL Server books online to get all the first hands information on syntax and basic steps to do, I do it always :)
  6. yhchan2005 Member

    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.
  7. satya Moderator

    See what BOL is recommending:
    Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

    FYI http://msdn.microsoft.com/en-us/library/bb934049.aspx
  8. yhchan2005 Member

    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.
  9. ghemant Moderator

  10. johnson_ef Member

    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
  11. satya Moderator

    ,.... in any case the testing is most important aspect about the what feature you want to use or not to use.
    Lets not generalise that its good or bad, because every environment and need is different to each other :)

Share This Page