SQL Server Managed Backup to Azure - Retention and Storage Settings

This topic describes the basic steps to configure SQL Server Managed Backup to Microsoft Azure for a database and to configure default settings for the instance. The topic also describes the steps necessary to pause and resume SQL Server Managed Backup to Microsoft Azure services for the instance.

For a complete walkthrough of setting up SQL Server Managed Backup to Microsoft Azure see Setting up SQL Server Managed Backup to Azure and Setting up SQL Server Managed Backup to Azure for Availability Groups.

Before You Begin

Limitations and Restrictions

• Do not enable SQL Server Managed Backup to Microsoft Azure on databases that are currently using maintenance plans or log shipping. For more information on interoperability and coexistence with other SQL Server features, see SQL Server Managed Backup to Azure: Interoperability and Coexistence

Prerequisites

• SQL Server Agent should be running.

  Warning

  If SQL Server Agent is stopped for a period of time and then restarted, you may see an increased backup activity depending on the length of time elapsed between the stop and start of SQL Agent, and there might be a backlog of log backups waiting to run. Consider configuring SQL Server Agent to start automatically on start up.

• A Azure storage account and a SQL Credential that stores the authentication information to the storage account should both be created before configuring SQL Server Managed Backup to Microsoft Azure. For more information see Introduction to Key Components and Concepts section of the SQL Server Backup to URL topic, and Lesson 2: Create a SQL Server Credential.

  Important

  SQL Server Managed Backup to Microsoft Azure creates the necessary containers to store the backups. The container name is created using 'machine name-instance name' format. For AlwaysOn Availability Groups the container is named using the GUID of the availability group.

Security

Permissions

To run the stored procedures that enable SQL Server Managed Backup to Microsoft Azure, you must be a either a System Administrator or member in the db_backupoperator database role with ALTER ANY CREDENTIAL permissions, and EXECUTE permissions on the sp_delete_backuphistory, and smart_admin.sp_backup_master_switch stored procedures. Stored procedures and functions used to review the existing settings typically require Execute permissions on the stored procedure and Select on the function respectively.

Considerations for enabling SQL Server Managed Backup to Microsoft Azure for Databases and Instances

SQL Server Managed Backup to Microsoft Azure can be enabled for individual databases separately or for the entire instance. The choices depend on the recoverability requirements for the databases on the instance, requirements for managing multiple databases and instances, and using Azure storage strategically.

Enabling SQL Server Managed Backup to Microsoft Azure at the Database Level

If a database has specific requirements for backup and retention period (recoverability SLA) that is different from other databases on the instance, configure SQL Server Managed Backup to Microsoft Azure at the database level for this database. Database level settings override instance level configuration settings. However both these options can be used together on the same instance. Following is a list of advantages and considerations when enabling SQL Server Managed Backup to Microsoft Azure at the database level.

• More granular: Separate configuration settings for each database. Can support different retention periods for individual databases.

• Overrides instance level settings for the database.

• Can be used to reduce storage costs by selecting individual databases to be backed up.

• Requires managing each database

Enabling SQL Server Managed Backup to Microsoft Azure at the Instance Level with Default Settings

Use this setting if most of the databases in the instance have the same requirements for backup and retention policies, or if you want new database instances to automatically be backed up on creation. A few databases that are exception to the policy can still be configured individually. Following is a list of advantages and considerations when enabling SQL Server Managed Backup to Microsoft Azure at the instance level.

• Automation at the instance level: Common settings applied to automatically for new databases added thereafter.

• New databases will be automatically backed up soon after they are created on the instances

• Can be applied to databases that have the same retention period requirements.

• You can still configure individual databases that require a different retention period even with SQL Server Managed Backup to Microsoft Azure backup enabled at in instance level with default settings. You can also disable SQL Server Managed Backup to Microsoft Azure for databases if you do not intend to use Azure storage for the backups.

Enable and Configure SQL Server Managed Backup to Microsoft Azure for a Database

The system stored procedure smart_admin.sp_set_db_backup is used to enable SQL Server Managed Backup to Microsoft Azure for a specific database. When SQL Server Managed Backup to Microsoft Azure is enabled for the first time on the database, the following information must be specified in addition to enabling SQL Server Managed Backup to Microsoft Azure:

• The name of the database.

• The retention period.

• SQL Credential used to authenticate to the Azure storage account.

• Either specify not to encrypt using @encryption_algorithm = NO_ENCRYPTION or specify a supported encryption algorithm. For more information on encryption, see Backup Encryption.

