SHARE
TWEET

BACKUP CERTIFICATE documentation test

SqlQuantumLeap Apr 23rd, 2019 294 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top