How can I save my Azure sql database as an mdf file?

Question

I am using Azure mobile services and I want to export my database to an MDF file so I can use it in (test) applications that expect an MDF.

How can I do it?

Answer 1

Another way you can do this with UI

1. Export your Database as a .bacpac file.

2. Download that bacpac from the Storage Account you exported it to.

3. Import the bacpac into your local machine through SSMS.

4. Access to the MDF File by Right-Click on the Database and under Database Properties, you will see the location of the MDF File

let me know if that helps, Vin

Answer 2

It seems it is not supported out of box. However you can open the Azure SQL database in SQL Server Management Studio and generate Create Script that you can use to create the database in On-Prem server and if you need to copy the data as well then you can use bcp utility.

http://blogs.msdn.com/b/cesardelatorre/archive/2010/06/04/importing-exporting-data-to-sql-azure-databases-using-bcp-and-sql-scripts.aspx

Steps to create MDF file.

1. Open SQL Server Management Studio and connect to both Azure server and local SQL server.
2. Select the Azure database for which you want to create MDF, right click and select Script Database as > Create To > New Query Window, which will display the create script in query window. Copy it.
3. Then select databases under local server and hit New Query button in toolbar, paste the script copied in previous step. Execute the script, which will create the database locally.
4. To find the MDF file, right click newly created database and select Properties. In the dialog box select Files pages and in the properties grid scroll to right and you will see the Path and Name of both MDF and LDF files.