Post reply

Hw to restore an encrypted database backup in sql server 2008 (using script)?

  • November 5, 2011 at 12:28 am

    #262330

    Hi,

    Can anybody please suggest me the script a database encrypted backup?

    backup database test to disk='c:\test.bak' with mediapassword = 'XXXX'

    restore database test1 from disk='c:\test.bak' decryption by password = 'XXXX';

    The above script doesn't work..

    Regards,

    Sourav

    Thanks.

  • November 5, 2011 at 12:12 pm

    #1403652

    In this link[/url] you may find the answer. If you are restoring to another Server, you first need to export the certificate you created on the Server where you create the backup. Good luck!



    My blog

  • November 5, 2011 at 12:37 pm

    #1403655

    As per Books Online...

    RESTORE DATABASE ...

    WITH MEDIAPASSWORD = 'XXXX'

    This is not encryption, no keys, no certificates. This is nothing more than a weak password to discourage someone from restoring the media. Also, it's deprecated (since 2005 iirc) and should not be used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 5, 2011 at 1:11 pm

    #1403658

    Thanks guys for the valuable feedback.

    Thanks.

  • November 6, 2011 at 1:50 am

    #1403691

    You are welcome. Please let us know how it goes!:-)



    My blog

  • November 6, 2011 at 9:22 am

    #1403715

    I'm now suggesting the customer to use 'database level encryption'.

    Thanks.

  • November 6, 2011 at 9:32 am

    #1403716

    Sourav-657741 (11/6/2011)

    I'm now suggesting the customer to use 'database level encryption'.

    SQL 2005 doesn't have database encryption. It has column encryption, which is a completely different thing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 6, 2011 at 9:39 am

    #1403719

    Thanks GilaMonster, the server is SQL 2K8.

    Thanks.

  • November 7, 2011 at 7:04 am

    #1403931

    There is Transparent Data Encryption or column encryption, no database level encryption. Whichever one you choose, you will need to be careful about backing up and managing the encryption keys. You also should make sure you use the proper terminology or you might confuse people as to how they might handle a restore.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply