1

Doing my first backup and restore of someone else's sql server database. I have already done a successful backup and restore of one of the databases, but the other two are locked behind Transparent Data Encryption.

Is there a simple way of finding the certificates' files and just copy/pasting them to the new server's filepath and then restore? What's the proper direction for restoration from backup here?

If I do:

select * FROM [master].[sys].[certificates]

I get a bunch of rows that have ## before them. Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

AI52487963
  • 111
  • 1

1 Answers1

0

Is there a simple way of finding the certificates' files

On source server:

declare 
    @SourceDb sysname = 'MyDb',
    @Thumbprint varbinary(20),
    @Cert sysname,
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)

select @Thumbprint = encryptor_thumbprint 
from sys.dm_database_encryption_keys 
where database_id = db_id(@SourceDb)

select @Cert = name from master.sys.certificates
where thumbprint = @Thumbprint

select @Cert '@Cert'

set @sql = 'BACKUP CERTIFICATE ' + quotename(@Cert) + ' TO FILE = '''+@BackPath+'''
WITH PRIVATE KEY (
FILE = '''+@KeyPath+''',
ENCRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

and then restore

On destination server:

declare 
    @DestDb sysname = 'MyDb',
    @Cert sysname = 'MyTDECert',
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)
set @sql = 'USE master; 
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '''+@Pwd+''';'
exec(@sql)
if @@error<>0 print 
'You already have a DMK - do not drop it. 
Instead, BACKUP MASTER KEY TO FILE... 
and secure its backup.
Also BACKUP SERVICE MASTER KEY TO FILE...
and secure its backup.'

set @sql = 'USE master;
CREATE CERTIFICATE '+quotename(@Cert)+' 
FROM FILE = '''+@BackPath+'''
WITH PRIVATE KEY 
(FILE = '''+@KeyPath+''',
DECRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

What's the proper direction for restoration from backup here?

Now restore the database. After securing the keys and backups off of the 2 servers: Permanently shift+delete them (which in above examples were saved or copied to C:\temp).

Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

They are significant - leave them be :). A cert's thumbprint is used (in above code on the source server) to find the cert which encrypted the db. More than likely it is the cert without the double hashes in the name, but checking sys.dm_database_encryption_keys is better than guessing :).

Bill
  • 224
  • 1
  • 3