managed_backup.sp_backup_config_advanced (Transact-SQL)

Article
01/29/2021
2 minutes to read

Applies to: SQL Server 2016 (13.x) and later

Configures advanced settings for SQL Server Managed Backup to Microsoft Azure.

Topic link icon Transact-SQL Syntax Conventions

Syntax

EXEC managed_backup.sp_backup_config_advanced   
    [@database_name = ] 'database_name'  
    ,[@encryption_algorithm = ] 'name of the encryption algorithm'  
    ,[@encryptor_type = ] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}  
    ,[@encryptor_name = ] 'name of the certificate or asymmetric key'  
    ,[@local_cache_path = ] 'NOT AVAILABLE'

Arguments

@database_name
The database name for enabling managed backup on a specific database. If NULL or *, then this managed backup applies to all databases on the server.

@encryption_algorithm
The name of the encryption algorithm used during the backup to encrypt the backup file. The @encryption_algorithm is SYSNAME. It is a required parameter when configuring SQL Server Managed Backup to Microsoft Azure for the first time for the database. Specify NO_ENCRYPTION if you do not wish to encrypt the backup file. When changing the SQL Server Managed Backup to Microsoft Azure configuration settings, this parameter is optional - if the parameter is not specified then the existing configuration values are retained. The allowed values for this parameter are:

AES_128
AES_192
AES_256
TRIPLE_DES_3KEY
NO_ENCRYPTION

For more information on encryption algorithms, see Choose an Encryption Algorithm.

@encryptor_type
The type of encryptor, which can be either 'CERTIFICATE' or 'ASYMMETRIC_KEY". The @encryptor_type is nvarchar(32). This parameter is optional if you specify NO_ENCRYPTION for the @encryption_algorithm parameter.

@encryptor_name
The name of an existing certificate or asymmetric key to use to encrypt the backup. The @encryptor_name is SYSNAME. If using an asymmetric key, it must be configured with Extended Key Management (EKM). This parameter is optional if you specify NO_ENCRYPTION for the @encryption_algorithm parameter.

For more information, see Extensible Key Management (EKM).

@local_cache_path
This parameter is not yet supported.

Return Code Value

0 (success) or 1 (failure)

Security

Permissions

Requires membership in db_backupoperator database role, with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistory stored procedure.

Examples

The following example sets advanced configuration options for SQL Server Managed Backup to Microsoft Azure for the instance of SQL Server.

Use msdb;  
Go  
   EXEC managed_backup.sp_backup_config_advanced  
                @encryption_algorithm ='AES_128'  
                ,@encryptor_type = 'CERTIFICATE'  
                ,@encryptor_name = 'MyTestDBBackupEncryptCert'  
GO