Enable SQL Server managed backup to Azure

Artikel
03/06/2023

Applies to: SQL Server

This topic describes how to enable SQL Server managed backup to Microsoft Azure with default settings at both the database and instance level. It also describes how to enable email notifications and how to monitor backup activity.

This tutorial uses Azure PowerShell. Before starting the tutorial, download and install Azure PowerShell.

Important

If you also want to enable advanced options or use a custom schedule, configure those settings first before enabling SQL Server managed backup to Microsoft Azure. For more information, see Configure advanced options for SQL Server managed backup to Microsoft Azure.

Create the Azure Blob Storage container

The process requires an Azure account. If you already have an account, go to the next step. Otherwise, you can get started with a free trial or explore purchase options.

For more information about storage accounts, see About Azure Storage Accounts.

Azure CLI
PowerShell

Sign in to your Azure account.
```
az login
```
Create an Azure storage account. If you already have a storage account, go to the next step. The following command creates a storage account named <backupStorage> in the East US region.
```
az storage account create -n <backupStorage> -l "eastus" --resource-group <resourceGroup>
```

Create a blob container named <backupContainer> for the backup files.

$keys = az storage account keys list --account-name <backupStorage> --resource-group <resourceGroup> | ConvertFrom-Json
az storage container create --name <backupContainer> --account-name <backupStorage> --account-key $keys[0].value

Generate a Shared Access Signature (SAS) to access the container. The following command creates a SAS token for the <backupContainer> blob container that expires in one year.
```
az storage container generate-sas --name <backupContainer> --account-name <backupStorage> --account-key $keys[0].value
```

The following command logs in to your Azure account.

Connect-AzAccount
Set-AzContext -SubscriptionId "<subscriptionId>"

Create an Azure storage account. If you already have a storage account, go to the next step. The following command creates a storage account named <backupStorage> in the East US region.
```
New-AzStorageAccount -StorageAccountName <backupStorage> -Location "EAST US" -ResourceGroupName <resourceGroup> -SkuName Standard_GRS
```

Create a blob container named <backupContainer> for the backup files.

$context = New-AzStorageContext -StorageAccountName <backupStorage> -StorageAccountKey (Get-AzStorageAccountKey -Name <backupStorage> -ResourceGroupName <resourceGroup>).Value[0]  
New-AzStorageContainer -Name <backupContainer> -Context $context

Generate a Shared Access Signature (SAS) to access the container. The following command creates a SAS token for the <backupContainer> blob container that expires in one year.
```
New-AzStorageContainerSASToken -Name <backupContainer> -Permission rwdl -ExpiryTime (Get-Date).AddYears(1) -FullUri -Context $context 
```

Note

These steps can also be accomplished using the Azure portal.

The output will contain either the URL to the container and/or the SAS token. The following is an example:

https://managedbackupstorage.blob.core.windows.net/backupcontainer?sv=2014-02-14&sr=c&sig=xM2LXVo1Erqp7LxQ%9BxqK9QC6%5Qabcd%9LKjHGnnmQWEsDf%5Q%se=2015-05-14T14%3B93%4V20X&sp=rwdl

If the URL is included, separate it from the SAS token at the question mark (do not include the question mark). For example, the previous output would result in the following two values.

Type	Output
Container URL	https://managedbackupstorage.blob.core.windows.net/backupcontainer
SAS token	sv=2014-02-14&sr=c&sig=xM2LXVo1Erqp7LxQ%9BxqK9QC6%5Qabcd%9LKjHGnnmQWEsDf%5Q%se=2015-05-14T14%3B93%4V20X&sp=rwdl

Record the container URL and SAS for use in creating a SQL CREDENTIAL. For more information about SAS, see Shared Access Signatures, Part 1: Understanding the SAS Model.

Enable managed backup to Azure

