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



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |