Edit

Share via

Verify a DACPAC file

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The process of converting an existing SQL project to an SDK-style project is done by manually editing the .sqlproj file to include the Microsoft.Build.Sql SDK-style project format. Backing up the project file and archiving a .dacpac of the project before beginning the conversion is recommended. By comparing a "before" and "after" .dacpac built from the project, you can ensure that the conversion process was correctly completed.

The DacpacVerify CLI (preview) is a command line tool that compares two .dacpac files and outputs the differences between the files. The tool is useful for verifying that a project conversion was successful by comparing the .dacpac files before and after the conversion.

Prerequisites

Similar to the SqlPackage command line tool, the DacpacVerify CLI (preview) is available as a dotnet tool. DacpacVerify can be installed on Windows, macOS, and Linux and requires the .NET SDK to be installed on your machine. To install the DacpacVerify CLI, run the following command:

dotnet tool install --global Microsoft.DacpacVerify --prerelease

DacpacVerify tool

The basic usage of the DacpacVerify tool involves running the dacpacverify command followed by the paths to the two .dacpac files that you want to compare.

dacpacverify before.dacpac after.dacpac

The tool outputs a summary of the differences between the two files. The verification of the .dacpac files includes:

  • pre-deployment scripts
  • post-deployment scripts
  • SQLCMD variables
  • database references
  • database properties/options
  • database objects (tables, views, stored procedures, etc.)

Verify a converted project

In this example, we'll verify a project conversion by comparing the .dacpac files before and after a SQL project conversion. Before the conversion, we build the project and create a .dacpac file, which defaults to the bin/Debug folder. Make a copy of the .dacpac file for comparison later with the name before.dacpac in a separate folder.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">
  <PropertyGroup>
    <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
    <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
    <Name>ConversionTest</Name>
    <SchemaVersion>2.0</SchemaVersion>
    <ProjectVersion>4.1</ProjectVersion>
    <ProjectGuid>{<unique identifier>}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <OutputType>Database</OutputType>
    <RootPath>
    </RootPath>
    <RootNamespace>ConversionTest</RootNamespace>
    <AssemblyName>ConversionTest</AssemblyName>
    <ModelCollation>1033, CI</ModelCollation>
    <DefaultFileStructure>BySchemaAndSchemaType</DefaultFileStructure>
    <DeployToDatabase>True</DeployToDatabase>
    <TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>
    <TargetLanguage>CS</TargetLanguage>
    <AppDesignerFolder>Properties</AppDesignerFolder>
    <SqlServerVerification>False</SqlServerVerification>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseSet>True</TargetDatabaseSet>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
    <OutputPath>bin\Release\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>False</TreatWarningsAsErrors>
    <DebugType>pdbonly</DebugType>
    <Optimize>true</Optimize>
    <DefineDebug>false</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|AnyCPU' ">
    <OutputPath>bin\Debug\</OutputPath>
    <BuildScriptName>$(MSBuildProjectName).sql</BuildScriptName>
    <TreatWarningsAsErrors>false</TreatWarningsAsErrors>
    <DebugSymbols>true</DebugSymbols>
    <DebugType>full</DebugType>
    <Optimize>false</Optimize>
    <DefineDebug>true</DefineDebug>
    <DefineTrace>true</DefineTrace>
    <ErrorReport>prompt</ErrorReport>
    <WarningLevel>4</WarningLevel>
  </PropertyGroup>
  <PropertyGroup>
    <VisualStudioVersion Condition="'$(VisualStudioVersion)' == ''">11.0</VisualStudioVersion>
    <!-- Default to the v11.0 targets path if the targets file for the current VS version isn't found -->
    <SSDTExists Condition="Exists('$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets')">True</SSDTExists>
    <VisualStudioVersion Condition="'$(SSDTExists)' == ''">11.0</VisualStudioVersion>
  </PropertyGroup>
  <Import Condition="'$(SQLDBExtensionsRefPath)' != ''" Project="$(SQLDBExtensionsRefPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <Import Condition="'$(SQLDBExtensionsRefPath)' == ''" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
  <ItemGroup>
    <Folder Include="Properties" />
  </ItemGroup>
  <ItemGroup>
    <Build Include="Table1.sql" />
    <Build Include="View1.sql" />
    <Build Include="View2.sql" />
  </ItemGroup>
  <ItemGroup>
    <SqlCmdVariable Include="EnvironmentName">
      <DefaultValue>
      </DefaultValue>
      <Value>$(SqlCmdVar__1)</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

Related content