Azure Storage: creating, maintaining, and deleting SQL Server backups
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.
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
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.