SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backing up a Certificate

If you create your own certificate in SQL Server, you need to make sure that you back it up immediately. Once you start to encrypt anything with a certificate, you increase the risk that you’ll lose your data if an catastrophic event occurs. In this case, if you lose the certificate, you can’t access the data.

I talked about creating a certificate in another post, and there is corresponding DDL for backing up a certificate. BACKUP CERTIFICATE is the command you want to use, and it works like most of the other backup commands.

Let’s assume I have the certificate named MySalaryCert from the previous post. To create the backup of this certificate, I’d issue:

 TO FILE = N'c:\SQLBackup\MySalaryCert.cer'
  ( FILE = N'c:\SQLBackup\MySalaryCert.pvk'
  , ENCRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'
  , DECRYPTION BY PASSWORD = N'R3allyToughP@ssword4You'

This will generate two files for me in c:\sqlbackup as shown below.


The certificate was created with a password, so the backup must include the DECRYPTION BY option with that password. The password you use for the backup can be different, as shown, but you need to be sure that you manage this password properly. You will need it to restore the certificate, which I’ll show you next time.

Filed under: Blog Tagged: encryption, security, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...