SqlQuantumLeap

BACKUP CERTIFICATE documentation test

Apr 23rd, 2019
450
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. This example script support the following Microsoft documentation PR:
  3.   https://github.com/MicrosoftDocs/sql-docs/pull/1976
  4.  
  5. That PR concerns the following documentation page:
  6.   https://github.com/MicrosoftDocs/sql-docs/edit/live/docs/t-sql/statements/backup-certificate-transact-sql.md
  7.  
  8. By: Solomon Rutzky
  9. On: 2019-04-23
  10. https://SqlQuantumLeap.com/
  11.  
  12. */
  13.  
  14. USE [tempdb];
  15.  
  16.  
  17. CREATE CERTIFICATE [_BackupRestoreCert]
  18.   ENCRYPTION BY PASSWORD = 'password for private key'
  19.   WITH SUBJECT = 'https://SqlQuantumLeap.com/';
  20.  
  21.  
  22.  
  23. -- Make sure private key is loaded:
  24. SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
  25. FROM   sys.certificates
  26. WHERE  [name] = N'_BackupRestoreCert';
  27. -- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
  28. -- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
  29. -- pvt_key_encryption_type:  ENCRYPTED_BY_PASSWORD
  30. -- pvt_key_last_backup_date: NULL
  31.  
  32.  
  33. -- 1) Backup the private key to a file:
  34. BACKUP CERTIFICATE [_BackupRestoreCert]
  35.   TO FILE = '_BackupRestoreCert_Backup.cer' -- default folder = DefaultData directory
  36.   WITH PRIVATE KEY (
  37.     FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
  38.     DECRYPTION BY PASSWORD = 'password for private key', -- current password
  39.     ENCRYPTION BY PASSWORD = 'backup pass' -- new password
  40.                    );
  41.  
  42.  
  43. -- 2) Remove private key:
  44. ALTER CERTIFICATE [_BackupRestoreCert]
  45.   REMOVE PRIVATE KEY;
  46.  
  47.  
  48. -- 3) Verify that private key has been removed:
  49. SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
  50. FROM   sys.certificates
  51. WHERE  [name] = N'_BackupRestoreCert';
  52. -- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
  53. -- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
  54. -- pvt_key_encryption_type:  NO_PRIVATE_KEY
  55. -- pvt_key_last_backup_date: 2019-04-23 15:29:04.783   (in UTC)
  56.  
  57.  
  58. -- 4) Restore private key:
  59. ALTER CERTIFICATE [_BackupRestoreCert]
  60.   WITH PRIVATE KEY (
  61.     FILE = '_BackupRestoreCert_Backup.pvk', -- default folder = DefaultData directory
  62.     DECRYPTION BY PASSWORD = 'backup pass', -- current password
  63.     ENCRYPTION BY PASSWORD = 'password for private key' -- new password
  64.                    );
  65.  
  66.  
  67. -- 5) Verify that private key has been restored:
  68. SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc], [pvt_key_last_backup_date]
  69. FROM   sys.certificates
  70. WHERE  [name] = N'_BackupRestoreCert';
  71. -- thumbprint:               0x5424DBDA16C2CB3F22630F2908DE57C0737B1886
  72. -- cert_serial_number:       3f e6 9b 87 30 99 bd 8c 41 9a 36 a5 f3 ab 02 fb
  73. -- pvt_key_encryption_type:  ENCRYPTED_BY_PASSWORD
  74. -- pvt_key_last_backup_date: 2019-04-23 15:29:04.783   (in UTC)
RAW Paste Data