SQL Azure Backup

August 16, 201134 Comments

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.

Try It

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

OR


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.

Share

34 Responses to “SQL Azure Backup”

  1. Peter Ennis says:

    Tried this:

    Zip File Path
    Backup Option
    1=Backup Script Only
    2=Backup Script and Data
    3=Backup Data Only
    4=Restore Data Only
    Login Type
    0=Do not include @ServerName with user login
    1=Include @ServerName with user login (may be needed for SQL Azure)

    C:\Downloads\SQLAzureBackup>SQLAzureBackup MyAzureDb.database.windows.net MyDbName MyDbLogin@MyAzureDb MyPassword C:\TEMP C:\TEMP 2 1

    This is a trial version of this software.
    Please purchase the full version at http://voiceoftech.com.

    Failed to connect to server .

    Done

    Not sure if the incantation is correct.
    The real login details work for SSMS, Access 2010 and also the Azure portal.

  2. @Peter – Always write your user name as “MyDbLogin” (don’t include the ‘@’) — The parameter tells the application to include the ‘@’ at runtime — I can see how that instruction might be confusing.

  3. Peter Ennis says:

    I found that using C:\TEMP C:\TEMP would create a zip file that included the contents of C:\TEMP.
    It also caused the error ‘cannot access the file “C:\TEMP\backup_2011-09-06-…zip” because it is being used by another process.

    Using empty folders C:\TEMPBAK C:\TEMPZIP with the login correction worked.

    Next question: How do I restore the db to my SQL Server 2008 R2 from these files to prove it really worked?

  4. @Peter To perform a restore, you would create a blank database and then run the {database}.sql script against it (using Query Analyzer). This will create your database objects. Then run SQLAzureBackup with option 4 — Make sure you point it at your newly created database. If all of the output files are in the same location, it will perform a BCP in from those files.

  5. Pavel Hairullin says:

    I have problem.

    SQLAZUrebackup tcp:q269dpn964.database.windows.net dbname usernamr@q269dpn964 password d:\bcf.bak d:\bcf.zip 3 0

    return error: Index was outside the bounds of the array.

    I tried different output pathes and other params, but got the same result.

    Win 7 Russian, MS framework 4

  6. @Paul – Try this:

    SQLAZUrebackup q269dpn964.database.windows.net dbname username password d:\backup d:\backupzip 3 0

    Create the folders backup and backupzip on your d: drive.

    I also removed “tcp:” from your server name and removed the “@q269dpn964″ from the username.

  7. Peter Ennis says:

    What are the licensing terms of your code if I want to adapt it for my own use?

  8. @Peter I supply the source code so that you can modify it for your own use. The license does not grant any additional rights for distribution.

  9. SEO says:

    This will be the excellent weblog for anybody who wishes to know about this subject. You recognize a lot its almost arduous to argue with you (not that I severely would want…HaHa)!!! You surely put a brand new spin on a subject thats been written about for decades. Nice things, just good!

  10. very helpful data for me.Because i am new in blogging and i am in need of excellent tutorials like your post. Good to visit here, and do not forget to visit our blog too and provides me more spirit to proceed my blogging actions

  11. scuba boats says:

    I’m at all times a foul commenter.how can you get optimistic response from me,nonetheless modern and informative.thanx buddy.

  12. Laurent says:

    Hi,
    i have tried this sentence :
    SQLAZUrebackup myserver.database.windows.net dbname username password C:\TEMP C:\TEMP 3 0
    i have also the message :ndex was outside the bounds of the array.

    PS : If i try with a wrong database name, he says correctly ‘Database … not found’, so he can open my server ?

    Thanks

    • Shannon says:

      Microsoft has made a ton of progress since I first posted this. I would suggest looking at their backup options now. You may be having an issue with the version of SQL client, but I don’t think it’s worth troubleshooting. I’ll update this post to reflect that the code is out of date.

  13. Tod says:

    I feel that is among the such a lot significant info
    for me. And i’m glad studying your article. But should remark on some normal issues, The site
    style is great, the articles is really excellent : D.

    Good task, cheers

  14. I picture this may very well be diverse upon the written content material? nonetheless I nonetheless consider that it could be suitable for almost any form of topic material, as a outcome of it should regularly be nice to find out a warmth and delightful face or maybe listen a voice whilst preliminary landing.

  15. My spouse and I stumbled over here coming from a different web site and thought I ought to check issues out. I like what I see so now i am following you. Stay up for exploring your web page repeatedly.

  16. Greetings! Very helpful advice in this particular post!
    It is the little changes that make the most important
    changes. Thanks a lot for sharing!

  17. lingerie says:

    Yours is actually a prime instance of informative writing. I feel my college students might learn a good deal from your writing fashion and your content. I might share this write-up with them.

  18. This can be my second stop by at your blog. I’m planning on starting a brand new weblog inside same category. Your website supplied me with ideas to work with. (Don’t worry, I can’t be copying you :)) You’ve got carried out an admirable job.

  19. {We thank you for this essential article. and I am on the lookout for such selective information.|Thank you for sharing this. It is so lucky to read your blog, full of knowledgable message.I think we both can do still better in the future.

  20. If I were the 1 having to jot down this content material, all these readers could be disappointed. It is a very good factor that you are the writer and you deliver contemporary concepts to us all. This is fascinating.

  21. Took me time to learn all of the comments, however I actually enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters right here! It is at all times good when you can’t only be told, but additionally entertained! I am certain you had enjoyable penning this article.

  22. Fantastic post, I believe site owners ought to acquire a whole lot from this web website its quite user pleasant.

  23. I see your point, and I completely appreciate your write-up. For what its price I’ll tell all my buddies about it, quite resourceful. Later.

  24. Heya i am for the first time here. I discovered this board and I find It really useful & it helped me out much. I am hoping to provide one thing once more and assist

  25. Thanks for a really attention-grabbing site. What else may I get that sort of information written in such an excellent manner? I’ve a enterprise that I am just now working on, and I have been on the look out for such information.

  26. e Liquid says:

    I’d all the time wish to be update on new articles on this web site , saved to favorites ! .

  27. You should indulge in a contest for one of the greatest blogs over the internet. Ill suggest this internet website!

  28. with thanks with regard towards the certain post i have been looking out with regard to this form of recommendation on the net for sum time as of late and so with thanks

  29. you may have a amazing weblog here! would you want to make some invite posts on my weblog?

  30. Certainly I like your net-website, but it’s worthwhile to test the spelling on several of your posts. A number of them are rife with spelling problems and I discover it very troublesome to inform you. On the other hand I will certainly come once more once more!

  31. superhero says:

    Do you mind if I quote a couple of your articles so lengthy as I offer credit and sources back to your blog? My web site is in the quite very same area of curiosity as yours and my customers would really profit from a great deal of the data you current here. Please let me know if this okay with you. Regards!

  32. The data is massively nice. I am recurrently a wordless reader but compelled me to draft this time. splendid post.

  33. action games says:

    Intriguing post I’ve been checking your web site out for a while and I genuinely like what you could have executed with it

Leave a Reply

Twitter Tweet This