managed_backup.sp_backup_master_switch (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later versions
Pauses or resumes the SQL Server managed backup to Microsoft Azure.
Use managed_backup.sp_backup_master_switch
to temporarily pause and then resume SQL Server managed backup to Microsoft Azure. This procedure makes sure that all the configurations settings remain, and are retained when the operations resume. When SQL Server managed backup to Microsoft Azure is paused the retention period isn't enforced.
In other words, there's no check to determine:
- whether files should be deleted from storage
- if there are corrupted backup files
- if there's a break in the log chain.
Transact-SQL syntax conventions
EXEC managed_backup.sp_backup_master_switch [ @new_state = ] { 0 | 1 }
[ ; ]
Set the state of SQL Server managed backup to Microsoft Azure. @new_state is bit. When set to a value of 0
, the operations are paused, and when set to a value of 1
, the operation resume.
0
(success) or 1
(failure).
Requires membership in db_backupoperator database role, with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on sp_delete_backuphistory
stored procedure.
The following example can be used to pause SQL Server managed backup to Microsoft Azure on the instance it's executed on:
USE msdb;
GO
EXEC managed_backup.sp_backup_master_switch @new_state = 0;
Go
The following example can be used to resume SQL Server managed backup to Microsoft Azure.
USE msdb;
GO
EXEC managed_backup.sp_backup_master_switch @new_state = 1;
Go