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.
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
identity(2,2), FName nvarchar(100), LName nvarchar(100))
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:
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:
URL = N
'/<backup file name>'
, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
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
and now we can backup the database
,STATS = 5;
Read Backup headers
Restore from URL
'https://[Storage name].blob.core.windows.net/[container name]/BackupFileName.bak'
'C:\Program Files\Microsoft SQL Server\\MSSQL\DATA\TestingBackup2Azure.mdf'
'C:\Program Files\Microsoft SQL Server\\MSSQL\DATA\TestingBackup2Azure.ldf'
,STATS = 5;
RESTORE LOG [TestingBackup2Azure]
'https://[Storage name].blob.core.windows.net/[container name]/LogBackupFileName.trn'
RECOVERY, STOPAT =
Official documentation - highly recommended!
SQL Server backup to URL
Connect SQL Server on-premises to a Microsoft Azure Subscription