en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read this before you use the blog! Maximize

Recent Entries

Minimize
פבר1

Written by: ronen ariely
01/02/2022 11:41 RssIcon

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