Backing up AppHarbor SQL Server databases using BACPACs
October 9, 2012 2 Comments
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…
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");
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.
"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"
"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!