IN SQL Server 2005, how do I backup the sys.asymmetric_keys and sys.symmetric_keys tables?

Question

In SQL Server 2005, how do I backup the sys.asymmetric_keys and sys.symmetric_keys tables?

Answer

There is no way to directly backup these individual tables, and there is no need to do so. This is because SQL Server 2005 automatically backs up these tables when you back up a database.

On the other hand, you do need to learn how to backup (and restore) the database master key. The database master key is used to encrypt keys and certificates inside a database. If it is deleted or corrupted, SQL Server will be unable to decrypt those keys, and the encrypted data is effectively lost. For this reason, you should back up the database master key, and store the backup securely off-site.

Here's how to backup a database master key (taken from Books Online):

• In SQL Server Management Studio, connect to the database containing the database master key you wish to back up.
• Choose a password that will be used to encrypt the database master key on the backup medium. Do not use the same password that is used to encrypt the key in the database.
• Obtain a removable backup medium for storing a copy of the backed-up key.
• Identify an NTFS directory in which to create the backup of the key. This is where you will create the file specified in the next step. The directory should be protected with highly restrictive ACLs.
• In Query Editor, execute the following Transact-SQL command: BACKUP MASTER KEY TO FILE = '<complete path and filename>' ENCRYPTION BY PASSWORD = '<password>' ; GO
• Copy the file to the backup medium and verify the copy.
• Store the backup in a secure, off-site location.

To restore a database master key (taken from Books Online)::

• Copy the backed-up service master key from the backup medium to a directory on the local file system.
• Execute the following Transact-SQL command: RESTORE SERVICE MASTER KEY FROM FILE = '<complete path and file name>' DECRYPTION BY PASSWORD = '<password>' ; GO