I suddenly start to get the message while performing backup, I know i have taken backup of the certificate and stored it in my password manager which I still have access to. My question is that do we need to have backup cycle of certificate(s) backup? is there any default limit set from Microsoft that we need to take backup every x days. I know ,I took backup of certificate on '2017-02-03 15:57:53.000' i.e. 164 days ago.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

  • 1
    You stated that you took a backup of the certificate, but it is also essential that you have a backup of the associated private key. The private key is what decrypts the Database Encryption Key and the DEK is used for both encrypting and decrypting the pages in Transparent Data Encryption. – Michael Keleher Jul 18 '17 at 12:33

That warning is just checking to see if you've ever backed up your encryptor certificate using T-SQL. This could put you in situation where the database backup would be unreadable if you were to lose that SQL Server instance for whatever reason.

No, you don't need a regular backup cycle for the cert. You wouldn't need to back it up after the initial one unless you've lost your copy.

That warning is just checking to see if the pvt_key_last_backup_date IS NULL.

    select pvt_key_last_backup_date, * 
   from master.sys.certificates 
   where name = 'BackupCertifcateName'

Perhaps that backup was taken with a different certificate than the one you've backed up?

You can check to see which Certificate was used with this query:

    c.name as backupCertName,
    type as backupType
FROM msdb.dbo.backupset bks
JOIN master.sys.certificates c on c.thumbprint = bks.encryptor_thumbprint
WHERE database_name = 'DB_NAME'
AND Encryptor_type = 'CERTIFICATE'
AND type <> 'L'
ORDER BY backup_start_date  DESC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.