Azure SQL Database Bacpac Local Restore
AzureAzure Sql-DatabaseBacpacAzure Problem Overview
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.
Azure Solutions
Solution 1 - Azure
This can be done simply through SQL Server Management Studio 2012
- Right click on the Connection > Databases node and select "Import Data-tier application..."
- Select "Next" on the introduction step.
- Browse, or connect to a storage account where backups are kept.
Solution 2 - Azure
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:
-
Goto this link http://msdn.microsoft.com/en-us/jj650014 and install the SQL Server Data Tools for Visual Studio 2010
-
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
-
Browse to this via the command line or powershell
-
You are going to want to execute the SqlPackage.exe
-
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)
-
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.
Solution 3 - Azure
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:
Solution 4 - Azure
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
Solution 5 - Azure
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.
Solution 6 - Azure
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] }