none
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.
    Friday, February 12, 2010 8:15 PM

Answers


  • 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
    • Marked as answer by Yi-Lun Luo Monday, February 22, 2010 8:54 AM
    Tuesday, February 16, 2010 7:36 AM

All replies

  • Try SSIS. This should work.
    mysorian
    Friday, February 12, 2010 11:49 PM

  • 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
    • Marked as answer by Yi-Lun Luo Monday, February 22, 2010 8:54 AM
    Tuesday, February 16, 2010 7:36 AM
  • Hi,

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

    Thanks
    Alexander
    Tuesday, February 16, 2010 9:07 AM
  • 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.
    Saturday, February 27, 2010 7:00 PM
  • 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

    Saturday, February 27, 2010 8:00 PM
  • 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.
    Thursday, March 04, 2010 3:38 PM
    Moderator
  • 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.
    Thursday, March 04, 2010 8:57 PM
    Moderator
  • Hi,
    Please also take a look at SQL Azure documentation for more information: http://msdn.microsoft.com/en-us/library/ee730904.aspx 

    Thanks!
    Selcin
    Thursday, March 04, 2010 9:07 PM
  • 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
    Thursday, April 15, 2010 2:55 PM
  • 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.
    Thursday, April 15, 2010 4:28 PM
  • 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.

     

    Thursday, April 15, 2010 6:47 PM
  • Have you tried SQLBulkInsert?
    mysorian
    Friday, August 27, 2010 1:10 PM
  • 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.

    Tuesday, January 11, 2011 10:07 PM
  • 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
    Tuesday, October 04, 2011 11:03 AM
  • shall we copy the database using c# - SMO?
    kiran svm
    Thursday, November 24, 2011 12:42 PM
  • 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/
    • Edited by BJAAS Thursday, December 15, 2011 8:47 PM
    Thursday, December 15, 2011 8:46 PM
  • 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 )
    Friday, December 16, 2011 2:49 AM
    Moderator
  • 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.
    Saturday, July 28, 2012 9:15 AM
  • 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.


    Thursday, September 20, 2012 4:15 PM
  • 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!
    Wednesday, May 15, 2013 11:29 AM