SQL Server Managed Backup to Microsoft Azure for database level configuration is only supported through Transact-SQL.

Once SQL Server Managed Backup to Microsoft Azure is enabled for a database this information is persisted. If you are changing the configuration, only the database name and the setting you want to change is required, SQL Server Managed Backup to Microsoft Azure retains the existing values for other parameters when not specified.

Important

Before configuring SQL Server Managed Backup to Microsoft Azure on a database it might be useful to existing configuration if any. The step to reviewing configuration settings for a database is explained later in this section.

• Using Transact-SQL:

  If you are enabling SQL Server Managed Backup to Microsoft Azure for the first time, the required parameters are: @database_name, @credential_name, @encryption_algorithm, @enable_backup The @storage_url parameter is optional. If you do not provide a value for the @storage_url parameter, the value is derived using the storage account information from the SQL Credential. If you provide the storage URL you should only provide the URL for the root of the storage account, and must match the information in the SQL Credential you specified.

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute. This example enables SQL Server Managed Backup to Microsoft Azure for the database 'TestDB'. The retention period is set to 30 days. This sample uses the encryption option by specifying the encryption algorithm and the encryptor information.

  Use msdb;  
  GO  
  EXEC smart_admin.sp_set_db_backup   
                  @database_name='TestDB'   
                  ,@enable_backup=1  
                  ,@retention_days =30   
                  ,@credential_name ='MyCredential'  
                  ,@encryption_algorithm ='AES_256'  
                  ,@encryptor_type= 'Certificate'  
                  ,@encryptor_name='MyBackupCert'  
  GO
  

  Important

  The retention period can be set to any value from 1 to 30 days.

  For more information on creating a certificate for encryption, see the Create a Backup Certificate step in Create an Encrypted Backup.

  For more information on this stored procedure, see smart_admin.set_db_backup (Transact-SQL)

  To review the configuration settings for a database use the following query:

  Use msdb  
  GO  
  SELECT * FROM smart_admin.fn_backup_db_config('TestDB')  
  

Enable and Configure Default SQL Server Managed Backup to Microsoft Azure settings for the Instance

You can enable and configure default SQL Server Managed Backup to Microsoft Azure settings at the instance level in two ways: By using the system stored procedure smart_admin.set_instance_backup or SQL Server Management Studio. The two methods are explained below:

smart_admin.set_instance_backup:. By specifying the value 1 for @enable_backup parameter, you can enable backup and set the default configurations. Once applied at the instance level, these default settings are applied to any new database that is added to this instance. When SQL Server Managed Backup to Microsoft Azure is enabled for the first time, the following information must be provided in addition to enabling SQL Server Managed Backup to Microsoft Azure on the instance:

• The retention period.

• SQL Credential used to authenticate to the Azure storage account.

• The encryption option. Either specify not to encrypt using @encryption_algorithm = NO_ENCRYPTION or specify a supported encryption algorithm. For more information on encryption, see Backup Encryption.

Once enabled these settings are persisted. If you are changing the configuration, only the database name and the setting you want to change is required. SQL Server Managed Backup to Microsoft Azure retains the existing values when not specified.

Important

Before configuring SQL Server Managed Backup to Microsoft Azure on an instance, it might be useful to check for existing configuration, if any. The step to reviewing configuration settings for a database is explained later in this section.

SQL Server Management Studio: To do this task in SQL Server Management Studio, go the object explorer, expand the Management node, and right click on Managed Backup. Select Configure. This opens the Managed Backup dialog. Use this dialog to specify the retention period, SQL Credential, Storage URL, and the encryption settings. For specific help with this dialog, see Configure Managed Backup (SQL Server Management Studio).

Using Transact-SQL

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

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

Use msdb;  
Go  
   EXEC smart_admin.sp_set_instance_backup  
                @retention_days=30   
                ,@credential_name='sqlbackuptoURL'  
                ,@encryption_algorithm ='AES_128'  
                ,@encryptor_type= 'Certificate'  
                ,@encryptor_name='MyBackupCert'  
                ,@enable_backup=1;  
GO  
  

Important

The retention period can be set to any value from 1 to 30 days.

For more information on creating a certificate for encryption, see the Create a Backup Certificate step in Create an Encrypted Backup.

To view the default configuration settings for the instance, use the following query:

Use msdb;  
GO  
SELECT * FROM smart_admin.fn_backup_instance_config ();

Using PowerShell

1. Start a PowerShell Instance

