disable-sql-server-managed-backup-to-microsoft-azure.md

title	Disable managed back up to Azure Blob Storage
description	This article shows you how to disable or pause SQL Server Managed Backup to Microsoft Azure at both the database and instance levels using Transact-SQL.
author	MashaMSFT
ms.author	mathoma
ms.date	03/04/2017
ms.service	sql
ms.subservice	backup-restore
ms.topic	how-to

Disable SQL Server Managed Backup to Microsoft Azure

[!INCLUDE SQL Server] This topic describes how to disable or pause [!INCLUDEss-managed-backup] at both the database and instance levels.

Disable [!INCLUDEss-managed-backup] for a database

You can disable [!INCLUDEss-managed-backup] settings by using the system stored procedure, managed_backup.sp_backup_config_basic (Transact-SQL). The @enable_backup parameter is used to enable and disable [!INCLUDEss-managed-backup] configurations for a specific database, where 1 enables and 0 disables the configuration settings.

To Disable [!INCLUDEss-managed-backup] for a specific database:

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

EXEC msdb.managed_backup.sp_backup_config_basic  
                @database_name = 'TestDB'   
                ,@enable_backup = 0;  
GO

Note

You might also need to set the @container_url parameter depending on your configuration.

Disable [!INCLUDEss-managed-backup] for all the databases on the Instance

The following procedure is for when you want to disable [!INCLUDEss-managed-backup] configuration settings from all the databases that currently have [!INCLUDEss-managed-backup] enabled on the instance. The configuration settings like the storage URL, retention, and the SQL Credential will remain in the metadata and can be used if [!INCLUDEss-managed-backup] is enabled for the database at a later time. If you want to just pause [!INCLUDEss-managed-backup] services temporarily, you can use the master switch explained in the later sections of this topic.

To disable [!INCLUDEss-managed-backup] for all the databases:

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. The following example identifies if [!INCLUDEss-managed-backup] is configured at the instance level and all the [!INCLUDEss-managed-backup] enabled databases on the instance, and executes the system stored procedure sp_backup_config_basic to disable [!INCLUDEss-managed-backup].

-- Create a working table to store the database names  
Declare @DBNames TABLE  
  
       (  
             RowID int IDENTITY PRIMARY KEY  
             ,DBName varchar(500)  
  
       )  
-- Define the variables  
DECLARE @rowid int  
DECLARE @dbname varchar(500)  
DECLARE @SQL varchar(2000)  
-- Get the database names from the system function  
  
INSERT INTO @DBNames (DBName)  
  
SELECT db_name  
       FROM   
  
       msdb.managed_backup.fn_backup_db_config (NULL)  
       WHERE is_managed_backup_enabled = 1 
       AND is_dropped = 0
  
       --Select DBName from @DBNames  
  
       select @rowid = min(RowID)  
       FROM @DBNames  
  
       WHILE @rowID IS NOT NULL  
       Begin  
  
             Set @dbname = (Select DBName From @DBNames Where RowID = @rowid)  
             Begin  
             Set @SQL = 'EXEC msdb.managed_backup.sp_backup_config_basic    
                @database_name= '''+'' + @dbname+ ''+''',   
                @enable_backup=0'  
  
            EXECUTE (@SQL)  
  
             END  
             Select @rowid = min(RowID)  
             From @DBNames Where RowID > @rowid  
  
       END  

To review the configuration settings for all the databases on the instance, use the following query:

Use msdb;  
GO  
SELECT * FROM managed_backup.fn_backup_db_config (NULL);  
GO  

Disable Default [!INCLUDEss-managed-backup] settings for the Instance

Default settings at the instance level apply to all new databases created on that instance. If you no longer need or require default settings, you can disable this configuration by using the managed_backup.sp_backup_config_basic system stored procedure with the @database_name parameter set to NULL. Disabling does not remove the other configuration settings like the storage URL, retention setting, or the SQL Credential name. These settings will be used if [!INCLUDEss-managed-backup] is enabled for the instance at a later time.

To disable [!INCLUDEss-managed-backup] default configuration settings:

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

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

Pause [!INCLUDEss-managed-backup] at the Instance Level

There might be times when you need to temporarily pause the [!INCLUDEss-managed-backup] services for a short period time. The managed_backup.sp_backup_master_switch system stored procedure allows you to disable [!INCLUDEss-managed-backup] service at the instance level. The same stored procedure is used to resume [!INCLUDEss-managed-backup]. The @state parameter is used to define whether [!INCLUDEss-managed-backup] should be turned off or on.

To Pause [!INCLUDEss-managed-backup] Services Using Transact-SQL:

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and then click Execute

Use msdb;  
GO  
EXEC managed_backup.sp_backup_master_switch @new_state=0;  
Go

To resume [!INCLUDEss-managed-backup] Using Transact-SQL

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and then click Execute.

Use msdb;  
Go  
EXEC managed_backup.sp_backup_master_switch @new_state=1;  
GO  

See Also

Enable SQL Server Managed Backup to Microsoft Azure