Azure Storage: creating, maintaining, and deleting SQL Server backups

6 min read

Sep 2, 2014 12:00:00 AM

This post covers how to create, monitor, maintain, and automatically delete SQL Server backups. What is Azure Storage and why should you use it? Microsoft Windows Azure is Microsoft’s cloud offering for offsite storage. It offers the ability to seamlessly enable massive storage, Virtual Servers, SQL Server database instances, and many other options without having to worry about the hardware or maintenance in house. Many companies are currently using Azure as offsite storage for their nightly Production backups. A company chooses one of 15 datacenters that Microsoft has around the world. This datacenter automatically and transparently maintains three copies of each backup file, and also replicates to a second datacenter in a different geographic location. The replication is not real-time, but in general there will always be six copies of each backup file available in case of an emergency. In the event the Primary datacenter fails, Microsoft will decide when or if to failover to the Secondary datacenter. However, in the coming months they plan to roll out an API which would allow individual clients to make that decision. SQL Server 2012 SP1 CU6+ is required. The current pricing is about $90 per month per TB of storage used. Accessing the Azure front end To access the Azure front end:

Open Internet Explorer and navigate to https://portal.azure.com.
- You will be prompted to login with a Microsoft MSDN Account.
The Azure administrator in your company should have granted this account access.
Click on the Azure Portal icon to bring up the Azure Main Page for your account.
Click on the Storage Icon on the left.
Drill down into your storage account to open the Storage Main Page.
Click on the Containers tab
Drill down into your containerThis is a list of all of the backups being written to the Production container.

