Previewing DACPAC Deployments By Generating Deployment Reports And Scripts

A DACPAC is a method for deploying a database via code. You specify the desired state of the database and the tooling determines how to modify the target database to match the desired state. (see https://learn.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications for more details)

You can preview what would happen if a specific DACPAC would be deployed to a database.

If you’re using SqlPackage you can specify SqlPackage /Action:DeployReport for an XML report or SqlPackage /Action:Script for T-SQL script (see https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-deploy-drift-report / https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-script).

Tests

using Microsoft.SqlServer.Dac;
using ThrowawayDb;
using Xunit;

namespace TestDatabase.Tests
{
    public class PreviewTests
    {
        [Fact]
        public void DacServicesGenerateDeployReport()
        {
            // Arrange
            using var database = ThrowawayDatabase.FromLocalInstance(@"(LocalDB)\MSSQLLocalDB");

            var dacPackage = DacPackage.Load("../../../../../src/TestDatabase/bin/Debug/TestDatabase.dacpac");
            var dacServices = new DacServices(database.ConnectionString);

            // Act
            var deployReport = dacServices.GenerateDeployReport(dacPackage, database.Name);

            // Assert
            Assert.Equal(
                @"<?xml version=""1.0"" encoding=""utf-8""?><DeploymentReport xmlns=""http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02""><Alerts /><Operations><Operation Name=""Create""><Item Value=""[dbo].[TestTable]"" Type=""SqlTable"" /></Operation></Operations></DeploymentReport>",
                deployReport);
        }

        [Fact]
        public void DacServicesGenerateDeployScript()
        {
            // Arrange
            using var database = ThrowawayDatabase.FromLocalInstance(@"(LocalDB)\MSSQLLocalDB");

            var dacPackage = DacPackage.Load("../../../../../src/TestDatabase/bin/Debug/TestDatabase.dacpac");
            var dacServices = new DacServices(database.ConnectionString);

            // Act
            var deployScript = dacServices.GenerateDeployScript(dacPackage, database.Name);

            // Assert
            var user = "USER_HERE";
            Assert.Equal(
$@"/*
Deployment script for {database.Name}

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName ""{database.Name}""
:setvar DefaultFilePrefix ""{database.Name}""
:setvar DefaultDataPath ""C:\Users\{user}\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\""
:setvar DefaultLogPath ""C:\Users\{user}\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB\""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled ""True""
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET PAGE_VERIFY NONE,
                DISABLE_BROKER 
            WITH ROLLBACK IMMEDIATE;
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS 
    WITH ROLLBACK IMMEDIATE;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367)) 
            WITH ROLLBACK IMMEDIATE;
    END


GO
PRINT N'Creating SqlTable [dbo].[TestTable]...';


GO
CREATE TABLE [dbo].[TestTable] (
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Update complete.';


GO
",
deployScript);
        }
    }
}

Demo

These tests are available at https://github.com/kendaleiv/dacpac-playground/blob/main/tests/TestDatabase.Tests/PreviewTests.cs.