Download an entire SQL Azure database as single file

29
votes

Is there a facility in Azure to get a copy of the database? Or rather, detach the mdf and get it as file? On occasion I create a database in the cloud, it's up for a while, and then I want to take it down and archive it. My current rutine copies the database using SQL Azure Migration Wizard to a local Express instance, which I then detach and put in a safe place.

EDIT Interestingly my method of choice throws an exception this time around. So it's far from ideal.

7
azure-sql-database
I've read that for backup you need to open up another SQL Azure instance and copy the database from one to another, essentially copying the entire database, or the tables you need. Or you can use bcp or other import/export tools to dump table data into flat files. Backup/restore is not supported for SQL Azure. – Stephen Chung

7 Answers

50
votes

There is another way to do it:

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Export /SourceServerName:"tcp:xxxxx.database.windows.net,1433" /SourceDatabaseName:DbName /SourceUser:user /SourcePassword:password /TargetFile:C:\backups\backup.bacpac
23
votes

From SQL Server Management Studio 2014, connect to the destination database server, right click its Databases node, and click Import Data-tier Application. In the import wizard, select the option to import from Windows Azure.

6
votes

Using Sql Managment Studio (I use 2012):

  • Create a new local database.
  • Right-click in the database -> Tasks -> Import Data

Then you need to select the azure database as the data source and you new local database as data destination.

At this point, you can create a sql server backup or generate a sql script file to get a local copy at that moment.

5
votes

I created the Enzo Backup for SQL Azure utility for that very reason. You can create a full backup and get your hands on a file that you can restore later to either another SQL Azure database, or a SQL Server database.

Note that SQL Azure will offer a form of backup, cloud-only, in the future. That's another good option. Finally Red-Gate has a product to copy a SQL Azure database to a local SQL Server database, but I am not sure that it gives you a "backup file" per say.

3
votes

There is a RedGate tool that will backup your database to a local server http://www.red-gate.com/products/dba/sql-azure-backup/

I have found this useful before I do any database upgrades, in case bad stuff happens.

3
votes

Since I've asked this question, the Azure management console added an option to export the entire database to blob storage. You can keep your backups there, but if you prefer a hardcopy there are many blob explorer tools such as this one.

0
votes

A good free option I've been using for past few years is Sql Backup and Ftp. It's 100% free if you backup to your local laptop.

External storage requires paid license though.

From the website:

No more multi-step SSMS configuration, just a single form to automate backups: select databases, backup (full, diff, tran log), encrypt, compress, send to a folder, FTP or cloud service: cloudservices

schedule backups, receive confirmation emails and restore when needed.