Automatically create database compare scripts and execute

Question

I have a situation where I have to compare my database version (schema) with all my clients (possibly different versions) and deploy necessary changes to make all versions alike. I'm looking for a way that automatically compares my database schema with my client's schema, generates an difference script and executes it making sure that all the versions are same.

I'm aware about Red Gate and similar tools, but the problem is that I need to get the schema from my client (which is difficult to get).

Thanks in advance.

Answer 1

I finally got a possible solution for the question. I'm using SqlPackage utility tool to generate a dacpac, compare client's dacpac with mine, create a difference script and deploy the changes.

Here are the commands

Create Dacpac

sqlpackage.exe /Action:Extract /SourceServerName:DbServer /SourceDatabaseName:DbName /TargetFile:"C:\Working Folder\Client.dacpac" /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True /p:IgnoreUserLoginMappings=True /p:VerifyExtraction=True

Compare Dacpacs and generate deployment script

sqlpackage.exe/a:Script /sf:"C:\Working Folder\Primary.dacpac" /tf:"C:\Working Folder\Client.dacpac" /tdn:"DbName" /op:"C:\Working Folder\DifferenceScript.sql"

Running script

sqlcmd -S DbServer -i "C:\Working Folder\DifferenceScript.sql" -o "C:\Working Folder\Output.txt"

Output.txt file contains results of the deployment. So all I've to do is run these commands and it takes care of everything.

Here is another helpful link.