The Missing BACKUP ASYMMETRIC KEY statement

Introduction

Back in How to Back Up your Certificates I recommended that you always keep a separate backup of your important certificates around. The same is true for asymmetric keys. However, there we run into a slight complication.

The BACKUP ASYMMETRIC KEY Attempt

While SQL Server's T-SQL dialect is not always consistent, in many cases you can guess what a statement should look like. To back up an asymmetric key we would expect a statement like this:

This would be followed by the specification of where the backup should be written to, just as it is the case with other T-SQL backup statements. However, when trying to execute this stub, we get a discouraging error:

Instead of complaining about an incomplete statement, SQL Server tells us that is does not know the combination of BACKUP and ASYMMETRIC. That does not bode well. Let us check, if the Intelligent Code Completion feature can be of any help.

No luck here either. It seems there is no way to back up your asymmetric keys.

The Workaround

Well, there is indeed no built-in way in SQL Server to back up an asymmetric key. However, as we have seen in my article about strong name files, you can create asymmetric keys outside of SQL Server and then import them. If you create all your asymmetric keys that way, you can keep a copy of the original file around as a "backup".

Another way to deal with this "lack of backup" is to use certificates. In most situations that call for an asymmetric key, a certificate can be used just as well.

Database Backups

Asymmetric keys are stored in the database that they were created in. That means they are (like certificates too) backed up with every database backup. That might be enough protection for you because if something goes awry, you can just restore the entire database. I just like to have an additional layer of protection, as these keys often are literally the key to the kingdom data.

Summary

SQL Server does not provide a BACKUP ASYMMETRIC KEY statement or any other way to back up your asymmetric keys. There are two ways to deal with that situation. You can either generate all asymmetric keys outside of SQL Server and back up the key source or you can avoid using asymmetric keys altogether.