SQL Database Schema Synchronization via SQLPackage.exe and PowerShell

By: Jeffrey Yao | Comments (4) | Related: More > Comparison Data and Objects

Problem

There are many times that we may want to update a SQL Server database with another database to achieve schema level synchronization. For example, in a team development environment, each developer may have their own environment while at the same time want to import changes from a centralized database where every team member has consolidated their changes.

A similar requirement is that we may want to know whether there is any difference between two databases in terms of metadata of all objects, like tables, views, stored procedures, indexes, etc. To fulfill the requirement, we usually have to rely on 3rd party tools or we need to write a complex procedure to do the work, is there other way to do this work?

Solution

Actually, Microsoft has provided a very powerful tool called SQLPackage.exe, it is part of the SQL Server Data Tools installation. We can use it to achieve the following goals:

Check whether two databases are same at schema level, i.e. same composition of database objects, like tables, views, stored procedures, user defined functions, etc.
If needed, synchronize two databases at the schema level, but not data synchronization

Test Environment Setup

We will create two databases [DB_Source] and [DB_Target], and then create a few database objects inside each for testing purposes.

use Master
create database DB_Source;
go
create database DB_Target;
go
alter database DB_Source set recovery simple;
alter database DB_Target set recovery simple;
go

We now create the same set of objects in the two databases:

use DB_Target
--drop table dbo.t;
--drop proc dbo.uspTest;

create table dbo.t (id int identity, a varchar(30));
go
create proc dbo.uspTest (@name varchar(100))
as 
begin
 print 'hello ' + @name
end
go


use DB_Source
--drop table dbo.t;
--drop proc dbo.uspTest;

create table dbo.t (id int identity, a varchar(30));
go
create proc dbo.uspTest (@name varchar(100))
as 
begin
 print 'hello ' + @name
end
go

Since these two databases are exactly the same in terms of database objects contained, we should not expect to see anything different.

We will first look at the PowerShell code on how to compare the objects. The code below is used to compare a source database with a target database, both databases in the example are located on my local [SQL2016] instance. In the real world, they can be on different instances.

# add path for SQLPackage.exe
IF (-not ($env:Path).Contains( "C:\program files\microsoft sql server\130\DAC\bin"))
{ $env:path = $env:path + ";C:\program files\microsoft sql server\130\DAC\bin;" }

sqlpackage /a:extract /of:true /scs:"server=.\sql2016;database=db_source;trusted_connection=true" /tf:"C:\test\db_source.dacpac";

sqlpackage.exe /a:deployreport /op:"c:\test\report.xml" /of:True /sf:"C:\test\db_source.dacpac" /tcs:"server=.\sql2016; database=db_target;trusted_connection=True" 

[xml]$x = gc -Path "c:\test\report.xml";
$x.DeploymentReport.Operations.Operation |
% -Begin {$a=@();} -process {$name = $_.name; $_.Item | %  {$r = New-Object PSObject -Property @{Operation=$name; Value = $_.Value; Type = $_.Type} ; $a += $r;} }  -End {$a}

Here is an explanation of the script:

Add the sqlpackage.exe location path, i.e. "C:\program files\microsoft sql server\130\DAC\bin", in the environment.
Run sqlpackage.exe to extract the metadata information out of the source database and put into a .dacpac file, i.e. c:\test\db_source.dacpak.
Run sqlpackage.exe to generate a report (in XML format) on what changes would be applied to synchronize the target database with the source dacpak file.
Parse the report.xml file to retrieve the changed information and present it in a table format.

If we open a PowerShell ISE and run the above script, we will get the following output:

There are no schema differences between the source and target databases

We can open the generated c:\test\report.xml with Microsoft Edge browser, and can see that the file is actually empty as shown below:

$c:\test\report.xml is actually empty$

The result is exactly as expected because the two databases are identical.

Now we will look at a few cases when the source database is different from the target database.

Case 1: Modification of Existing Database Objects

We first modify the source database in three places as shown below:

Make [id] column primary key for table [dbo].[t]
Add a new column [b] int for table [dbo].[t]
Modify the stored procedure [dbo].[uspTest]

use DB_Source
drop table dbo.t;
drop proc dbo.uspTest;
go
--1. make [id] column as primary key, and 2. add a new column [b]
create table dbo.t (id int identity primary key, a varchar(30), b int);
go
--3. modify the SP by changing 'hello ' to 'hi '
create proc dbo.uspTest (@name varchar(100))
as 
begin
 print 'hi ' + @name
end
go

If we run the previous PS script again, we will get the following, and it shows exactly 3 changes.

PowerShell result of modifying existing SQL Server database objects

We will later see the exact changes that SQLPackage.exe can provide us.

Case 2: Adding New SQL Server Database Objects

In this case, we will add two new objects, one table and one stored procedure on top of the Case 1 changes.

use DB_Source
drop table dbo.t;
drop proc dbo.uspTest;
go
--1. make [id] column as primary key, and 2. add a new column [b]
create table dbo.t (id int identity primary key, a varchar(30), b int);
go
--3. modify the SP by changing 'hello ' to 'hi '
create proc dbo.uspTest (@name varchar(100))
as 
begin
 print 'hi ' + @name
end

--4. adding a table
create table dbo.t2 (id int)
go
--5. adding a SP
create proc dbo.uspTest2 
as
  select CurrentTime=getdate();
go

If we run the previous PowerShell script again, we will get the following, and it shows exactly 2 more additions.

