/*
This example script support the following Microsoft documentation PR:
  https://github.com/MicrosoftDocs/sql-docs/pull/1976

That PR concerns the following documentation page:
  https://github.com/MicrosoftDocs/sql-docs/edit/live/docs/t-sql/statements/backup-certificate-transact-sql.md

By: Solomon Rutzky
On: 2019-04-23
https://SqlQuantumLeap.com/

*/

USE [tempdb];


CREATE CERTIFICATE [_BackupRestoreCert]
  ENCRYPTION BY PASSWORD = 'password for private key'
  WITH SUBJECT = 'https://SqlQuantumLeap.com/';



-- Make sure private key is loaded:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM   sys.certificates
WHERE  [name] = N'_BackupRestoreCert';
-- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type:  ENCRYPTED_BY_PASSWORD
-- pvt_key_last_backup_date: NULL


-- 1) Backup the private key to a file:
BACKUP CERTIFICATE [_BackupRestoreCert]
  TO FILE = '_BackupRestoreCert_Backup.cer' -- default folder = DefaultData directory
  WITH PRIVATE KEY (
    FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
    DECRYPTION BY PASSWORD = 'password for private key', -- current password
    ENCRYPTION BY PASSWORD = 'backup pass' -- new password
                   );


-- 2) Remove private key:
ALTER CERTIFICATE [_BackupRestoreCert]
  REMOVE PRIVATE KEY;


-- 3) Verify that private key has been removed:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM   sys.certificates
WHERE  [name] = N'_BackupRestoreCert';
-- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type:  NO_PRIVATE_KEY
-- pvt_key_last_backup_date: 2019-04-23 15:29:04.783   (in UTC)


-- 4) Restore private key:
ALTER CERTIFICATE [_BackupRestoreCert]
  WITH PRIVATE KEY (
    FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
    DECRYPTION BY PASSWORD = 'backup pass', -- current password
    ENCRYPTION BY PASSWORD = 'password for private key' -- new password
                   );


-- 5) Verify that private key has been restored:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
FROM   sys.certificates
WHERE  [name] = N'_BackupRestoreCert';
-- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
-- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
-- pvt_key_encryption_type:  ENCRYPTED_BY_PASSWORD
-- pvt_key_last_backup_date: 2019-04-23 15:29:04.783   (in UTC)