Create a SQL Credential for the SAS URL: Use the SAS token to create a SQL Credential for the blob container URL. In SQL Server Management Studio, use the following Transact-SQL query to create the credential for your blob container URL based on the following example:
```
CREATE CREDENTIAL [https://managedbackupstorage.blob.core.windows.net/backupcontainer]   
WITH IDENTITY = 'Shared Access Signature',  
SECRET = 'sv=2014-02-14&sr=c&sig=xM2LXVo1Erqp7LxQ%9BxqK9QC6%5Qabcd%9LKjHGnnmQWEsDf%5Q%se=2015-05-14T14%3B93%4V20X&sp=rwdl'  
```
Ensure SQL Server Agent service is Started and Running: Start SQL Server Agent if it is not currently running. SQL Server managed backup to Microsoft Azure requires SQL Server Agent to be running on the instance to perform backup operations. You may want to set SQL Server Agent to run automatically to make sure that backup operations can occur regularly.
Determine the retention period: Determine the retention period for the backup files. The retention period is specified in days and can range from 1 to 90.
Enable and configure SQL Server managed backup to Microsoft Azure : Start SQL Server Management Studio and connect to the target SQL Server instance. From the query window run the following statement after you modify the values for the database name, container url, and retention period per your requirements:

Important

To enable managed backup at the instance level, specify NULL for the database_name parameter.
```
USE msdb;  
GO  
EXEC msdb.managed_backup.sp_backup_config_basic   
 @enable_backup = 1,   
 @database_name = 'yourdatabasename',  
 @container_url = 'https://managedbackupstorage.blob.core.windows.net/backupcontainer',   
 @retention_days = 30  
GO  
```
SQL Server managed backup to Microsoft Azure is now enabled on the database you specified. It may take up to 15 minutes for the backup operations on the database to start to run.
Review Extended Event Default Configuration: Review the Extended Event settings by running the following Transact-SQL statement.
```
SELECT * FROM msdb.managed_backup.fn_get_current_xevent_settings()  
```
You should see that Admin, Operational, and Analytical channel events are enabled by default and cannot be disabled. This should be sufficient to monitor the events that require manual intervention. You can enable debug events, but the debug channels include informational and debug events that SQL Server managed backup to Microsoft Azure uses to detect issues and solve them.
Enable and Configure Notification for Health Status: SQL Server managed backup to Microsoft Azure has a stored procedure that creates an agent job to send out e-mail notifications of errors or warnings that may require attention. The following steps describe the process to enable and configure e-mail notifications:
1. Setup Database Mail if it is not already enabled on the instance. For more information, see Configure Database Mail.
2. Configure SQL Server Agent Notification to use Database Mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.
3. Enable e-mail notifications to receive backup errors and warnings: From the query window, run the following Transact-SQL statements:
```
EXEC msdb.managed_backup.sp_set_parameter  
@parameter_name = 'SSMBackup2WANotificationEmailIds',  
@parameter_value = '<email1;email2>'  
```
View backup files in the Azure Storage Account: Connect to the storage account from SQL Server Management Studio or the Azure portal. You will see any backup files in the container you specified. Note that you might see a database and a log backup within 5 minutes of enabling SQL Server managed backup to Microsoft Azure for the database.

Monitor the Health Status: You can monitor through e-mail notifications you configured previously, or actively monitor the events logged. The following are some example Transact-SQL Statements used to view the events:

--  view all admin events  
USE msdb;  
GO  
DECLARE @startofweek datetime  
DECLARE @endofweek datetime  
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)   
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)  

DECLARE @eventresult TABLE  
(event_type nvarchar(512),  
event nvarchar (512),  
timestamp datetime  
)  

INSERT INTO @eventresult  

EXEC managed_backup.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek  

SELECT * from @eventresult  
WHERE event_type LIKE '%admin%'

-- to enable debug events  
USE msdb;  
GO  
EXEC managed_backup.sp_set_parameter 'FileRetentionDebugXevent', 'True'

--  View all events in the current week  
USE msdb;  
GO  
DECLARE @startofweek datetime  
DECLARE @endofweek datetime  
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)   
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)  

EXEC managed_backup.sp_get_backup_diagnostics @begin_time = @startofweek, @end_time = @endofweek;

The steps described in this section are specifically for configuring SQL Server managed backup to Microsoft Azure for the first time on the database. You can modify the existing configurations using the same system stored procedures and provide the new values.

Delen via

Enable SQL Server managed backup to Azure

Create the Azure Blob Storage container

Enable managed backup to Azure

See also

Feedback

Aanvullende resources