AcloudA

[Azure] Backup your SQL Server databases

Posted by Florent Appointaire on July 26, 2017
No ratings yet.

If you want to backup your databases on SQL Server, but you don’t have storage anymore, or you don’t want to buy new hardware or you want to back up your LAB, you can save them on Azure. To do this, it’s very simple, and you pay only the storage that you use.

Microsoft SQL Server Logo

Prerequisites

Start by creating a storage account with the BLOB type:

Create storage account window - BLOB storage

Create a container on this storage account:

Storage account - Overview - Essentials

Backup

To back up your database, right click on it > Tasks > Back Up…:

Microsoft SQL Server Management Studio - Object Explorer - Task - Back Up

Change the destination to URL and click on Add:

Back Up Database - Microsoft.MgmtSvc.Store - URL - Add

In the new window that appears, select New Container to connect to your Azure account:

Select Backup Destination - New container

Connect to your Azure account by clicking on Sign In:

Connect to a Microsoft Subscription - Sign In...

When you’re connected, select the Azure subscription that contains the storage account created at the beginning, the storage account and the container, and, generate a new key with an expiration date by clicking on Create Credential:

Connect to a Microsoft Subscription

You have the connection who is established with your Azure storage account:

Select Backup Destination - Azure storage container

Choose the type of backup (FULL) and the URL will be generated for your backup:

Back Up Database - VirtualManagerDB - Backup type

Click on OK to start the backup:

Back Up Database - VirtualManagerDB - Connection Progress

Depending on the size of your database, after some time, the backup is done:

Back Up Database - VirtualManagerDB - Microsoft SQL Server Management Studio - Completed Succesfully

I’ll now create a new empty database, with a table:

New Empty DataBase creating - Object Explorer

Backup this database with the previous step-by-step and delete it:

Solution1 - Microsoft SQL Server Management Studio - Object Explorer - VirtualManagerDB

You can automate the backup with the following script: https://github.com/Flodu31/PowerShell-Scripts/blob/master/SQLServer/Backup_DB_To_Azure_v1.0.ps1

Restore

We will now restore the database. Right click on Databases > Restore Database…:

Solution1 - Microsoft SQL Server Management Studio - Object Explorer - Databases - Restore Database

Choose the source Device and click on the block with … :

Restore Database - Select a page - Device

Choose the media type URL and click on Add:

Select backup devices - URL - Add

Choose the storage account who is already registered and click on Add:

Select a Backup File Location

Choose the Azure subscription, the storage account and the container where the database has been saved. Create a new key:

Connect to a Microsoft Subscription - Create Credential

You are now connected to the storage account that contains the backup of your database:

Select a Backup File Location

Choose the backup that you want to restore and click OK:

Locate Backup File in Microsoft Azure

URL appears with the backup file name. Click on OK:

Select backup devices

Choose the name of the database that you want to restore and click OK:

Restore Database - TestDB - Select a page - Connection - Progress

After few moments, depending on the size of the database, your database has been restored correctly:

Restore Database - TestDB - Restored successfully

If you are doing a select again, data are again present:

All data present - Object Explorer

This solution has for advantage to be quick, flexible and the cost is very low because you’re paying only the storage cost, without any delay that you could have with the hardware. You don’t have the cost to maintain an infrastructure, pay someone to manage it, etc.

 

Related materials:

Views All Time
1
Views Today
7
Return to all posts

Microsoft Released in Public Preview a Customized Azure Service Health Dashboard
Walmart is Asking Providers to Avoid Storing Information in AWS, but is that a Right Decision?

Please rate this

The following two tabs change content below.
Florent Appointaire
Florent Appointaire is Microsoft Engineer with 5 years of experience, specialized in Cloud Technologies (Public/Hybrid/Private). He is a freelance consultant in Belgium from the beginning of 2017. He is MVP Cloud and Datacentre Management. He is MCSE Private Cloud and Hyper-V certified. His favorite products are SCVMM, SCOM, Windows Azure pack/Azure Stack and Microsoft Azure.

Latest posts by Florent Appointaire (see all)

One Response to “[Azure] Backup your SQL Server databases”

  1. … [Trackback]

    […] Find More on|Find More|Find More Infos here|Here you will find 21426 more Infos|Informations to that Topic: aclouda.com/blog/ms/azure-backup-your-sql-server-databases/ […]

Leave a Reply

You must be logged in to post a comment.