managed_backup.sp_backup_on_demand (Transact-SQL)


Updated: June 10, 2016

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

Requests SQL Server Managed Backup to Microsoft Azure to perform a backup of the specified database.

Use this stored procedure to perform ad hoc backups for a database configured with SQL Server Managed Backup to Microsoft Azure. This prevents any break in the backup chain and SQL Server Managed Backup to Microsoft Azure processes are aware and the backup is stored in the same Windows Azure Blob storage container.

Upon successful completion of the backup the full backup file path is returned. This includes the name and location of the new backup file resulting from the backup operation.

An error is returned if SQL Server Managed Backup to Microsoft Azure is in the process of executing a backup of given type for the specified database. In this case, the error message returned includes the full backup file path where the current backup is being uploaded to.

Topic link icon Transact-SQL Syntax Conventions

EXEC managed_backup.sp_backup_on_demand   
[@database_name  =]  'database name',[@type = ] { 'Database' | 'Log' }  

The name of the database on which the backup is to be performed. The @database_name is SYSNAME.

The type of backup to be performed: Database or Log. The @type parameter is NVARCHAR(32).

0 (success) or 1 (failure)


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

The following example makes a database backup request for the database ‘TestDB’. This database has SQL Server Managed Backup to Microsoft Azure enabled.

Use MSDB  
EXEC managed_backup.sp_backup_on_demand  
 @database_name = 'TestDB'  
,@type = 'Database'  

For each code snippet, select 'tsql' in the language attribute field.

Community Additions