Download an entire SQL Azure database as single file

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.