managed_backup.sp_backup_config_basic (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Configures the SQL Server Managed Backup to Microsoft Azure basic settings for a specific database or for an instance of SQL Server.

Note

This procedure can be called on its own to create a basic managed backup configuration. However, if you plan to add advanced features or a custom schedule, first configure those settings using managed_backup.sp_backup_config_advanced (Transact-SQL) and managed_backup.sp_backup_config_schedule (Transact-SQL) before enabling managed backup with this procedure.

Topic link icon Transact-SQL Syntax Conventions

Syntax

EXEC managed_backup.sp_backup_config_basic  
    [@enable_backup = ] { 0 | 1}    ,[@database_name = ] 'database_name'    ,[@container_url = ] 'Azure_Storage_blob_container  
    ,[@retention_days = ] 'retention_period_in_days'    ,[@credential_name = ] 'sql_credential_name'  

Arguments

@enable_backup
Enable or disable SQL Server Managed Backup to Microsoft Azure for the specified database. The @enable_backup is BIT. Required parameter when configuring SQL Server Managed Backup to Microsoft Azure for the first instance of SQL Server. If you are changing an existing SQL Server Managed Backup to Microsoft Azure configuration, this parameter is optional. In that case, any configuration values not specified retain their existing values.

@database_name
The database name for enabling managed backup on a specific database.

@container_url
A URL that indicates the location of the backup. When @credential_name is NULL, this URL is a shared access signature (SAS) URL to a blob container in Azure Storage, and the backups use the new backup to block blob functionality. For more information, please review Understanding SAS. When @credential_name is specified, then this is a storage account URL, and the backups use the deprecated backup to page blob functionality.

Note

Only a SAS URL is supported for this parameter at this time.

@retention_days
The retention period for the backup files in days. The @storage_url is INT. This is a required parameter when configuring SQL Server Managed Backup to Microsoft Azure for the first time on the instance of SQL Server. While changing the SQL Server Managed Backup to Microsoft 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. When specified, the backup is stored to a page blob. If this parameter is NULL, the backup will be stored as a block blob. Backup to page blob is deprecated, so it is preferred to use the new block blob backup functionality. When used to change the SQL Server Managed Backup to Microsoft Azure configuration, this parameter is optional. If not specified, then the existing configuration values are retained.

Warning

The @credential_name parameter is not supported at this time. Only backup to block blob is supported, which requires this parameter to be NULL.

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

You can create both the storage account container and the SAS URL by using the latest Azure PowerShell commands. The following example creates a new container, mycontainer, in the mystorageaccount storage account and then obtains a SAS URL for it with full permissions.

$context = New-AzureStorageContext -StorageAccountName mystorageaccount -StorageAccountKey (Get-AzureStorageKey -StorageAccountName mystorageaccount).Primary  
New-AzureStorageContainer -Name mycontainer -Context $context  
New-AzureStorageContainerSASToken -Name mycontainer -Permission rwdl -FullUri -Context $context  

The following example enables SQL Server Managed Backup to Microsoft Azure for the instance of SQL Server it is executed on, sets the retention policy to 30 days, sets the destination to a container named 'mycontainer' in a storage account named 'mystorageaccount'.

Use msdb;  
Go  
   EXEC managed_backup.sp_backup_config_basic  
                @enable_backup=1  
                ,@container_url = 'https://mystorageaccount.blob.core.windows.net/mycontainer'  
                ,@retention_days=30;   
GO  

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

Use msdb;  
Go  
EXEC managed_backup.sp_backup_config_basic  
                @enable_backup=0;  
GO  

See Also

managed_backup.sp_backup_config_advanced (Transact-SQL)
managed_backup.sp_backup_config_schedule (Transact-SQL)