managed_backup.sp_backup_on_demand (Transact-SQL)

Article
08/22/2024

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

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 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.

Transact-SQL syntax conventions

Syntax

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

Arguments

[ @database_name = ] 'database name'

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

[ @type = ] { 'Database' | 'Log' }

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

Return code values

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.

Examples

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;
GO

EXEC managed_backup.sp_backup_on_demand
    @database_name = 'TestDB',
    @type = 'Database';
GO

Share via