A SQL Azure tip a day (9) – Backup database

In SQL Azure you have 3 copies of your databases in 3 different geographical locations in case of harware failure etc. But how do you handle human failures, like deleting or updating wrong records? The best solution is to make sure it never happen, but if it can happen, it will happen. First of all, make sure you always do everything in a transaction, verify your result and commit or rollback depending of your verification, but that’s what you should do in any database, not just SQL Azure. But not all human errors are found at once, and then you may need to have a recent backup to fallback to, but in SQL Azure you don’t have the backup command. Instead you have some other options like creating a database copy, exporting the data or use SQL Azure Data Sync to make sure you have a recent copy of your data.

Creating a database copy and exporting data is a manual process and SQL Azure Data Sync can be an automated process, like once an hour, but if you find the error after the synchronization, the error will also be synchronized. A database copy is easy to create, but keep in mind that you have to pay for the second database as well. CREATE DATABASE [MySQL database copy] AS COPY OF [MySQL database]; You can monitor the copy process by querying the DMV sys.dm_database_copies, like this SELECT * FROM sys.dm_database_copies; There are also some third party tools like Red-Gate SQL Azure Backup and it is free of charge and it is using the Microsoft’s CTP Import/Export Service to create BACPAC files in Windows Azure blob storage.  The point is, make sure you have a backup and restore strategy for your SQL Azure databases also, not only your local databases. I have seen too many bad disaster recovery plans failing when they are really needed, or bad High Avalibilty (HA) solutions for that matter, causing a lots of bad reputation, loss of money and clients. 

At least the HA is solved in SQL Azure and is one of Microsoft responsibilities.

“The customer ultimately is responsible for the schema and data on SQL Azure, however Microsoft via the current SQL Azure service level agreement has assumed some of the responsibilities of keeping the data highly available. For restores from force majeure and customer errors the customer is responsible for the restoration and taking backups that match their business model.”  quoute from http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-backup-and-restore-strategy.aspx

If you need some help with disaster and recovery planning, feel free to contact any of our SQL server consultants and we will help you.