Copy SQL Server 2012 database to SQL Azure through BACPAC (for continuous integration)

Question

I am creating a continuous integration MSBuild script that copies a database on a local SQL Server 2012 to SQL Azure.

Is it easy?

Methods

After a little research, I came across the following methods:

• Use PowerShell to directly access the DAC library , then use the MSBuild PowerShell Extension to port the script . To do this, you will need to install PowerShell 3 and develop how to make the MSBuild PowerShell extension with it, since obviously MS has moved the DAC API to another namespace in the latest version of the library. PowerShell will provide direct access to the API, but may require quite a few templates.

• Use the sample client tools of the DAC Framework , which require their compilation on their own, since the downloads available from Codeplex include only the Hosting version . You would also need to fix their use of the DAC 3.0 classes, since they appear to be using an earlier version of the DAC. Then I could call these tools from the <Exec Command="" /> in the MSBuild script. Less template, and if I hit all the bumps on the road, I can just make changes to the source.

The processes

Using any method, the process can be:

• Export from local SQL Server 2012 to local BACPAC
• Download BACPAC to the blog repository
• Import BACPAC to SQL Azure through Hosted DAC

Or:

• Export from local SQL Server 2012 to local BACPAC
• Import BACPAC into SQL Azure using Client DAC

Question

All of the above seems to require a lot of effort for what seems like a standard feature ... so before I start reinventing the wheel and documenting the results so everyone can see if there is anything really obvious that I missed? a pre-written script that MS released that I haven’t discovered yet?

There is a command in the GUI of SQL Server Management Studio 2012 that does EXACTLY what I'm trying to do (right-click on the local database, select "Tasks", click "Deploy Database to Azure SQL"). Of course, if it's a few clicks in the GUI, should it be a single command on the command line somewhere?

Answer 1

So I decided to use PowerShell (since this is really more of a script), with Client DAC . The example here was very useful.

Using the MSBuild Extension Pack allows you to create tasks for packaging a PowerShell template.

Note that you must install PowerShell 3 to access DAC 3.0, which is best done by installing Windows Management Framework 3.0.

TaskFactory Wrappers:

 <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <!-- This custom task encapsulates DAC Framework 3.0 BACPAC import and export routines, which are compatible with SQL Server 2012 and later, and SQL Azure --> <!-- Required Import to use MSBuild Extension Pack --> <PropertyGroup> <AssemblyFile>$(MSBuildExtensionsPath)\ExtensionPack\4.0\MSBuild.ExtensionPack.TaskFactory.PowerShell.dll</AssemblyFile> <SqlServerDacDll>C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll</SqlServerDacDll> </PropertyGroup> <UsingTask TaskFactory="PowershellTaskFactory" TaskName="ExportBacpac" AssemblyFile="$(AssemblyFile)"> <ParameterGroup> <ConnectionString Required="true" ParameterType="System.String" /> <BacpacFile Required="true" ParameterType="System.String" /> <DatabaseName Required="true" ParameterType="System.String" /> </ParameterGroup> <Task> <![CDATA[ #write progress to activity log $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Starting export of database '$databasename' to '$bacpacfile' with connection string '$connectionstring' ") # load in DAC DLL (requires config file to support .NET 4.0) # change file location for a 32-bit OS add-type -path "$(SqlServerDacDll)" # make DacServices object, needs a connection string $d = new-object Microsoft.SqlServer.Dac.DacServices $connectionstring # register events, if you want 'em register-objectevent -in $d -eventname Message -source "msg" -action { $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High", $Event.SourceArgs[1].Message.Message) } # Export schema and data from database $databasename $d.exportbacpac($bacpacfile, $databasename) # clean up event unregister-event -source "msg" $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Completed export of database '$databasename' to '$bacpacfile'") ]]> </Task> </UsingTask> <UsingTask TaskFactory="PowershellTaskFactory" TaskName="ImportBacpac" AssemblyFile="$(AssemblyFile)"> <ParameterGroup> <ConnectionString Required="true" ParameterType="System.String" /> <BacpacFile Required="true" ParameterType="System.String" /> <DatabaseName Required="true" ParameterType="System.String" /><!-- Not relevant for Azure import, which uses the Bacpac file name as the database name --> </ParameterGroup> <Task> <![CDATA[ #write progress to activity log $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Starting import of database '$databasename' from '$bacpacfile' with connection string '$connectionstring' ") # load in DAC DLL (requires config file to support .NET 4.0) # change file location for a 32-bit OS add-type -path "$(SqlServerDacDll)" # make DacServices object, needs a connection string $d = new-object Microsoft.SqlServer.Dac.DacServices $connectionstring # register events, if you want 'em register-objectevent -in $d -eventname Message -source "msg" -action { $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High", $Event.SourceArgs[1].Message.Message) } # Load bacpac from file & import to database named $databasename $bp = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacfile) $d.importbacpac($bp, $databasename) # clean up event unregister-event -source "msg" $log.LogMessage([Microsoft.Build.Framework.MessageImportance]"High","Completed import of database '$databasename' from '$bacpacfile'") ]]> </Task> </UsingTask> </Project> 

Sample The purpose of invoking these wrappers is as follows:

 <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <Import Project="BacpacImportExport.xml"/> <PropertyGroup> <TempBacpacFile>$(ReleaseFolderPublish)\$(DestinationDBName).bacpac</TempBacpacFile> </PropertyGroup> <Target Name="CopyAndReplaceDatabaseViaBacpac"> <Message Text="Clean bacpac directory"/> <Exec Command="mkdir $(ReleaseFolderPublish)\" IgnoreExitCode="true"></Exec> <Exec Command="del /Q $(ReleaseFolderPublish)\*.bacpac " IgnoreExitCode="true"></Exec> <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Exporting database to BACPAC from source"/> <ExportBacpac ConnectionString="$(SourceConnectionString)" BacpacFile="$(TempBacpacFile)" DatabaseName="$(SourceDBName)" /> <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Dropping database from destination (does not fail on error)"/> <MSBuild.ExtensionPack.SqlServer.SqlCmd TaskAction="Execute" Server="$(DestinationDBServer)" Database="master" LogOn="$(DestinationDBUser)" Password="$(DestinationDBPass)" CommandLineQuery="DROP DATABASE [$(DestinationDBName)];" RedirectStandardError="true" SeverityLevel="1" /> <MSBuild Projects="$(MSBuildProjectFile)" Targets="ReportBuildProgress" Properties="Message=Importing database from BACPAC to destination"/> <ImportBacpac ConnectionString="$(DestinationConnectionString)" BacpacFile="$(TempBacpacFile)" DatabaseName="$(DestinationDBName)" /> </Target> </Project> 

Changes can easily be made to use the Hosted DAC by invoking the reference implementation of the console application via <Exec Command="" /> instead of calling <ImportBacpac /> .

If you notice any improvements, let me know!