AKAWN

Below shows some steps to test populating the pvt_key_last_backup_date of the sys.certificates DMV.

 The code for testing is here:

USE testdb;

-- ***** master key created so a certificate can be created *****'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$trongPa55';

-- ***** certificate created *****
CREATE CERTIFICATE testcert WITH SUBJECT='testcertsub';

-- ***** show pvt_key_last_backup_date *****
SELECT name,pvt_key_encryption_type_desc,pvt_key_last_backup_date FROM sys.certificates;

-- ***** now to populate pvt_key_last_backup_date column *****
BACKUP CERTIFICATE testcert TO FILE = 'c:\test\testcert.cer'
WITH PRIVATE KEY ( FILE = 'c:\test\dmk_tesdb.pvk', ENCRYPTION BY PASSWORD = 'another$trongPa55');

-- ***** show pvt_key_last_backup_date *****
SELECT name,pvt_key_encryption_type_desc,pvt_key_last_backup_date FROM sys.certificates;

-- ***** clean-up drop certificate so we can drop the master key *****
DROP CERTIFICATE testcert;

-- ***** clean-up drop master key *****
DROP MASTER KEY;