Export (0) Print
Expand All

SQL Server Managed Backup to Windows Azure - Retention and Storage Settings

SQL Server 2014

Limitations and Restrictions

Prerequisites

  • SQL Server Agent should be running.

    Caution note Caution

    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 Windows 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 Windows 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 note Important

    SQL Server Managed Backup to Windows 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 Windows 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.

Arrow icon used with Back to Top link [Top]

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

SQL Server Managed Backup to Windows 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 Windows Azure storage strategically.

Enabling SQL Server Managed Backup to Windows 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 Windows 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 Windows 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 Windows 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 Windows 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 Windows Azure backup enabled at in instance level with default settings. You can also disable SQL Server Managed Backup to Windows Azure for databases if you do not intend to use Windows Azure storage for the backups.

The system stored procedure smart_admin.sp_set_db_backup is used to enable SQL Server Managed Backup to Windows Azure for a specific database. When SQL Server Managed Backup to Windows 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 Windows Azure:

  • The name of the database.

  • The retention period.

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

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

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

Once SQL Server Managed Backup to Windows 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 Windows Azure retains the existing values for other parameters when not specified.

Important note Important

Before configuring SQL Server Managed Backup to Windows 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 Windows 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 Windows 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 noteImportant

    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

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

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

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

smart_backup.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 Windows Azure is enabled for the first time, the following information must be provided in addition to enabling SQL Server Managed Backup to Windows Azure on the instance:

  • The retention period.

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

  • The encryption option. Either specify not to encrypt using @encryption_algorithm = NO_ENCRYPTON 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 Windows Azure retains the existing values when not specified.

Important note Important

Before configuring SQL Server Managed Backup to Windows 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 noteImportant

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

    C:\ PS> 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 noteImportant

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.

You can disable SQL Server Managed Backup to Windows 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 Windows Azure configurations for a specific database, where 1 enables and 0 disables the configuration settings.

To Disable SQL Server Managed Backup to Windows 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

The following procedure is for when you want to disable SQL Server Managed Backup to Windows Azure configuration settings from all the databases that currently have SQL Server Managed Backup to Windows 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 Windows Azure is enabled for the database at a later time. If you want to just pause SQL Server Managed Backup to Windows 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 Windows 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 Windows Azure is configured at the instance level and all the SQL Server Managed Backup to Windows Azure enabled databases on the instance, and executes the system stored procedure sp_set_db_backup to disable SQL Server Managed Backup to Windows 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

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 Windows Azure is enabled for the instance at a later time.

To disable SQL Server Managed Backup to Windows 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:

    C:\ PS> cd SQLSERVER:\SQL\Computer\MyInstance 
    Set-SqlSmartAdmin –BackupEnabled $False
    

There might be times when you need to temporarily pause the SQL Server Managed Backup to Windows 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 Windows Azure service at the instance level. The same stored procedure is used to resume SQL Server Managed Backup to Windows Azure. The @state parameter is used to define whether SQL Server Managed Backup to Windows Azure should be turned off or on.

To Pause SQL Server Managed Backup to Windows 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 Windows Azure Using PowerShell

  1. Start a PowerShell Instance

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

    C:\ PS> cd SQLSERVER:\SQL\Computer\MyInstance 
    Get-SqlSmartAdmin | Set-SqlSmartAdmin –MasterSwitch $False
    

To resume SQL Server Managed Backup to Windows 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 Windows Azure Using PowerShell

  1. Start a PowerShell Instance

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

    C:\ PS> cd SQLSERVER:\SQL\Computer\MyInstance 
    Get-SqlSmartAdmin | Set-SqlSmartAdmin –MasterSwitch $True
    
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft