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.

How will you compare the schema if you can't get it from the client? — kchason
– kchason, Commented Nov 1, 2017 at 20:03
You explain one of the ways this can be done. Not really sure what the question is here. — Sean Lange
– Sean Lange, Commented Nov 1, 2017 at 20:05
the general way to do this is to use versioning and update/rollback scripts, the version of schema deployed is stored at client's and you update as you wish. my personal favourite migration tool is mybatis migrations, other options stackoverflow.com/questions/131020/migrations-for-java — MarianP
– MarianP, Commented Nov 1, 2017 at 21:01
SQL Server Data Tools comes with a schema compare and migration tool. But if you can't get access to your clients schema what do you think you can do? You could try and use the tool to automatically generate a change script on the client side, but that could be disastrous. — Nick.Mc
– Nick.Mc, Commented Nov 1, 2017 at 22:55
Do you own SQL Compare? If so, I'll post an answer here describing how you can get the schema from your client without them requiring a copy of SQL Compare. — David Atkinson
– David Atkinson, Commented Nov 22, 2017 at 23:36

Harshit Gindra · Accepted Answer · 2017-11-07 16:55:04Z

6

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.

answered Nov 7, 2017 at 16:55

Harshit Gindra

3654 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ackdari Over a year ago

You actually can create the deplyoment script with just the Script action. As source you set the build dacpac-file and as target the database connection (with user, password, etc.)

Collectives™ on Stack Overflow

Automatically create database compare scripts and execute

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related