Edit

BACKUP SYMMETRIC KEY (Transact-SQL)

  • Article

Applies to: SQL Server 2022 (16.x)

Note

SQL Server 2022 (16.x) introduces support for exporting and importing symmetric keys, either to or from Azure Blob storage or file.

Exports the symmetric key.

Transact-SQL syntax conventions

Syntax

BACKUP SYMMETRIC KEY key_name TO 
  {
    FILE = 'path_to_file'
  | URL = 'Azure Blob storage URL'
  }
      ENCRYPTION BY PASSWORD = 'password'

Arguments

FILE ='path_to_file'
Specifies the complete path, including file name, to the file to which the symmetric key will be exported. The path may be a local path or a UNC path to a network location.

URL ='Azure Blob storage URL' Is the URL for your Azure Blob storage, in the format similar to https://<storage_account_name>.blob.core.windows.net/<storage_container_name>/<backup_file_name>.bak.

ENCRYPTION BY PASSWORD ='password'
Is the password used to encrypt the symmetric key in the backup file. This password is subject to complexity checks. For more information, see Password Policy.

Prerequisite

In order to back up the symmetric key to an Azure Blob storage, you need to:

  1. Have an Azure storage account.
  2. Create stored access policy and shared access storage.
  3. Create a SQL Server credential using a shared access signature.

For more information, see Tutorial: Use Azure Blob Storage with SQL Server.

Permissions

Requires ALTER permission on the symmetric key. If adding encryption by a certificate or asymmetric key, requires VIEW DEFINITION permission on the certificate or asymmetric key. If dropping encryption by a certificate or asymmetric key, requires CONTROL permission on the certificate or asymmetric key.

Examples

In the following example, the symmetric key is backed up to a file.

BACKUP SYMMETRIC KEY symmetric_key
   TO FILE = 'c:\temp_backups\keys\symmetric_key' 
   ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

In the following example, the symmetric key is backed up to an Azure Blob storage.

BACKUP SYMMETRIC KEY symmetric_key 
   TO URL = 'https://mydocsteststorage.blob.core.windows.net/mytestcontainer/symmetric_key.bak'
   ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'

See also

RESTORE SYMMETRIC KEY