How to enable SQL Server Managed Backup in Microsoft Azure storage Account using Powershell and SSMS.
==============================================
Step by Step configuration for SQL Server Managed Backup and Restore database from Azure storageHere we are exploring manual using URL and automatic database backup using Microsoft Azure storage in SQL Server 2016 & SQL Server 2017
SQL Server Managed Backup to Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. This feature was introduced in SQL Server 2014 called smart backup now it has been revamped in SQL Server 2016 onward called Managed Backup
Managed Backup – Enhancement
Managed backup was introduced in SQL Server 2014, and allowed an automatic database backup to Microsoft Azure, based on changes done in the database. This feature schedules, performs and maintains the backups–all a DBA needs to do is specify a retention period. In SQL Server 2014, there was not much of control for frequency. In SQL Server 2016, this has been enhanced:
System databases can be backed up.
Backup of databases in simple recovery model is possible.
A backup schedule can be customized based on business need rather than log usage.
There are many objects added in MSDB database in SQL Server 2016 to manage them. They are located under new schema called managed_backup. We can run the below query to find all objects under the new schema.
SELECT name, type_desc FROM msdb.sys.objects
WHERE SCHEMA_ID = SCHEMA_ID('managed_backup')
output:-
| name | type_desc |
| sp_backup_config_basic | SQL_STORED_PROCEDURE |
| sp_backup_config_advanced | SQL_STORED_PROCEDURE |
| sp_backup_config_schedule | SQL_STORED_PROCEDURE |
| sp_create_job | SQL_STORED_PROCEDURE |
| sp_add_task_command | SQL_STORED_PROCEDURE |
| sp_backup_on_demand | SQL_STORED_PROCEDURE |
| sp_set_parameter | SQL_STORED_PROCEDURE |
| sp_get_backup_diagnostics | SQL_STORED_PROCEDURE |
| sp_backup_master_switch | SQL_STORED_PROCEDURE |
| sp_get_encryption_option | SQL_STORED_PROCEDURE |
| sp_get_striping_option | SQL_STORED_PROCEDURE |
| sp_do_backup | SQL_STORED_PROCEDURE |
| fn_backup_db_config | SQL_TABLE_VALUED_FUNCTION |
| fn_backup_instance_config | SQL_TABLE_VALUED_FUNCTION |
| fn_is_master_switch_on | SQL_SCALAR_FUNCTION |
| fn_get_parameter | SQL_TABLE_VALUED_FUNCTION |
| fn_available_backups | SQL_TABLE_VALUED_FUNCTION |
| UQ__fn_avail__FED62726584BD6B2 | UNIQUE_CONSTRAINT |
| fn_get_current_xevent_settings | SQL_TABLE_VALUED_FUNCTION |
| fn_get_health_status | SQL_TABLE_VALUED_FUNCTION |
Benefits
Currently automating backups for multiple databases requires developing a backup strategy, writing custom code, and scheduling backups. Using SQL Server Managed Backup to Windows Azure, you can create a backup plan by specifying only the retention period and storage location.
SQL Server Managed Backup to Windows Azure schedules, performs, and maintains the backups and hence called Smart Backup.
SQL Server Managed Backup to Azure storage can be configured at the database level or at the SQL Server instance level. When configuring at the instance level, any new databases are also backed up automatically.
You can also encrypt the backups for additional security, and you can set up a custom schedule (however not required as it ensures Point in time restore) to control when the backups are taken.
Prerequisites
· Microsoft Azure account
· Azure storage account
· MS SQL Server 2016 / 2017
. SQl Server Agent
. SSMS - 17.X
. DB Server should be on Internet connection
. system clock should be correct
Backup Strategy
· Full Database Backup
Managed backup schedules a full database backup if any of the following is true:o A database is SQL Server Managed Backup to Windows Azure enabled for the first time.
o The log growth since last full database backup is equal to or larger than 1 GB.
o The maximum time interval of one week has passed since the last full database backup.
o The log chain is broken. The most common reason for log chain breaks is probably a backup command issued using Transact-SQL or through the Backup task in SQL Server Management Studio
· Transaction Log Backup
SQL Server Managed Backup to Windows Azure schedules a log backup if any of the following is true:o There is no log backup history that can be found. This is usually true when SQL Server Managed Backup to Windows Azure is enabled for the first time.
o The transaction log space used is 5 MB or larger.
o The maximum time interval of 2 hours since the last log backup is reached.
o Any time the transaction log backup is lagging behind a full database backup. The goal is to keep the log chain ahead of full backup.
Ø Now let’s configure SQL Server managed backup to Microsoft Azure Blob storage:-
================================================================
1.Connect to a Microsoft Azure Subscription using powershell and configured
Azure Storage
Container
Container URL
SAS token and how to generate on demand primary & Secondary Storage key.
2.SQL Server Backup to URL
3.Managed Backup to Microsoft Azure
4.Restoring From Backups Stored in Microsoft Azure
5.File-Snapshot Backups for Database Files in Azure
So lets first start Enable and Configure SQL Server Managed Backup to Microsoft Azure with Default Settings
Create the Azure Blob Container
you can use the Azure Management Portal or Azure PowerShell to create the storage account. The following
New-AzureStorageAccount command creates a storage account named rakmanagedbackupstorage in the West US region.
PowerShell or https://shell.azure.com
============
Create a Resource group
------------------------------
New-AzureRmResourceGroup -Name demomanagedRG -Location "West US" -Tag @{Empty=$null; Department="Home"}
Create a Storage Account
----------------------------------
New-AzureRmStorageAccount -ResourceGroupName "demomanagedRG" -AccountName "rakmanagedbackupstorage" -Location "West US" -SkuName "Standard_LRS"
Create a blob container for the backup files: You can create a blob container in the Azure Management Portal or with Azure PowerShell.
The following New-AzureStorageContainer command creates a blob container named rakbackupcontainer in the rakmanagedbackupstorage storage account.
Create a storage context
-------------------------------
$context = New-AzureStorageContext -StorageAccountName rakmanagedbackupstorage -StorageAccountKey (Get-AzureRMStorageAccountKey -StorageAccountName rakmanagedbackupstorage -ResourceGroupName demomanagedRG).Value[0]
Create a Container
--------------------------
New-AzureStorageContainer -Name rakbackupcontainer -Context $context
Generate a Shared Access Signature (SAS):
-------------------------------------------------------
To access the container, you must create a SAS. This can be done in some tools, code, and Azure PowerShell. The following New-AzureStorageContainerSASToken command creates SAS token for the rakbackupcontainer blob container that expires in one year.
Lets Generate a Shared Access Signature (SAS)
New-AzureStorageContainerSASToken -Name rakbackupcontainer -Permission rwdl -ExpiryTime (Get-Date).AddYears(1) -FullUri -Context $context
The output for this command will contain both the URL to the container and the SAS token. The following is an example:
https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer?sv=2017-04-17&sr=c&sig=w%2BbRLmeIxsBIx6obOSsvWWm%2FIB0F1zVVMM8LMKExF8E%3D&se=2019-03-13T15%3A45%3A54Z&sp=rwdl
In the previous example, separate the container URL 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.
Container URL: https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer
SAS token: sv=2017-04-17&sr=c&sig=w%2BbRLmeIxsBIx6obOSsvWWm%2FIB0F1zVVMM8LMKExF8E%3D&se=2019-03-13T15%3A45%3A54Z&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.
Using powershell if you want to get Access key for storage account use below command
(Get-AzureRMStorageAccountKey -StorageAccountName rakmanagedbackupstorage -ResourceGroupName demomanagedRG).Value[0]
output of this above powershell command will go to the Account key
Put Storage account Name & Account key to remain connected to Microsoft Azure Storage
-----------------------------------------------------------------------------------------------------------
Create a credential with help of these details, then only backup to URL will work.
CREATE CREDENTIAL [https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer]
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'sv=2017-04-17&sr=c&sig=w%2BbRLmeIxsBIx6oXXXXXm%2FIB0F1zVVMM8LMKExF8E%3D&se=2019-03-13T15%3A45%3A54Z&sp=rwdl'
Backup to URL
==============
BACKUP DATABASE [master] TO URL = N'https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer/master_backup_2018_03_13_175305.bak'
WITH NOFORMAT, NOINIT, NAME = N'master-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Enable SQL Server Managed Backup to Microsoft 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:
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.
CREATE CREDENTIAL [https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer]
WITH IDENTITY = 'Shared Access Signature',
SECRET = 'sv=2017-04-17&sr=c&sig=w%2BbRLmeIxsBIx6oXXXXXm%2FIB0F1zVVMM8LMKExF8E%3D&se=2019-03-13T15%3A45%3A54Z&sp=rwdl'
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 30.
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.
Here i am taking Backup of only one database rakeshmydb
Use msdb;
GO
EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup = 1,
@database_name = 'rakeshmydb',
@container_url = 'https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer',
@retention_days = 30
GO
for all databases...
-----------------------
Use msdb;
GO
EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup = 1,
@database_name= NULL,
@container_url = 'https://rakmanagedbackupstorage.blob.core.windows.net/rakbackupcontainer',
@retention_days = 30
GO
Note:-
When enabled on instance level, it is only applying the configuration to newly created databases. If you want the settings to apply for existing databases, then you need to enable it on each database
To check the settings for each database, run the following T-SQL:
Use msdb;
SELECT * FROM managed_backup.fn_backup_db_config (NULL);
The following example returns the SQL Server Managed Backup to Microsoft Azure default configuration settings for the instance it is executed on:
Use msdb;
GO
SELECT * FROM managed_backup.fn_backup_instance_config ();
Configures automated or custom scheduling options for SQL Server Managed Backup to Microsoft Azure.
USE msdb;
GO
EXEC managed_backup.sp_backup_config_schedule
@database_name = 'rakeshmydb'
,@scheduling_option = 'Custom'
,@full_backup_freq_type = 'daily'
--,@days_of_week = 'Tuesday'
,@backup_begin_time = '17:30'
,@backup_duration = '02:00'
,@log_backup_freq = '00:05'
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:
Setup Database Mail if it is not already enabled on the instance. For more information, see Configure Database Mail.
Configure SQL Server Agent Notification to use Database Mail. For more information, see Configure SQL Server Agent Mail to Use Database Mail.
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 = 'kushagra.rakesh@gmail.com'
View backup files in the Microsoft Azure Storage Account: Connect to the storage account from SQL Server Management Studio or the Azure Management 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.
To disable SQL Server Managed Backup for a database 'rakeshmydb' to Microsoft Azure default configuration settings:
EXEC msdb.managed_backup.sp_backup_config_basic
@database_name = 'rakeshmydb'
,@enable_backup = 0;
GO
In order to disable all databases :-
-- 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
Then 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
To Pause SQL Server Managed Backup to Microsoft Azure Services Using Transact-SQL:
Use msdb;
GO
EXEC managed_backup.sp_backup_master_switch @new_state=0;
Go
To resume SQL Server Managed Backup to Microsoft Azure Using Transact-SQL
Use msdb;
Go
EXEC managed_backup.sp_backup_master_switch @new_state=1;
GO
The following example lists all the available backups backed up through SQL Server Managed Backup to Microsoft Azure for the database ‘rakeshmydb’
SELECT *
FROM managed_backup.fn_available_backups ('rakeshmydb')
The following example returns aggregated error counts for the last 30 minutes from the time it was executed.
SELECT *
FROM managed_backup.fn_get_health_status(NULL, NULL)
The following example returns the aggregated error counts for 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)
SELECT *
FROM managed_backup.fn_get_health_status(@startofweek, @endofweek)
The following example returns the SQL Server Managed Backup to Microsoft Azure default configuration settings for the instance it is executed on:
Use msdb;
GO
SELECT * FROM managed_backup.fn_backup_instance_config ();
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
Question:- DB1 is a database that is hosted on an instance of SQL Server 2016. You are asked to run full and T-log backups from the database to Microsoft Azure
You configure the Azure and SQL Server prerequsites for SQL Server Managed Backup to Micrsoft Azure.
You enable Database Mail on the server Instance.
You need to enable email notifications of errors an warning generated during backups. EMail should be sent to
your email address.
Which function or procedure should you run?
1.Managed_backup.sp_backup_config_advaned
2.Managed_backup.fn_backup_db_config
3.Managed_backup.sp_set_parameter
4.Managed_backup.sp_back_master_switch
SQL Server Backup to URL Best Practices and Troubleshooting
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-best-practices-and-troubleshooting?view=sql-server-2017
Below topics coming soon.
4.Restoring From Backups Stored in Microsoft Azure storage.
5.File-Snapshot Backups for Database Files in Azure
You can refer this link too:-
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-2017
