SQL Azure Backups

Has anyone come up with a good way to do backups of SQL Azure databases?

The two approaches seem to be SSIS or BCP. SSIS looks like it re开发者_开发技巧quires a local install of MS SQL 2008 which I don't have. BCP looks a bit more promising but I haven't managed to find any examples of using it with SQL Azure as of yet.

---

At the PDC09 they announced SQL Azure Data Sync, which was an early preview that is designed to let you keep your local SQL Server in sync with an Azure SQL Server.

In terms of database backups for maintenance etc, then of course that is part of the service you pay for with Azure that MS manage.

The sync framework team have a blog on a number of issues surrounding data syncronisation between Azure and a local DB - http://blogs.msdn.com/sync/default.aspx

---

My personal favorite solution is to use Azure Mobile Services to do a nightly backup & export from SQL Azure to a .bacpac file hosted in Azure Storage. This solution doesn't require a 3rd party tool, no bcp or powershell, is 100% cloud and doesn't require a local hosted SQL Server instance to download/copy/backup anything.

There are about 8 different steps, but they're all pretty easy: http://geekswithblogs.net/BenBarreth/archive/2013/04/15/how-to-create-a-nightly-backup-of-your-sql-azure.aspx

---

SQL Azure now offers automated and schedulable backups to bacpac files in Azure blob storage

http://blogs.msdn.com/b/sql-bi-sap-cloud-crm_all_in_one_place/archive/2013/07/24/sql-azure-automated-database-export.aspx

We use this to make nightly backups and have the tool keep the most recent 14 backups.

---

Enzo Backup for SQL Azure is available (full release expected October 1st): http://www.bluesyntax.net/backup.aspx

You can backup a database with transactional consistency and restore it at a later time; it stores its backups in the cloud, or on-premise. It also includes a scheduling capability.

---

I spent some time with BCP and got it working acceptably. It's a bit annoying to have to do the backup/restore table-by-table but I'll script it and that will do until Microsoft bring in a proper SQL Azure backup feature which is supposedly going to be the first half of 2010.

Backup:

bcp mydb.dbo.customers out customers.dat -n -U user@azureserver -P pass -S tcp:azureserver.database.windows.net

Restore:

bcp mydb.dbo.customers in customers.dat -n -U user@azureserver -P pass -S tcp:azureserver.database.windows.net

---

We set up a simple solution using Red Gate tools, but it too requires a local SQL instance: http://mooneyblog.mmdbsolutions.com/index.php/2011/01/11/simple-database-backups-with-sql-azure

---

I'm using www.sqlscripter.com to generate insert/update data scripts (to sync my local db). Not for free (shareware) but worth to try.

---

You can now use SQL Azure Copy to perform backups of your database. More details on this can be found here.

---

Seems that azurelabs has something to offer now:

Azure Labs

related article