Export (0) Print
Expand All

smart_admin.set_instance_backup

Sets SQL Server Managed Backup to Windows Azure the default settings for the instance of SQL Server.

Use this stored procedure to enable SQL Server Managed Backup to Windows Azure and configure the default settings for an instance of SQL Server.

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

Topic link icon Transact-SQL Syntax Conventions

EXEC smart_admin.sp_set_instance_backup 
    [@enable_backup = ] { 0 | 1}
    ,[@storage_url = ] 'storageurl'
    ,[@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'

@enable_backup

Enable or disable SQL Server Managed Backup to Windows Azure for the specified database. The @enable_backup is BIT. Required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time on the instance of SQL Server. When used to change the SQL Server Managed Backup to Windows Azure configuration, 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). If not specified then the storage URL value is derived from the information stored in the SQL Credential.

@retention_days

The retention period for the backup files in days. The @storage_url is INT. Required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time on the instance of SQL Server. When used to change the SQL Server Managed Backup to Windows Azure configuration, 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. Required parameter when configuring SQL Server Managed Backup to Windows Azure for the first time on the instance of SQL Server. When used to change the SQL Server Managed Backup to Windows Azure configuration, this parameter is optional. If not specified then the existing configuration values are retained.

@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 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 for the 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 be configured with Extended Key Management (EKM).

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

0 (success) or 1 (failure)

Permissions

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

The following example enables SQL Server Managed Backup to Windows Azure for the instance of SQL Server it is executed on, sets the retention policy to 30 days, sets the destination to ‘mystorageaccount’, and the SQL Credential ‘MyCredential’.

Use msdb;
Go
   EXEC smart_admin.sp_set_instance_backup
                @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

The following example disables SQL Server Managed Backup to Windows Azure for the instance of SQL Server it is executed on.

Use msdb;
Go
EXEC smart_admin.sp_set_instance_backup
                @enable_backup=0;
GO 
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft