Backup and restore SQL Azure database? Staging vs production etc.?

Question

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.

Answer 1

Try SSIS. This should work.

---

mysorian

Answer 2

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:

    bcp.exe dbname.dbo.table out  \somedir\table.tbl   -c -U your_login@your_server -S yourserver.database.windows.net  -t "|"
    bcp.exe dbname.dbo.table in    \somedir\table.tbl   -c -U your_login@your_server -S yourserver.database.windows.net  -t "|"


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) 

 

---

Tonyp

Answer 3

Hi,

If you use SQL Azure Migration Wizard, does the wizard export users and logins as well?

Thanks
Alexander

Answer 4

Thanks for the information. Please consider this to be a request for SQL Azure backup/snapshot support -- the ability to restore to a local dev environment would also be very useful.

There are serious shortcomings in the current solutions to generate scripts and BCP data, especially against a live database.
Please see http://sql-dotnet.blogspot.com/2010/02/sql-azure-pros-and-cons.html
Thank you.

Answer 5

Hi,

If you use SQL Azure Migration Wizard, does the wizard export users and logins as well?

Thanks
Alexander

Hello,
NO, it only exports:

Stored Procedures
Tables
User Defined Functions
Views

Answer 6

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.

Answer 7

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.

Answer 8

Hi,
Please also take a look at SQL Azure documentation for more information: http://msdn.microsoft.com/en-us/library/ee730904.aspx 

Thanks!
Selcin

Answer 9

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.

 

---

Pete

Answer 10

I have the same problem. Without simple and foolproof backup strategy the SQL Azure is pretty much useless in production environment. Unless you want to lose your job. I don't.

Answer 11

Several people on this thread said "consider this my vote."

Make you voice hear by voting on your backup needs on the SQL Azure Feature Voting forum at

http://mygreatsqlazureidea.com/ This is monitored by the SQL Azure folks.

 

Answer 12

Please vote for this.

http://www.mygreatwindowsazureidea.com/forums/34685-sql-azure-feature-voting/suggestions/655599-enable-backups

---

Pete

Answer 13

Have you tried SQLBulkInsert?

---

mysorian

Answer 14

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:

http://mooneyblog.mmdbsolutions.com/index.php/2011/01/11/simple-database-backups-with-sql-azure

Good Luck.

Answer 15

Any updates on this issue lately? I need to be able to backup and download to my developer environemnt, or put it as a seperate db on Azure.

---

Mcad

Answer 16

check out this one: http://blogs.msdn.com/b/davidmcg/archive/2011/09/29/data-backup-strategies-for-windows-and-sql-azure.aspx

and this one: http://sqldacexamples.codeplex.com/wikipage?title=Import%20Export%20Service%20Client

Answer 17

shall we copy the database using c# - SMO?

---

kiran svm

Answer 18

I have been using redgates free SQL Azure Backup program. Works great for backups and restore.

http://www.red-gate.com/products/dba/sql-azure-backup/

Answer 19

Almost all of the options have been discussed. Just one other Third party tool other than redgate's tool that i like and was missing in this thread is here: http://www.bluesyntax.net/backup.aspx 

(The price starts from around 50$)

---

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Answer 20

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.

Answer 21

The Windows Azure SQL Database Backup and Restore article explains the latest options available to create backups and  links to how to articles.

Thanks,

Karthika

---

Karthika [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 22

The key is to use .bakpac files.  These are used by the "Import Data-tier" and "Export Data-tier" tools in MS SQL Management Studio 2012 and by the Azure Portal's database "Import" and "Export."

For example:

1. From inside Azure portal, export database to blob storage.

2. From inside SQL Managment Studio, right click database and click "Import data-tier".

3. Enter information to get to your blob storage backup.

4. Done!