SQL Server Portal

In my earlier articles, I explained, how to take SQL Server backup to URL using T-SQL, SQL Server Management Studio (SSMS) & PowerShell. In this article, I will explain how to RESTORE the backup from URL using T-SQL.

Pre-Requisite :

1. Microsoft Azure – How to create storage and container
2. SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – TSQL
   

Let me now demonstrate in few easy steps how to RESTORE backup from URL.

Step 1 – Create Account, storage & container :
First of all you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – Create Credential :
Once you have the details, you need to create the credential using given below script. But if you notice, this script requires two parameters namely IDENTITY & SECRET. So how will I get them? Actually IDENTITY is the storage name that we created in the Pre-Requisite article -1 (Step 4) namely ‘raresql‘ and SECRET is the primary access key that we also picked up in the Pre-Requisite article -1 (Step 5). Lets create a credential.

Note: Please skip this step if you have already created the credentials.

USE master
GO
CREATE CREDENTIAL [My_Credential]
WITH IDENTITY ='raresql'
,SECRET = '/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uY3L5nw3XkndmAnDjiKn'
GO

Step 3 – RESTORE the backup from URL :
Once you created the credential, lets RESTORE the backup on URL using given below script, which requires two parameters, one is credential that we created in the above step and the second is the URL of the backup that we created in the Pre-Requisite article -2 (Step 3).

Note: Before proceeding with the Backup restore, kindly log in to your Azure account and verify the existence of the backup which URL you will mention in the below script.

USE [master]
ALTER DATABASE [AdventureWorks2012] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [AdventureWorks2012] FROM  URL = N'https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak'
WITH  CREDENTIAL = N'My_Credential'
,  FILE = 1,  NOUNLOAD,  STATS = 5
ALTER DATABASE [AdventureWorks2012] SET MULTI_USER
GO
--OUTPUT

As you can see, the messages show that your backup has been restored successfully.