PowerShell results of adding new SQL Server database objects

The result just shows us what has changed, but does not tell us the change details. To show the real changes in the script, we should run the following:

sqlpackage.exe /a:script /op:"c:\test\change.sql" /of:True /sf:"C:\test\db_source.dacpac" /tcs:"server=.\sql2016; database=db_target;trusted_connection=True"

The published T-SQL change script between the source and target databases

This will generate a T-SQL script at "c:\test\change.sql", we can open it in SQL Server Management Studio and will get the following code:

/*
Deployment script for db_target

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 "db_target"
:setvar DefaultFilePrefix "db_target"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\"

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'Altering [dbo].[t]...';


GO
ALTER TABLE [dbo].[t]
    ADD [b] INT NULL;


GO
PRINT N'Creating unnamed constraint on [dbo].[t]...';


GO
ALTER TABLE [dbo].[t]
    ADD PRIMARY KEY CLUSTERED ([id] ASC);


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


GO
CREATE TABLE [dbo].[t2] (
    [id] INT NULL
);


GO
PRINT N'Altering [dbo].[uspTest]...';


GO


ALTER proc dbo.uspTest (@name varchar(100))
as 
begin
 print 'hi ' + @name
end
GO
PRINT N'Creating [dbo].[uspTest2]...';


GO
-- adding a SP
create proc dbo.uspTest2 
as
  select CurrentTime=getdate();
GO
PRINT N'Update complete.';


GO

If we omit the header part, which is variable setup, we can see that all the changes we have made are there in the script, including adding new objects and modifying existing objects.

Case 3: Removing an Existing SQL Server Database Object

If we delete an object in [DB_Source], such as [dbo].[uspTest] while it still exists in the [DB_Target], what will happen?

use db_Source

--drop table dbo.t;
--drop proc dbo.uspTest;
go
create table dbo.t (id int identity, a varchar(30));
go

Assume [DB_Source] has only one object, [dbo].[t], now if we run the PowerShell script from above, we will find there is no difference:

default result from PowerShell of removing a Database object

The reason is that by default, SQLPackage.exe will NOT generate a synchronization result that deletes the existing target object when that object does not exist in the source side.

To force SQLPackage.exe to delete any target objects that are not in the source side, we need to add an additional parameter, i.e. /P:DropObjectsNotInSource=True, as shown below:

# add path for SQLPackage.exe
IF (-not ($env:Path).Contains( "C:\program files\microsoft sql server\130\DAC\bin"))
{ $env:path = $env:path + ";C:\program files\microsoft sql server\130\DAC\bin;" }

sqlpackage /a:extract /of:true /scs:"server=.\sql2016;database=db_source;trusted_connection=true" /tf:"C:\test\db_source.dacpac";

sqlpackage.exe /a:deployreport /op:"c:\test\report.xml" /of:True /sf:"C:\test\db_source.dacpac" /tcs:"server=.\sql2016; database=db_target;trusted_connection=True" /P:DropObjectsNotInSource=True 

[xml]$x = gc -Path "c:\test\report.xml";
$x.DeploymentReport.Operations.Operation |
% -Begin {$a=@();} -process {$name = $_.name; $_.Item | %  {$r = New-Object PSObject -Property @{Operation=$name; Value = $_.Value; Type = $_.Type} ; $a += $r;} }  -End {$a}

If we run this PS script, we will get the following

Drop SQL Server Database objects from the comparison

Just ignore the first two permission statement drop operations because they are always there when using the parameter /P:DropObjectsNotInSource=True, even if there is no database object difference between the target and the source.

We can run the following command to see what exactly the drop statement is for the stored procedure

sqlpackage.exe /a:script /op:"c:\test\change.sql" /of:True /sf:"C:\test\db_source.dacpac" /tcs:"server=.\sql2016; database=db_target;trusted_connection=True" /P:DropObjectsNotInSource=True

We can then open "c:\test\change.sql" in an SSMS window:

/*
Deployment script for db_target

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 "db_target"
:setvar DefaultFilePrefix "db_target"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\"

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'Dropping Permission...';

GO
REVOKE VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO PUBLIC CASCADE;


GO
PRINT N'Dropping Permission...';


GO
REVOKE VIEW ANY COLUMN MASTER KEY DEFINITION TO PUBLIC CASCADE;


GO
PRINT N'Dropping [dbo].[uspTest]...';


GO
DROP PROCEDURE [dbo].[uspTest];


GO
PRINT N'Update complete.';


GO

At the bottom of the script, we can see DROP PROCEDURE [dbo].[uspTest];, that's exactly what is expected.

Summary

In this tip, we have shown how to use SQLPackage.exe command line tool to report the difference in object schema between two databases, and also how to generate the synchronization T-SQL script, with the help of PowerShell.

This tool is not as fancy as many 3rd party tools, but it solves the key requirements we usually have, i.e. find any discrepancies between two databases? If so, what are they and then generate a synchronization script. It is free and you will never worry about the SQL Server version change.

Based on this tip, we can easily create a PowerShell function to automatically synchronize two database schemas, and also generate a report together with the synchronization script to be saved for auditing purposes.

Next Steps

You can play with SQLPackage.exe in more diverse scenarios, such as modifying a table column name, adding/dropping database users, or other database objects, and see what you can get.

It is worthwhile to look at this "database object comparison" question from some other perspectives as listed below:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

SQL Server Database Schema Synchronization via SQLPackage.exe and PowerShell