SQL Azure Backup
Update: Since this post was published, Microsoft has delivered several backup solutions for Azure. I would suggest giving them a try.
I love SQL Azure, but there is still a huge piece missing from an otherwise very complete service; yes, I’m talking about backups. In many of the Microsoft articles on this subject, they seem to imply that the SQL Azure system is so robust that data loss isn’t a large concern. Well, Microsoft is probably right if you’re worried about hardware or software failures, but in my experience, human failures are the most common cause of data loss, and the lack of a complete backup system is really troubling.
Microsoft suggests that you either use a database copy as your backup or utilize their data replication process. The database copy can be expensive and doesn’t allow for multiple archives. The replication service is a better option, but it means that I’ll need to maintain a large database on my servers to constantly pull the data.
There are some third-party tools that will backup your SQL Azure databases, but the costs for those systems are outside of my budget.
I decided that the best approach for me was to write a simple command-line application of my own. I created SQLAzureBackup using standard .NET tools, and I’m currently using it with my greater-than-10GB database. SQLAzureBackup can be scripted and run on a schedule.
How It Works
As I said, it’s very simple, the program generates a script for the entire database. It then runs the BCP process for every table. At the end, all of the BCP files and the script are wrapped up in a zip file. The name of the zip file includes the current date.
To restore the database, you create the database, run the script, and then rerun SQLAzureBackup. It will reload all of your data using the BCP files.
A 30-day trial version of SQLAzureBackup is available for download. You can also purchase the full version with source code for $39.99.
Download the Trial
Buy the full version:
There are some limitations — permissions and security aren’t currently scripted — I haven’t needed those features yet, but it may be necessary for someone else in the future. I expect that some modifications will be made when bugs are discovered and I will make updates available.
Give the software a try and let me know what you think. If you’ve been considering your own SQL Azure backup solution, this app will save you a lot of time.
data-text=”SQL Azure Backup (Shannon Whitley)”