Comparing databases using SSDT

<# 
.SYNOPSIS
 Compare 2 databases using SSDT (sqlpackage.exe)

.DESCRIPTION
 Invoke an SSDT dewployment using sqlpackage.exe. This will generate a DACPAC for the source
 and then create a T-SQL Script and a DeployReport against a target database.

.PARAMETER SourceServer 
 Source database server. This is where the "gold copy" lives.

.PARAMETER SourceDatabase 
 Source database or the "gold copy" database. 

.PARAMETER TargetServer 
 Target database server. 

.PARAMETER TargetDatabase 
 Target database. This is the database that is being compared to the gold copy.

.PARAMETER ArtifactPath 
 Working directory for the scripts to be placed in

.PARAMETER SqlPackagePath 
 Path to sqlpackage.exe

.EXAMPLE
 Compare-DatabasesSSDT

#>
function Compare-DatabasesSSDT
{
    [cmdletbinding()]
    param(
        [parameter(Mandatory=$true)][string]$SourceServer, 
        [parameter(Mandatory=$true)][string]$SourceDatabase, 
        [parameter(Mandatory=$true)][string]$TargetServer, 
        [parameter(Mandatory=$true)][string]$TargetDatabase,
        [parameter(Mandatory=$true)][string]$ArtifactPath = 'c:\temp\', # A wowrking directory. Defaults to c:\temp
        [parameter(Mandatory=$false)][string]$SqlPackagePath = 'C:\Program Files\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe' # Path to sqlpackage.exe
    )

    # Create artifact file if one does not exist.
    if (!(Test-Path -Path $($ArtifactPath)))
    {
        New-Item -ItemType Directory -Force -Path "$($ArtifactPath)" | Out-Null
    }

    try {
        $SourceDACPAC = "$($ArtifactPath)\$($SourceDatabase).dacpac"
        $TargetScript = "$($ArtifactPath)\$($TargetDatabase)_Script.sql"
        $TargetDriftReport = "$($ArtifactPath)\$($TargetDatabase)_DeployReport.xml"

        # sqlpackage export the source DACPAC
        &amp;"$SqlPackagePath" /a:Extract /ssn:$SourceServer /sdn:$SourceDatabase /tf:$SourceDACPAC /p:IgnorePermissions=True 2>&amp;1

        # sqlpackage script
        &amp;"$SqlPackagePath" /a:Script /sf:$SourceDACPAC /tsn:$TargetServer /tdn:$TargetDatabase /op:$TargetScript `
            /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True 2>&amp;1

        # sqlpackage driftreport
        &amp;"$SqlPackagePath" /a:DeployReport /sf:$SourceDACPAC /tsn:$TargetServer /tdn:$TargetDatabase /op:$TargetDriftReport `
            /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True 2>&amp;1

    }
    catch {
        throw $_
    }
}

# Unit testing commands
$ErrorActionPreference = "Stop"
$error.clear()

$params = @{
    'SourceServer' = 'localhost' ;
    'SourceDatabase' = 'AdventureWorks2012' ;
    'TargetServer' = 'localhost' ;
    'TargetDatabase' = 'AdventureWorks2012_2' ;
    'ArtifactPath' = 'c:\temp' ;
    'SqlPackagePath' = 'C:\Program Files\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe'
}

Get-Date
Compare-DatabasesSSDT @params -Verbose 
Get-Date
#>

/*
Deployment script for AdventureWorks2012_2

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 "AdventureWorks2012_2"
:setvar DefaultFilePrefix "AdventureWorks2012_2"
:setvar DefaultDataPath "C:\SQLData1\"
:setvar DefaultLogPath "C:\SQLLog1\"

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
USE [$(DatabaseName)];
GO

PRINT N'Update complete.';
GO

<?xml version="1.0" encoding="utf-8"?>
<DeploymentReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02"><Alerts />
</DeploymentReport>

/*
Deployment script for AdventureWorks2012_2

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 "AdventureWorks2012_2"
:setvar DefaultFilePrefix "AdventureWorks2012_2"
:setvar DefaultDataPath "C:\SQLData1\"
:setvar DefaultLogPath "C:\SQLLog1\"

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
USE [$(DatabaseName)];


GO
PRINT N'Creating [dbo].[SQLPackageDemo]...';


GO
CREATE TABLE [dbo].[SQLPackageDemo] (
    [id]             INT        NOT NULL,
    [sqlpackagedemo] NCHAR (10) NOT NULL
);


GO
PRINT N'Update complete.';


GO

<?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].[SQLPackageDemo]" Type="SqlTable" />
	</Operation>
</Operations>
</DeploymentReport>