Azure SQL Database Bacpac Local Restore

Question

I've created a BACPAC backup of my Azure SQL Database using the "Export" option within the Azure Management Console.

Having downloaded this to my machine I'm a little stuck on how I can restore this to a local SQL Server instance. I came across the DacImportExportCli tool but couldn't find an example of a local restore.

Also if anyone has written a script that does this (so it can be scheduled) that would be awesome.

Alejandroalejo

This may be a SQL Server 2012 thing, but in the SQL Server Management Studio if I right-click my local server's database folder and choose Import Data Tier Application that starts a wizard which reads in the BACPAC file to produce the copy of my Azure database. The wizard can also connect directly to the blob-store to obtain the BACPAC file if you would rather not copy it locally first. – Macnair

Did you ever get this figured out? – Swarm

@Macnair I believe it is a SQL 2012 SSMS thing to import the Data-tier application, 2008R2 only seems to have ability to export DACPACs, can't see an import option. – Arraign

Answer 1

This can be done simply through SQL Server Management Studio 2012

1. Right click on the Connection > Databases node and select "Import Data-tier application..."
2. Select "Next" on the introduction step.
4. Browse, or connect to a storage account where backups are kept.

Charkha

This is perfect. The RedGate tool can be flakey. I'm glad to see a MS supported option. – Litigation

Does it work with sql express 2012 ? Because I have the following error message : Count not load schema model from package. (Microsoft.SqlServer.Dac) ------------------------------ ADDITIONAL INFORMATION: Internal Error. The internal target platform type SqlAzureDatabaseSchemaProvider does not support schema file version '2.5'. (File: C:\Users\xxxxx\Downloads\dbname-2013-10-10-20-2.bacpac) (Microsoft.Data.Tools.Schema.Sql) – Sag

@ElTone I just restored a bacpac directly from Azure Storage to my local SQL Server Express 2012 database using the Management Studio. Did not see any errors and the data seems to be there. Somebody mentions that installing newer version of SQL Server Data Tools helped them (but they are not referring to Management Studio) social.msdn.microsoft.com/Forums/windowsazure/en-US/… – Bearer

@Juha Palomäki Correct : Installing latest version of SSDT solved the issue. All is better now. – Sag

There seems to be some limitation on how large bacpac files you can import with the Management Studio. If you hit OutOfMemory exception, check Flea's answer on how to use the command line tool SqlPackage.exe – Bearer

"Import Data-tier application", how didn't I think of it before! (super intuitive!) – Uranous

If you've noticed that a lot of your RAM have been occupied, just restart the SQL Server service after importing! – Anesthetist

Note: IF you have upgraded your SQL Azure to V12 then you need the latest SQL Server pack/CU for SQL 2014 to be able to import it locally – Antemortem

Answer 2

Try "SqlPackage.exe"

I needed to export a SQL Azure database and then import it into a local SQL 2008 R2 server (Note I am also using Visual Studio 2010). Microsoft certainly went out of their way to make this a painful task, however, I was able to do it by doing the following:

1. Go to this link http://msdn.microsoft.com/en-us/jj650014 and install the SQL Server Data Tools for Visual Studio 2010

2. This will install on your local drive. In my case here is where it put it: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

3. Browse to this via the command line or powershell

4. You are going to want to execute the SqlPackage.exe

5. Open up this link to see a list of all the parameter options for SqlPackage.exe (http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx)

6. Here is my command line that I needed to execute to import a .bacpac file into my local SQL 2008 R2 server:

   .\SqlPackage.exe /a:Import /sf:C:\mydatabasefile.bacpac /tdn:NorthWind /tsn:BINGBONG
   

• /tdn is the name of the database you want your bacpac file to restore to.
• /tsn is the name of your SQL server.

You can see all these parameter descriptions on the link from #5.

Compute

This can be accomplished even more easily if you have SSMS 2012 installed. You can simply use the wizard as described by @Josiah above, even with a 2008 R2 server. – Annadiana

I spent so much time searching for this solution that I managed to download the whole managment studio 2014 meanwhile. – Waly

SqlPackage was hanging for me on the 'updating database' step until I dropped the target database completely – Jonell

Answer 3

You can restore the BACPAC by using the client side tools. Videos are here:

http://dacguy.wordpress.com/2011/09/09/importexport-services/

The tools are available here:

http://sqldacexamples.codeplex.com/documentation

Ligni

The tool is supposed to support importing the bacpac into the local database. For example, importing a bacpac to a database using Windows Auth would look like this: "DacCli -S myserver -E -D nw_restored -F northwind.bacpac -I". However, the codeplex list of required assemblies is out of date - none of the links would work, so the tool won't run (it has a prerequisite on some SQL 2012 CTP components). – Prosthesis

I have had all the components installed on my machine so I tried importing a bacpac with DacCli - it worked (see the command-line in my previous comment). Took about 20 minutes for 100MB bacpac. – Prosthesis

Answer 4

Seems my prayers were answered. Redgate launched their SQL Azure Backup tool for FREE today - http://www.red-gate.com/products/dba/sql-azure-backup/download

Alejandroalejo

Several issues: 1. Active development of SQL Azure Backup is stopped for now (this note is displayed when you run the latest version of the tool) 2. When backing up directly from Azure to local SQL, it does not seem to pass by the bacpac but instead implements some sort of a home-grown schema/data copy. In my experience, it's been very slow. 3. To build upon the previous point, the mechanism is buggy - I've had multiple tables failing to transfer because the tool could not detect a unique key – Prosthesis

This answer should be updated, or no longer be the accepted answer. The linked tool has since become a "cloud" solution and does not support local backups. – Hymnody

The backup tool is still available from red-gate.com/products/dba/sql-azure-backup/Discontinued – Alejandroalejo

Answer 5

If you're using SSMS 2012, it is as easy as right-clicking on the Databases folder under a server in the Object Explorer and choosing "Import Data-tier Application...".

There is one bump in the road to watch out for: as of Mar 26 2013 (when I needed to find out how to do this myself), when you export a .bacpac from Azure, it will be downloaded as a .zip file, not a .bacpac file, and the file dialog that is opened by the Browse button in the import wizard will only show either *.bacpac or *.* in the file filters, implying that .zip is not supported. However, if you change the filter to *.*, select your downloaded .zip, and click Next, the wizard will proceed normally.

Flatter

This is the correct answer. You can now install the free standalone version of SSMS 2016 and it provides all these features – Charleencharlemagne

Answer 6

Here's a script to restore a bunch of bacpac files at once: Bulk Restore bacpac files local

cd [FOLDERPATH]
$goodlist = dir
cd 'C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin'
foreach($i in $goodlist){
    $name = $i.Name;
    $namer = $i.Name.Substring(0, $i.Name.length - 7);
    .\SqlPackage.exe /a:Import /sf:[FOLDERPATH]\$name /tdn:$namer /tsn:[SERVERNAME]
}

Improbity