How to Backup a SQL Server Certificate

Backing up SQL Server certificates is essential if you use them, especially for encrypted database backups that may need restoration on another server.

This guide explains how to securely back up a SQL Server certificate and its private key.

Backing up a SQL Server Certificate

1. Check Existing Certificates

To check your existing SQL Server certificates you want to back up, run the query below. We’re querying sys.certificates to get this info.

-- get server certificates
Use Master  
GO  
	SELECT name, pvt_key_encryption_type_desc, subject, start_date, expiry_date, key_length
	FROM sys.certificates
GO

2. Backup the Certificate and Private Key

Replace file paths and passwords as needed in the SQL command below. Check out the MS Docs for Backup Options and more info.

-- backup sql server certificate
BACKUP CERTIFICATE AT_Backup_Cert TO FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_cert.cer'  
      WITH PRIVATE KEY (   
		FILE ='C:\Temp_MSSQL_Backups\mssql_at_backup_key.pvk',  
		ENCRYPTION BY PASSWORD ='Str0ng3P4sSw0rd!' ) 

3. Secure the Backup Files

Move the .cer and .pvk files to a safe location, such as an encrypted storage device or secure cloud storage.

Notes

Use strong passwords to encrypt your SQL Server certificate backups.

Certificates are needed for restoring encrypted backups, and losing them can result in permanent data loss if caught in a Disaster Recovery incident.

Store backups securely and restrict access to authorised personnel only.