2. Run the following script after modifying it to suit your settings

   cd SQLSERVER:\SQL\Computer\MyInstance
   $encryptionOption = New-SqlBackupEncryptionOption -EncryptionAlgorithm Aes128 -EncryptorType ServerCertificate -EncryptorName "MyBackupCert"  
   Get-SqlSmartAdmin | Set-SqlSmartAdmin -BackupEnabled $True -BackupRetentionPeriodInDays 10 -EncryptionOption $encryptionOption  
   

Important

When you create a new database after configuring the default settings, it may take up to 15 minutes for the database to be configured with the default settings. This also applies to databases that are changed from Simple to Full or Bulk-Logged recovery model.

Disable SQL Server Managed Backup to Microsoft Azure for a database

You can disable SQL Server Managed Backup to Microsoft Azure settings by using the sp_set_db_backup system stored procedure. The @enableparameter is used to enable and disable SQL Server Managed Backup to Microsoft Azure configurations for a specific database, where 1 enables and 0 disables the configuration settings.

To Disable SQL Server Managed Backup to Microsoft Azure for a specific database:

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

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

Use msdb;  
Go  
EXEC smart_admin.sp_set_db_backup   
                @database_name='TestDB'   
                ,@enable_backup=0;  
GO

Disable SQL Server Managed Backup to Microsoft Azure for all the databases on the Instance

The following procedure is for when you want to disable SQL Server Managed Backup to Microsoft Azure configuration settings from all the databases that currently have SQL Server Managed Backup to Microsoft Azure 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 SQL Server Managed Backup to Microsoft Azure is enabled for the database at a later time. If you want to just pause SQL Server Managed Backup to Microsoft Azure services temporarily, you can use the master switch explained in the following sections later in this topic.

To disable SQL Server Managed Backup to Microsoft Azurefor all the databases:

1. Connect to the Database Engine.

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 SQL Server Managed Backup to Microsoft Azure is configured at the instance level and all the SQL Server Managed Backup to Microsoft Azure enabled databases on the instance, and executes the system stored procedure sp_set_db_backup to disable SQL Server Managed Backup to Microsoft Azure.

-- 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
  
       smart_admin.fn_backup_db_config (NULL)  
       WHERE is_smart_backup_enabled = 1  
  
       --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 smart_admin.sp_set_db_backup    
                @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 smart_admin.fn_backup_db_config (NULL);  
GO

Disable Default SQL Server Managed Backup to Microsoft Azure 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 smart_admin.sp_set_instance_backup System stored procedure. 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 SQL Server Managed Backup to Microsoft Azure is enabled for the instance at a later time.

To disable SQL Server Managed Backup to Microsoft Azure default configuration settings:

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

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

   Use msdb;  
   Go  
   EXEC smart_admin.sp_set_instance_backup  
                   @enable_backup=0;  
   GO
   

Using PowerShell

1. Start a PowerShell Instance

2. Run the following script:

   cd SQLSERVER:\SQL\Computer\MyInstance
   Set-SqlSmartAdmin -BackupEnabled $False  
   

Pause SQL Server Managed Backup to Microsoft Azure at the Instance Level

There might be times when you need to temporarily pause the SQL Server Managed Backup to Microsoft Azure services for a short period time. The smart_admin.sp_backup_master_switch system stored procedure allows you to disable SQL Server Managed Backup to Microsoft Azure service at the instance level. The same stored procedure is used to resume SQL Server Managed Backup to Microsoft Azure. The @state parameter is used to define whether SQL Server Managed Backup to Microsoft Azure should be turned off or on.

To Pause SQL Server Managed Backup to Microsoft Azure Services Using Transact-SQL:

1. Connect to the Database Engine.

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 smart_admin.sp_backup_master_switch @new_state=0;  
Go  
  

To pause SQL Server Managed Backup to Microsoft Azure Using PowerShell

1. Start a PowerShell Instance

2. Run the following script after you modify it to suit your settings

   cd SQLSERVER:\SQL\Computer\MyInstance
   Get-SqlSmartAdmin | Set-SqlSmartAdmin -MasterSwitch $False  
   

To resume SQL Server Managed Backup to Microsoft Azure Using Transact-SQL

1. Connect to the Database Engine.

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 smart_admin. sp_backup_master_switch @new_state=1;  
GO

To resume SQL Server Managed Backup to Microsoft Azure Using PowerShell

1. Start a PowerShell Instance

2. Run the following script after you modify it to suit your settings

   cd SQLSERVER:\SQL\Computer\MyInstance
   Get-SqlSmartAdmin | Set-SqlSmartAdmin -MasterSwitch $True