How To Restore Encrypted Backup To Another Instance On SQL Server

dbtut

5 years ago

First you should restore your certificate to this instance. You can restore your certificate with the help of following script.

You must write the path where the certificate and private key backup is located instead of ‘C:\Backup\MyCert’ and ‘C:\Backup\MyCertPrivateKey’

You must enter the password that you defined when taking a certificate backup to the password section.

You can find details in my article “Encrypted Backup On SQL Server“.

Use [master]
CREATE CERTIFICATE MyCert
    FROM FILE = 'C:\Backup\MyCert'
    WITH PRIVATE KEY ( FILE = 'C:\Backup\MyCertPrivateKey' ,
    DECRYPTION BY PASSWORD = 'Password' );

Use [master]

CREATE CERTIFICATE MyCert

FROM FILE = 'C:\Backup\MyCert'

WITH PRIVATE KEY ( FILE = 'C:\Backup\MyCertPrivateKey' ,

DECRYPTION BY PASSWORD = 'Password' );

You can then restore the encrypted backup as follows.

USE [master]

RESTORE DATABASE [MyDB] FROM  DISK = N'\\MyShare\MyDB.BAK' WITH  FILE = 1, 
MOVE N'MyDB' TO N'H:\Disk01\MyDB.mdf', 
MOVE N'MyDB_log' TO N'H:\Disk01\MyDB.ldf', 
NOUNLOAD,  STATS = 1

USE [master]

RESTORE DATABASE [MyDB] FROM DISK = N'\\MyShare\MyDB.BAK' WITH FILE = 1,

MOVE N'MyDB' TO N'H:\Disk01\MyDB.mdf',

MOVE N'MyDB_log' TO N'H:\Disk01\MyDB.ldf',

NOUNLOAD, STATS = 1