פבר1
Written by:
ronen ariely
01/02/2022 11:41 
In the last several weeks I see many questions regarding Microsoft released of the new support to manage backups of SQL Server on premises in the Azure storage. This post is not intended to replace the official documentation, but only to simplify operations in a practical way. This post present step by step tutorial on backup to URL, backup to Azure Storage, and restore back to on-premises.
Preparation
Create Azure Storage and Container
(1) For the sake of the demo you need to create a storage account in the Azure
(2) Create a container
Create New Database and table
CREATE DATABASE TestingBackup2Azure
GO
USE TestingBackup2Azure
GO
CREATE TABLE dbo.t01 (id INT identity(2,2), FName nvarchar(100), LName nvarchar(100))
GO
TRUNCATE TABLE T01
GO
INSERT t01 (FName,LName) values ('A','1'),('B','2')
GO
SELECT * FROM dbo.t01
GO
USE master
GO
Backup to URL using SSMS
In this demo I am using Authentication method for the Azure storage: Access key. For this authentication we can use (1) Azure storage account name and its access key value or (2) A Shared Access Signature token generated on specific container.
(1) In the Object explorer windows, right-click the desired database, point to Tasks, and then click Back Up
(2) In the General tab select URL for the destination -> Click Add
(3) Click on "New container"
Note! This will not create a new container but new connection to existing container
(4) If you did not created a credential to connect the Azure subscription in the database then Click on "sign in" -> fill the connection form to sign in your Azure subscription -> click on create Credential -> click OK
Behind the scenes this generate a stored access policy and shared access signature and then create a SQL Server credential. A credential is a record that contains the authentication information required to connect to a resource outside SQL Server. For example credentials can contain a Windows user name and password.
You can find the new credential by executing:
SELECT * FROM sys.credentials
GO
Default name should be something like: https://[storage].blob.core.windows.net/[container]
(5) select a file name and click OK
(6) In the backup windows select "Script Action to New Query Windows" -> examine the script and execute the backup.
Scrip should look like:
BACKUP DATABASE [TestingBackup2Azure]
TO URL = N'/<backup file name>' WITH NOFORMAT, NOINIT, NAME = N'', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Backup to URL using transact SQL
(1) Create credential
You can use a shared credential or a storage key for the task. If the storage key is used in the credential, then a page blob will be used; if the Shared Access Signature is used, then block blob will be used.
Note! Microsoft recommend to use block blob, which means to use Shared Access Signature (Update post: the differences are well documented here).
In order to create a shared credential you will need to generate a SAS_TOKEN.
For next demo we'll the storage key
CREATE CREDENTIAL [MyAzureStorageCredentialName] WITH IDENTITY = '[storage name]',SECRET = '[SAS_TOKEN]';
and now we can backup the database
BACKUP DATABASE [TestingBackup2Azure]
WITH CREDENTIAL = 'MyAzureStorageCredentialName'
,COMPRESSION
,STATS = 5;
GO
Read Backup headers
RESTORE HEADERONLY
WITH CREDENTIAL = 'MyAzureStorageCredentialName'
GO
Restore from URL
RESTORE DATABASE [TestingBackup2Azure]
FROM URL = 'https://[Storage name].blob.core.windows.net/[container name]/BackupFileName.bak' WITH MOVE 'NewFileName_data' to 'C:\Program Files\Microsoft SQL Server\\MSSQL\DATA\TestingBackup2Azure.mdf'
,MOVE 'NewFileName_lopg' to 'C:\Program Files\Microsoft SQL Server\\MSSQL\DATA\TestingBackup2Azure.ldf'
,NORECOVERY
,REPLACE
,STATS = 5;
GO
RESTORE LOG [TestingBackup2Azure]
FROM URL = 'https://[Storage name].blob.core.windows.net/[container name]/LogBackupFileName.trn' WITH RECOVERY, STOPAT = '2022-12-12 18:00'
GO
Official documentation - highly recommended!
SQL Server backup to URL
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699
Connect SQL Server on-premises to a Microsoft Azure Subscription
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/connect-to-a-microsoft-azure-subscription?view=sql-server-ver15