Introducing SQL Server Managed Backup aka Smart Backup and Restore from Azure storage SQL 2014

Step by Step configuration for SQL Server Managed Backup aka Smart Backup and Restore from Azure storage


From  SQL Server 2016 onwords follow this link:-

https://kushagrarakesh.blogspot.com/2018/03/enable-sql-server-managed-backup-to.html

Here we are  Exploring manual and automatic database backup using Microsoft Azure storage in SQL Server 2014

SQL Server Managed Backup to Windows Azure manages and automates SQL Server backups to Microsoft Azure Blob storage. This feature is introduced in SQL Server 2014.

Ø 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 Windows Azure 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

·        SQL Server Agent

Here you will get step by step process to  

backup a database to a storage account/Containers/manage access key on Windows Azure

Now coming to SQL Server Managed Backup aka Smart Backup

Ø 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:-
================================================================

·        Create SQL Credential

Can create SQL credential using GUI or through query.

Through GUI the steps are as:

SQL Server management studio à Object Explorer à Security à New Credential à Details of storage account and its Manage key

Through query:

USE [master]

GO

CREATE CREDENTIAL [Mycredential] WITH IDENTITY = <Azure Storage Account name>, SECRET = <Manage Access keys>

GO

BACKUP DATABASE [master] TO 
 URL = N'https://<Your Storage Account Name>.blob.core.windows.net/rakbackupcontainer/master_backup_2018_06_23_122216.bak' 
 WITH  CREDENTIAL = N'Mycredential' , NOFORMAT, NOINIT,  NAME = N'master-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, 
  STATS = 10

GO

Now test this credential by taking a adhoc backup to URL and it may fail with following error:

Backup failed for Server 'XXXXX'.  (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://XXXXX.blob.core.windows.net/AG_Database_backup_2015_09_05_174323.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (403) Forbidden.. (Microsoft.SqlServer.Smo)

This error is mostly encountered when the SQL Credential is not configured properly. Check its configuration and ensure the storage account name and the Manage Access keys are provided correctly.

·        Now second step is to enable Managed backup

SSMS -->Object Explorer --> Management --> Managed backup

Here File retention is the number of days the backup file will be retained in blob storage and default value is 30. If you will select the option “Enable Managed Backup” then the backup will start from then onwards. The backup files can be encrypted using the selected algorithm and need to have a certificate. Let’s create and use a self-signed certificate here:

-- Creating self-signed certificate

USE master

go

CREATE CERTIFICATE manage_test

   ENCRYPTION BY PASSWORD = 'Password@1'

   WITH SUBJECT = 'To encrypt manage backups',

   EXPIRY_DATE = '20201031';

GO

            Once the managed backup is enabled, the full backup and log backup of the database will be taken as per the schedule discussed in the beginning of the this blog.

Some useful queries to troubleshoot managed backup

·        To take backup on demand:

EXEC smart_admin.sp_backup_on_demand 'AdventureWorks2014','Database'

Msg 45207, Level 17, State 4, Procedure sp_add_task_command, Line 114 SQL Server Managed Backup to Windows Azure cannot schedule a 'Database' backup for the database, 'AdventureWorks2014', because SQL Server Managed Backup to Windows Azure is not enabled for the database.

·        To check if the managed backup is enabled for a database or not, run following command:

SELECT * FROM smart_admin.fn_backup_db_config ('test2')  
-- check column is_managed_backup

        To enable managed backup for a database:

Use msdb;

GO

EXEC smart_admin.sp_set_db_backup

                @database_name='AdventureWorks2014'

                ,@retention_days=30

                ,@encryption_algorithm = N'NO_ENCRYPTION'

                ,@credential_name='MyCredential'

                ,@enable_backup=1;

·        To check backup files created in azure storage:

select * from [msdb].[dbo].[smart_backup_files]

Ø Restore from the backup file in Azure storage

===========================================
SSMS à Object Explorer à Database à Restore Database

Go to Device and click on elipsys(…) àSelect ‘Backup media type’ as URL

It will again ask for the Azure storage details ie name and Manage access key along with SQL credential.

               

Once this is connected successfully, it will list all the backup files stored there. Select the desired one with which the database needs to be restored.

             
  
After OK, it will take a couple of minutes to pull information regarding the selected backup file and after that it will be in READY state to give other options related to restore:

             

Now select the required options in Files and options tab as we do for legacy backup restore.

The same can be done using following query:

USE [master]

RESTORE DATABASE [test] FROM  URL = N'https://XXXXXX.blob.core.windows.net/xxxxxxx/test_backup_2015_09_04_104956.bak'

WITH  CREDENTIAL = N'MyCredential' ,  FILE = 1,

MOVE N'test' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\test.mdf',

MOVE N'test_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\test_log.ldf',

NOUNLOAD,  REPLACE,  STATS = 5

GO

Note: if you change the database MDF and LDF File location using MOVE then you may get below error:

Msg 3634, Level 16, State 1, Line 2

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\test.mdf'.

Msg 3156, Level 16, State 5, Line 2

File 'test' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\test.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 3634, Level 16, State 1, Line 2

The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\test_log.ldf'.

Msg 3156, Level 16, State 5, Line 2

File 'test_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\test_log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 2

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

As error is related to ACCESS DENIED, we need to give permission to the SQL Server service account on the folder where we have directed the mdf and ldf file. After this, the RESTORE command will execute fine and will RESTORE the database successfully.

44 percent processed.

88 percent processed.

100 percent processed.

Processed 288 pages for database 'test', file 'test' on file 1.

Processed 2 pages for database 'test', file 'test_log' on file 1.

RESTORE DATABASE successfully processed 290 pages in 0.355 seconds (6.379 MB/sec).

Logic Inherited from this link.

Thank for Reading. 

For SQL Server 2016 follow this link:-

https://kushagrarakesh.blogspot.com/2018/03/enable-sql-server-managed-backup-to.html