Backing Up and Restoring a SQL Database to Microsoft Azure

There are many ways of backing up your SQL databases to the cloud. Most programs capture the SQL BAK files made within a SQL Maintenance plan and move them to the cloud storage. This example will show how you can quickly and easily backup and restore to MS Azure cloud services. 

Please note you will need to be on at least SQL 2012 SP1 CU2. You can get this from MS KB 2790947 or simply download from Windows Update. It will also assume you already have a MS Azure account and login.

Once logged into Azure you are at the management portal where you can navigate all your services. 

You will want to select "Storage" from the left. Once that is selected choose "New" at the bottom left corner. 

Click the Quick Create option to continue. 

Here you enter in the URL you wish to use, and also where you want your database stored. Keep in mind if this is for general backup or disaster recovery it may be best to store in a different geographical region then where the files reside. .

Select the Check Mark at the bottom to continue. 

Now in our the storage account names "companyname" is being created. 

When it completes and is ready to go you will see several tabs across the top of the screen. Select the Containers Screen to continue. 

 Click Create a Container

Enter in the container name. In this case we will call it backups. This will be a private container. Click the Check to continue the creation process. 

When its complete you will see the backups container. 

Click back on the storage name "companyname" to return to the storage level view. 

At the bottom of the screen you will see "Manage Access Keys" 

Each of your storage devices has its own set of keys. 

For this example click the copy icon to copy the key, you will need it later in SQL. 

At this point we are done with Azure and can open up our SQL environment. Login to your database using the SQL Managment Studio.

Drop down to the database you want to backup and right click it. Choose "New Query" from the list and a new window appears on the right of the screen. 

Here we will enter in the information to make the Azure Connection. Remember you need at least 2012 R2 CU2. If you don't have this the connection to Azure will fail as SQL cannot connect to a URL in previous versions. 

Enter your code like this

CREATE CREDENTIAL myAzureCredential
WITH IDENTITY='companyname', 
SECRET='paste key from above here';

Once done press the execute button to run the script.

Now that the connection has been made we can backup our first database.












BACKUP DATABASE dbname TO
URL='https://companyname.blob.core.windows.net/containername/dbname.bak
WITH FORMAT, CREDENTIAL='myAzureCredential', STATS = 5;

Here companyname is the name of the storage account, containername is the name of the container and dbname is the name of the database file you would like to create. In this example FORMAT means to overwirte the file sitting in Azure if it exists. 

For those of you not familiar with the STATS option, it displays a message after each percentage is is completed, percentage which is specified as the stats value, in the case of the query above, it’s 5. So each time 5% of the job is complete you will see a line of text in the bottom result pane.

When you press execute this will run the command and you will see a progress and the final results of the backup. 

The restore process is very similar. Click the database and choose new Query. 

The code to do a simple restore of the database would be like below but this would only work if the database doesn't already exist. 











RESTORE DATABASE dbname_test FROM
URL='URL='https://companyname.blob.core.windows.net/containername/dbname.bak'
WITH CREDENTIAL='myAzureCredential' , STATS =5;

Your code to restore the Azure backup as a new DB would be possible by adding a few lines of code to your script like below.










RESTORE DATABASE dbname_test FROM
URL='https://companyname.blob.core.windows.net/containername/dbname.bak'
WITH MOVE 'dbname' TO  'C:\new location\dbname_test.MDF', 
MOVE 'dbname_log' TO 'C:\new location\dbname_test.LDF'
CREDENTIAL='myAzureCredential' , STATS =5;

And when complete the results will show and you will have a new DB in your DB List on the left.