Automating MS SQL data backup to Azure using PowerShell

by CoolGiftson

It is always a better option to have our data backed up and stored in a Hybrid architecture. Storing it in public cloud like Azure gives as more automated option to manage the recovery and data integrity check process. I am going to use Azure Cool storage as backup storage unit in Azure, a low cost storage for cool object data. Ex - use cases for cool storage include backups, media content, scientific data, compliance and archival data. In general, any data that is seldom accessed is a perfect candidate for cool storage.

An Azure Blob storage, primarily constitutes three attributes, namely

1.Storage Account: The storage account is the starting point for all storage services. Here, you must create an Azure Storage account to store your data

2.Container: A Container contains a set of Azure Blobs. You must have at least one Container to store at least one Blob. A Container is similar to a folder

3. Blob: A blob is a simple file which can be a picture, a movie, or a .bak file

After creating above three, you can access the blob through URL like,
https://.blob.core.windows.net//

I created a storage account called “backupmysql” and a container called “Backup”. In that container blob all our SQL.bak files are stored.

Step 1 : Backing Up a SQL Database using Sql ServerManagement studio

For configuring backup on your Onsite SQL server, you require Microsoft SSMS assistance. Either you can use inbuilt SSMS Microsoft SQL server or you can download SSMS package from Microsoft download, now it is free..!

On Azure cloud we are choosing cool storage instead of hot storage. Since we are not accessing the .bak file very often we chooses cool storage. Cool Storage is dedicated to data that is not used frequently or archive data & Hot Storage is dedicated to production environment.

Let’s see how to create the storage account. Go to the Azure Portal and add a Storage Account, with the following information:

  • The Storage account name
  • The Deployment model: Select “Resource Manager”
  • Account kind: You must select “Blob Storage”
  • Replication: Depending your needs, you can select LRS for archive data
  • Access tier: Select the “Cool” option
  • Select your Azure subscription
  • Select a Resource Group or create a new one
  • Select the location

Once the Storage account is created, must create a container which will store the archive data.

  • On next step, Click “+ Container” to add a new Container. Type a name and select which type of access level you want.

         Private: The container and its blobs can be accessed only by the storage account owner

         Blob: Blobs within the container can be read by anonymous, but the container data can’t be read

        Container: Anonymous can read and list the entire container content

With this, Storage account is created, and Azure is ready to store our MS SQL data.

Step 2 : Backing Up a SQL Database using Sql ServerManagement studio -- Configuring SSMS

In the following steps, we are configuring OnPremise MS SQL server SSMS to backup our required database to Azure using an Azure valid account.

1. Go to your SQL Server, open the SSMS and right click on your Database -> Tasks -> Back Up…

2. Select the backup destination. Here, you must select URL to configure your Azure Account.

3. Click “New Container” to connect to Azure

4. In the new window that appears, click “Sign In”. You will be prompted to enter your Azure credentials:

  • Select your Azure Subscription
  • Select the Storage account that you previously created
  • Select the Blob container in which the backup file will be stored
  • To finish, generate a shared access signature by clicking "Create Credential”

If you want to run the backup more than once, then adjust the expiration date.

Step 3 : Backing Up a SQL Database using Sql ServerManagement studio -- Configuring SSMS continuation.

After sign-in with your Azure subscription, you will be connected to Azure storage account previously created, from MS SQL SSMS.

Once the connection has been established with your Azure account, notice the URL that has been generated by Azure with all details, as shown in the first image.

1. shows my Azure Storage Account
2. shows my Container
3. shows one of my Blobs

Once the backup is completed, you can check your Azure container and confirm that the backup file exists. You can also confirm the access tier type ( as shown in the Red box ) on Azure. Thus your MS SQL db is backup to Azure Cool storage.

Similarly, you can also easily restore the database from Azure.

  • Open SSMS, run the restore wizard, and select Device:
  • In the new window that appears, you can browse your Azure Container and select which backup file you want to use:
  • Click OK and wait until the restore finishes.

Step 4: Backing Up a SQL Database using PowerShell

Using Powershell we can automate the whole backup process above described without SSMS GUI. For powershell supporting on SSMS GUI, you need to install Powershell from Gallery.

A new console will appear with the SQL Server module already loaded. If you don’t have SSMS installed, then you can install the SQL Server module through the PowerShell Gallery:

  • PS > Install-Module -Name SqlServer
  • Wait until the module is installed.
  • Then import it with the following command:
PS > Import-Module SQLPS
  • Now, you can list all the databases that exist on your SQL Server using
Get-SQLDatabase -ServerInstance localhost
  • Confirm in the Azure Portal, that the backup file exists for confirmation.

It is very important for any database administrator to have backup for their production MSSQL server periodically. Thus following the above steps , any db administrator can achieve DR process with low cost storage. You could also use Microsoft Azure Backup server (MABS), and you require an additional server. But using this method, you can take backup directly from your MSSQL server to Azure.