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!

About these ads

2 Responses to Backing up AppHarbor SQL Server databases using BACPACs

  1. OK so how do I use the DLL in my project? I have added the dll in the reference section but using ‘using Microsoft..SqlServer.Dac’ results in the assembly not being found.

    • evanwieren says:

      Kindly disregard my comment. Visual Studio had not synced the new Assembly. As soon as I left this page it was working fine. Now to finish getting the DAC service working with SQL Azure, or whatever it is they are calling it today.

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: