Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement