Backing up AppHarbor SQL Server databases using BACPACs

One of the regularly requested features for AppHarbor is a way to get full backups from SQL Server databases there. The official line so far has been to use the AppHarbor-SqlServerBulkCopy tool to bulk copy the data from the hosted server to a local copy. This requires you to have an empty target database into which to squirt the data.

I’ve found a more convenient mechanism to get a simple backup of AppHarbor hosted databases using the *.BACPAC files, which it seems were created originally for getting databases in and out of Microsoft’s Azure cloud-hosting platform.

BACPAC files are Data-Tier Applications (aka DAC) packages which are essentially ZIPs that contain an XML representation of the schema:

… and the actual table data stored in JSON format:

Schema-only packages have a DACPAC file extension instead.

(Bob Beauchemin has some good info on Data-Tier Applications (DAC) on his blog)

You can backup and restore databases in SQL Server Management Studio 2012 (the Express version is fine) – look for the Import/Export “Data-tier Applications” commands on the context menu.

I think ”Data-tier Applications” are what we are supposed to call databases now…

Programmatically

If you want to do this programmatically, use Microsoft.SqlServer.Dac.dll which can be found in C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin if you have some flavour of SQL Server 2012 installed.

Exporting is simple enough:

using Microsoft.SqlServer.Dac;
…
// Export
var dacServices = new DacServices(remoteConnectionString);
dacServices.Message += (sender, e) => Console.WriteLine(e.Message);
dacServices.ExportBacpac(@"C:\temp\MyDb.bacpac", "MyDb");

And importing is similarly straightforward:

// Import
var dacServices = new DacServices(localConnectionString);
dacServices.Message += (sender, e) => Console.WriteLine(e.Message);

var package = BacPackage.Load(@"C:\temp\MyDb.bacpac");
dacServices.ImportBacpac(package, "MyDb");

Command-line

There is a command-line tool called SqlPackage which provides access to the same functionality, but it wasn’t until recently that it got the ability to export a bacpac for some reason. The SqlPackage.exe export feature was included as part of the SQL Server Data-Tier Application Framework (September 2012) update.

Download (http://go.microsoft.com/fwlink/?LinkID=266427) and install the appropriate flavour of DACFramework.msi.

Exporting:

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" ^
    /Action:Export ^
    /SourceConnectionString:"Server=foo.sqlserver.sequelizer.com; Database=dbXYZ; User ID=blah; Password=Pa55w0rd" ^
    /TargetFile:"C:\temp\MyDb.bacpac"

Importing:

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" ^
    /Action:Import ^
    /SourceFile:"C:\temp\MyDb.bacpac" ^
    /TargetConnectionString:"Server=.\sqlexpress; Database=MyDb; Integrated Security=True"

Hope that comes in useful for you!