When we try to de-attach a database on which TDE is enabled and try to restore, we get this error "Cannot Find Server Certificate with Thumbprint", We have to take the backup of certificate from source server and create on destination server and then we can attach database successfully.
The code can be used to generate backup certificates for all the databases on server.
The code can be used to generate backup certificates for all the databases on server.
USE MASTER GO DECLARE @CerName VARCHAR(500)
DECLARE @FolderLocation VARCHAR(100) SET @FolderLocation='C:\Scripts\Keys\' DECLARE CUR CURSOR FOR SELECT name FROM sys.certificates WHERE name NOT LIKE '##%' OPEN CUR FETCH NEXT FROM CUR INTO @CerName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @SQL NVARCHAR(MAX) SET @SQL='' SET @sql = 'BACKUP CERTIFICATE ' + @CerName SET @sql = @sql + ' TO FILE = ''' + @FolderLocation + @CerName + '.cer''' + CHAR(10) SET @SQL=@SQL + 'WITH PRIVATE KEY ( FILE=''' + @FolderLocation + @CerName + '.key'',' + CHAR(10) + 'ENCRYPTION BY PASSWORD=''' + CAST(NEWID() AS VARCHAR(50)) + ''')' + CHAR(10) + 'GO' PRINT @sql FETCH NEXT FROM CUR INTO @CerName END CLOSE CUR DEALLOCATE CUR
CREATE Certificate from Backup Copy
CREATE CERTIFICATE CertificateName
FROM FILE='C:\Scripts\Keys\CertificateName_TDECert.cer'
WITH PRIVATE KEY(FILE='C:\Scripts\Keys\Certificate_TDEKey.key',
DECRYPTION BY PASSWORD='Password$')
very useful script.
ReplyDelete