The backups are ordered by their name, and unfortunately there is no way to sort by Date Modified or other field. You can see more backups than are listed on the front page by clicking on the arrow at the bottom left. Checking the status of an Azure backup file To check the status of a backup to Azure storage, you have two options: 1. The Azure Portal 2. SQL Server Management Studio To use the Azure Portal, navigate to the container the backup is being written to, and find it in the list of files. If the size is 1 TB, then the backup is currently running. Using the Azure Portal, you can only see if a backup file is being created. You cannot see how much time is remaining until it is done. To use SQL Server Management Studio, open SSMS and connect to the instance running the backups. Run the following command to get percent done & estimated time to completion:

 SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete) AS [PercentComplete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command = 'BACKUP DATABASE'

Backing up to an Azure storage container In order to write a backup file to a Microsoft Azure storage container, two items are needed:

A SQL Server credential to connect to the Azure Account
- This requires the Account Name and a Storage Key.
The container URL

To find the Azure Account Name and Storage Key, navigate to the Azure Main Page. At the bottom of the page, there is a “Manage Access Keys”. Clicking on this icon bring ups the Account Name and a Primary & Secondary Access Key. 1. Copy the Account Name and one of the Access Keys. DO NOT REGENERATE THE KEYS. 2. Copy the URL. 3. Open SQL Server Management Studio and connect to the RLPRODMSSQL01 instance. 4. From a new query window, run the following command: CREATE CREDENTIAL AzureBackups WITH IDENTITY = ‘Account Name’, SECRET = ‘Storage Key’ 5. Run this same command on all instances that will backup to this container. 6. Run the following command to backup a database to the Azure Storage container: BACKUP DATABASE db_name FROM URL = ‘Container URL + Backup File Name’ WITH CREDENTIAL = ‘AzureBackups’ Restoring from an Azure Storage Container To restore from an Azure Storage Container, two items are needed: 1. A credential to connect to the Azure Account (See steps 1-4 of Backing up to an Azure Storage Container) 2. The backup file URL To get the backup file URL, navigate to the container where the backup file is stored. The URL is to the left of the backup name. 1. Copy the URL. 2. Run the following command on the instance you want to restore the database onto: RESTORE DATABASE db_name FROM URL = ‘Backup File URL’ WITH CREDENTIAL = ‘AzureBackups’ Deleting SQL Server backups from Azure storage In SQL Server 2012, Azure storage is not fully integrated with Maintenance Plans and deleting old backups is a manual process. This causes issues, because there is no way to quickly delete a batch of backups, and if this is forgotten for a few days then the cost of storage begins to rise quickly. I have written the below code to create an executable that will connect to the Azure storage container and delete any backups older than x days. In addition, the code can check for any backups that have a “locked lease”, break the lease, and then delete them. The parameters for the executable are:

Parameter 1 – MS Azure Account Name (string)
Parameter 2 – MS Azure Storage Key (string)
Parameter 3 – Azure Container Name (string)
Parameter 4 – Number of days backups to retain (positive integer)
Parameter 5 - File type to delete (.bak, .trn, etc..)
Parameter 6 – Delete backups with locked lease? (True/False)
- Note that a True value for parameter 6 will cause the executable to ignore parameters 4 and 5.
- This is meant to be run after a failed backup job.

In order to work, the executable will need the Windows Azure Storage Client Library. Alternatively, you can download and run the executable using this ZIP file. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.WindowsAzure.Storage; using Microsoft.WindowsAzure.Storage.Auth; using Microsoft.WindowsAzure.Storage.Blob; namespace DeleteAzureBackups { class Program { static void Main(string[] args) { if (args.Length != 6) { Console.WriteLine("Please run with correct number of parameters. Type ? for help."); return; } if (args[0] == "?" || args[0] == "help" || args[0] == "h") { Console.WriteLine("=============================================================="); Console.WriteLine("Pythian Azure Backup Delete Utility"); Console.WriteLine(""); Console.WriteLine("Parameter 1 : String : MS Azure Account Name"); Console.WriteLine("Parameter 2 : String : MS Azure Account Key"); Console.WriteLine("Parameter 3 : String : Container Name"); Console.WriteLine("Parameter 4 : Positive Integer : Number of days to retain backups"); Console.WriteLine("Parameter 5 : String : File type to delete (.bak, .trn, etc...)"); Console.WriteLine("Parameter 6 : True/False : Delete backups with locked leases (will ignore Parameters 4 & 5)"); Console.WriteLine("=============================================================="); } // Account name and key. string accountName = args[0].ToLower(); //Account Name string accountKey = args[1]; //Account Key string containerName = args[2]; //Container Name int numberOfDays = Int16.Parse(args[3]); //Number of Days before deleting string fileType = args[4]; bool deleteLockedBlobs = bool.Parse(args[5]); try { CloudBlobContainer container = openConnection(accountName, accountKey, containerName); if (!deleteLockedBlobs) { deleteAzureBackups(container, numberOfDays, fileType); } else { unlockLeasedBlobs(container); } } catch (Exception ex) { Console.WriteLine(ex.Message); } return; } static CloudBlobContainer openConnection(string accountName, string accountKey, string containerName) { try { //Get a reference to the storage account, with authentication credentials StorageCredentials credentials = new StorageCredentials(accountName, accountKey); CloudStorageAccount storageAccount = new CloudStorageAccount(credentials, true); //Create a new client object. CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); // Retrieve a reference to a container. CloudBlobContainer container = blobClient.GetContainerReference(containerName); return container; } catch (StorageException ex) { Console.WriteLine("Failed to create connection to MS Azure Storage."); Console.WriteLine(ex.Message); return null; } } static void deleteAzureBackups(CloudBlobContainer container, int numberOfDays, string fileType) { DateTimeOffset now = DateTimeOffset.Now; foreach (IListBlobItem item in container.ListBlobs(null, false)) { CloudPageBlob blob = (CloudPageBlob)item; //If date blob was last modified is more than x days out, then it gets deleted. if ((now - blob.Properties.LastModified.Value).Days >= numberOfDays && blob.Name.Substring(blob.Name.Length - fileType.Length) == fileType) { deleteBlob(blob); } } } static void unlockLeasedBlobs(CloudBlobContainer container) { foreach (IListBlobItem item in container.ListBlobs(null, false)) { CloudPageBlob blob = (CloudPageBlob)item; if (blob.Properties.LeaseStatus == LeaseStatus.Locked) { try { Console.WriteLine("Breaking lease on {0} blob.", blob.Name); blob.BreakLease(new TimeSpan(), null, null, null); Console.WriteLine("Successfully broken lease on {0} blob.", blob.Name); deleteBlob(blob); } catch (StorageException ex) { Console.WriteLine("Failed to break lease on {0} blob.", blob.Name); Console.WriteLine(ex.Message); } } } } static void deleteBlob(CloudPageBlob blob) { try { Console.WriteLine("Attempting to delete {0}", blob.Name); blob.Delete(DeleteSnapshotsOption.IncludeSnapshots); Console.WriteLine("Successfully deleted {0}", blob.Name); } catch (StorageException ex) { Console.WriteLine("Failed to delete {0}.", blob.Name); Console.WriteLine(ex.Message); } } } }

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Azure Storage: creating, maintaining, and deleting SQL Server backups

Share this

Share this

More resources

Analytics with Limitless Scale on Microsoft Azure - Part 1

Cosmos DB indexing fundamentals – SQL on the edge episode 15

How to fix SSIS deployment error "please create a master key"

Ready to unlock value from your data?