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 :).
