backup SQL Azure and restore in SQL Server

Question

Hi,

I understand that there's no out-of-the-box / straight-forward way to perform backup and restore in SQL Azure.

I also heard that we can use BCP to backup SQL Azure's table.

But how do we restore the BCP to on-premise SQL Server in case our SQL Azure is unavailable / we are not using it anymore?

Answer 1

Hi Daniell.Chong,

You can use the SQL Azure Migration Wizard to generate scripts and migrate data from SQL Azure to a on–premise SQL Server.

For more information, please see: http://sqlazuremw.codeplex.com/

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

Hi,

But SQL Azure MW could not able to perform schedule backup. Any idea?

Answer 3

Hello,

 

Would this do the trick?

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

 

 

Answer 4

I work for Red Gate Software and we've just produced a free tool that may do what you want.

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

If not you could always use SQL Compare and SQL Data Compare in a scheduled task.

Either way always interesting in your thoughts.

 

Answer 5

An interesting article on SQL Azure backup:
http://www.cumulux.com/2011/04/28/sql-azure-backup-options/

---

Paras Doshi, I blog about SQLAzure @ http://www.parasdoshi.com

Answer 6

Hi Daniell,

You can use DAC CLI Tool to Backup SQL Azure database objects and data and then restore it in On-Premise SQL Server and vice versa.

Please read this article for step by step instructions

http://sqlxpertise.com/2011/05/11/dacimportexportcli-import-export-data-from-sql-azure-to-sql-server-vice-versa/

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com

Answer 7

how about "automatic" / "scheduled" backup?

Answer 8

how about "automatic" / "scheduled" backup?

i replied on another thread started by you: http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/6b40d9b9-5d94-4272-8071-bee42e5f7822

---

Paras Doshi, I blog about SQLAzure @ http://www.parasdoshi.com

Answer 9

Hi Daniell,

You can Create a Batch file using DAC CLI Tool and setup that as Scheduled Job to run locally

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, let us know by clicking "Vote as Helpful".

Answer 10

You can use Import of teh database  functionality to take the back up. This will store the DB scheema+ data as a blob. Using this blob and export wizard you can restore the another DB with same scheema and data.

Answer 11

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 12

have you considered SQL Data Sync http://msdn.microsoft.com/en-us/library/windowsazure/hh456371.aspx?

Answer 13

You should better write a procedure that Creates backups and Store them in BLOB STORAGE.
To schedule it you should do by writing module that fires the procedure in pre-described schedule.

It worked for me once.

Backup Query is here: http://msdn.microsoft.com/en-us/library/ms186865.aspx

---

Mahesh Dahal

Answer 14

There are several other options to programmatically export our database from Azure using Data-Tier Application Framework (DACFx) technology. First, create a copy of your database then export your database.  For a larger database, option 1 will provide support for retries, we are improving retry logic for options 2 & 3.

1) Write a program to call the REST endpoints of the Windows Azure SQL Database Import Export Service - http://code.msdn.microsoft.com/windowsazure/Windows-Azure-SQL-Database-5eb17fe2#content.  The daciesvcli application essentially does the same thing but writing your own application will give you greater flexibility.

2) Use the sqlpackage.exe application - details here - http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

3) Use the DACFx api - details here - http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.aspx

---

Shamitha Reddy (Microsoft)

Answer 15

Hi Shamitha,

We've been waiting for a while for the details on REST endpoints of the Windows Azure SQL Database Import Export Service. Do we know when are these details coming?

Answer 16

Code samples for using the Import/Export REST API - http://code.msdn.microsoft.com/windowsazure/Windows-Azure-SQL-Database-5eb17fe2#content

---

Shamitha Reddy (Microsoft)

Answer 17

SQL Data Sync is not recommended for backing up/restoring databases.  However, it does work for keeping database data in sync.

See the article at http://msdn.microsoft.com/en-us/library/hh667301.aspx#bkmk_datasyncbackupandrestore for details.

---

_RussH