Restore encrypted database backup

When restoring to another server, an encrypted backup can not be restored right away. It is after all encrypted and you would need the keys to decrypt it. Assuming you have all the files necessary you could restore the database using the following T-SQL code:

First you have to restore the master key:

RESTORE MASTER KEY
    FROM FILE = 'Media:\BackupFile.key'
	DECRYPTION BY PASSWORD = 'password'
	ENCRYPTION BY PASSWORD = 'password'
GO

You then need to restore the certificate using a private key, certificate itself and the opened master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
	CREATE CERTIFICATE [certName]   
		FROM FILE = 'Media:\BackupFile.cer' 
		WITH PRIVATE KEY (
			FILE = 'Media:\BackupFile.pvk',
			DECRYPTION BY PASSWORD = 'password'
			)
CLOSE MASTER KEY
GO

The following command is necessary to be able to use the master key that we just restored. The Service master key is at the root of the encryption hierarchy.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
	ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
GO

In the last step we want to give the specific location of the files associated to the database. For this to work, we need the exact name of the files. If you don’t know the names of these files you could use the following code to extract those names to be able to use them.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
    RESTORE FILELISTONLY FROM disk = 'Media:\BackupFile.bak'
CLOSE MASTER KEY
GO

The last piece of code is used to restore the database using the given name and file locations.

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
RESTORE DATABASE [databaseName]
	FROM disk = 'Media:\BackupFile.bak'
	WITH NORECOVERY,
	REPLACE,
		MOVE 'databaseName_Data' TO 'Media:\NewLocation.mdf',
	REPLACE,
		MOVE 'databaseName_Log' TO 'Media:\NewLocation.ldf'	
CLOSE MASTER KEY
GO

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *