3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Harshit Gindra
  • 355
  • 3
  • 7
  • 5
    How will you compare the schema if you can't get it from the client? – kchason Nov 01 '17 at 20:03
  • 3
    You explain one of the ways this can be done. Not really sure what the question is here. – Sean Lange Nov 01 '17 at 20:05
  • 1
    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 https://stackoverflow.com/questions/131020/migrations-for-java – MarianP Nov 01 '17 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 Nov 01 '17 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 Nov 22 '17 at 23:36

1 Answers1

5

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.

Harshit Gindra
  • 355
  • 3
  • 7
  • 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.) – Ackdari Sep 02 '20 at 09:54