Copy SQL Server 2012 database to SQL Azure via BACPAC (for Continuous Integration)

Question

I'm creating a continuous integration MSBuild script which copies a database in on-premise SQL Server 2012 to SQL Azure.

Easy right?

Methods

After a fair bit of research I've come across the following methods:

Use PowerShell to access the DAC library directly, then use the MSBuild PowerShell extension to wrap the script. This would require installing PowerShell 3 and working out how to make the MSBuild PowerShell extension work with it, as apparently MS moved the DAC API to a different namespace in the latest version of the library. PowerShell would give direct access to the API, but may require quite a bit of boilerplate.
Use the sample DAC Framework Client Side Tools, which requires compiling them myself, as the downloads available from Codeplex only include the Hosted version. It would also require fixing them to use DAC 3.0 classes as they appear to currently use an earlier version of DAC. I could then call these tools from an <Exec Command="" /> in the MSBuild script. Less boilerplate and if I hit any bumps in the road I can just make changes to the source.

Processes

Using whichever method, the process could be either:

Export from on-premise SQL Server 2012 to local BACPAC
Upload BACPAC to blog storage
Import BACPAC to SQL Azure via Hosted DAC

Or:

Export from on-premise SQL Server 2012 to local BACPAC
Import BACPAC to SQL Azure via Client DAC

Question

All of the above seems to be quite a lot of effort for something that seems to be a standard feature... so before I start reinventing the wheel and documenting the results for all to see, is there something really obvious that I've missed here? Is there pre-written script that MS has released that I have not yet uncovered?

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

Nick Evans Nick Evans · Accepted Answer · 2012-09-25T21:08:31

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

Using the MSBuild Extension Pack allows the creation of tasks to wrap the PowerShell boilerplate.

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

TaskFactory wrappers as follows:

<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 Target to call these wrappers would look 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>

This could easily be amended to use the Hosted DAC by calling the reference implementation console app via <Exec Command="" /> instead of calling <ImportBacpac />.

If you spot any improvements let me know!

Copy SQL Server 2012 database to SQL Azure via BACPAC (for Continuous Integration)

1 Answers