smart_admin.set_db_backup (Transact-SQL)

Article
02/26/2015

Configures the SQL Server Managed Backup to Windows Azure settings for the specified database.

Use this stored procedure to enable and configure SQL Server Managed Backup to Windows Azure settings for a specific database.

Applies to: SQL Server (SQL Server 2014 through current version).

Topic link icon Transact-SQL Syntax Conventions

Syntax

EXEC smart_admin.sp_set_db_backup  
     [@database_name = ] 'database name'
    ,[@enable_backup = ] { 0 | 1}
    ,[@storage_url = ] 'storage url'
    ,[@retention_days = ] 'retention_period_in_days'
    ,[@credential_name = ] 'sql_credential_name'
    ,[@encryption_algorithm] 'name of the encryption algorithm'
    ,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}
    ,[@encryptor_name] 'name of the certificate or asymmetric key'

Arguments

@database\_name
Name of the database. The @database\_name is SYSNAME. This is a required parameter.
@enable\_backup
Enable or disable SQL Server Managed Backup to Windows Azure for the specified database. The @enable\_backup is BIT. It is a required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time for the database. When used to change the SQL Server Managed Backup to Windows Azure configuration settings, this parameter is optional. If not specified then the existing configuration values are retained.
@storage\_url
The URL of the Windows Azure Storage account. For example: 'https://www.blob.core.windows.net/mystorageaccount'. The container should not be included in this. The @storage\_url is NVARCHAR(1024). It is a required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time. When used to change the SQL Server Managed Backup to Windows Azure configuration settings, this parameter is optional. If not specified then the existing configuration values are retained.
@retention\_days
The retention period for the backup files in days. The @storage\_url is INT. It is a required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time. When used to change the SQL Server Managed Backup to Windows Azure configuration settings, this parameter is optional. If not specified then the existing configuration values are retained.
@credential\_name
The name of the SQL Credential used to authenticate to the Windows Azure storage account. @credentail\_name is SYSNAME. It is a required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time for the database. When used to change the SQL Server Managed Backup to Windows Azure configuration settings, this parameter is optional. If not specified then the existing configuration values are retained.
@encryption\_algorithm
The name of the encryption algorithm to use to encrypt the backup file during the backup operation. The @encryption\_algorithm is SYSNAME. It is a required parameter when configuring SQL Server Managed Backup to Windows 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 Windows 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 used for encryption. The @encryptor\_type is NVARCHAR(32). You can use a certificate of asymmetric key as the encryptor type. 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 reside in the Extended Key Management (EKM) provider.

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

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 enables and configures SQL Server Managed Backup to Windows Azure for the database ‘TestDB’.

Use msdb;
GO
EXEC smart_admin.sp_set_db_backup 
                @database_name='TestDB' 
                ,@enable_backup=1
                ,@storage_url = 'https://mystorageaccount.blob.core.windows.net/' 
                ,@retention_days=30 
                ,@credential_name='MyCredential'
                ,@encryption_algorithm ='AES_128'
                ,@encryptor_type= 'ServerCertificate'
                ,@encryptor_name='MyBackupCert';
GO