In two mouse clicks I can easily delete permantly my production SQL Azure database. Is there a way to back it up or take a snapshot which I can restore to the same or to another SQL Azure database?
This would meet several needs: 1. Ability to backup production DB to qa/staging to test migration scripts before deploying to production 2. Ability to backup production before deploying changes in case something goes wrong 3. Ability to backup production and restore locally to development machine for testing/developing
If this is NOT possible, then how is Microsoft SQL Azure recommending that these needs be met? I am weary of data sync tools as these may be brittle and/or not get me an exact copy of the production db.
You can use the SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/) to analyzes, generates scripts, and migrate data (via BCP) from, SQL Server to SQL Azure, SQL Azure to SQL Server or SQL Azure to SQL Azure
You can also use BCP.exe to copy data out from SQL Azure or move data into SQL Azure:
If you just need to make copies of the schema you can use the SQL Server Management Studio (SSMS) 2008 R2 "Generate Scripts" option to script out a database from SQL Azure.
Later this year we will provide some new database copy commands that will allow you to create exact copies of a database within the SQL Azure system -- you will be able to use the new command for items 1 and 2 on your list. Some more details are available in this presentation: http://microsoftpdc.com/Sessions/SVC52 (fast forward 20min)
would be great to have the ability to manually copy a snapshot to Azure blob storage and then restore back from there. Would come in handy not just for recorvery scenarios but for running regression tests.
Setup data, back it up, then run test. Restore and run test again.
While SQL Azure does not currently support Backup/Restore, that does not mean that you are dead in the water. As others have suggested in this thread, SSIS and BCP are alternatives, as is using synchronization. One or even a combination of these provide an excellent alternative to backup/restore captabilities. Still to be determined is how to "backup" users and logins without doing it manually.
I cannot find a reasonable backup solution for SQL Azure. I am trying very hard to use SQL Azure for a production app but the inability to produce a reliable backup and restore plan may prevent me from using it.
Regarding the suggestions that have been made:
BCP constantly times out or has its connection "forcibly removed by the host". After this happens bcp fails to event connect for some time. BCP also cannot handle a table with large amounts of data.
SQL Azure MW chokes completely on even a moderate amount of data and cannot finish generating scripts.
Microsoft Sync Framework is not what I need. It appears that it would synchronize data between two databases, when I just need a one-way export from SQL Azure for backup purposes.
Microsoft SQL Azure team -- please help! You can't really have expected anyone to use this service without the ability to reliably export or backup data.
I read this thread several months ago, because we had the same requirement. We couldn't find a good solution in anywhere else, so we cobbled together a simple solution using Red Gate SQL Compare and SQL Data Compare, and it has worked well for us.
Here's the details:
You can use import option available in azure. This store the all sql azure db (scheema+ data) as a blob. Using this blob and export option in azure , you can create the same sql azure db with data also.