How to synchronize Tables (Structure not Data) of two SQL Server databases?

Question

We have two different databases of SQL Server, called DB1 and DB2. I want to synchronize tables, views and stored procedures of these two databases. Let me clarify, I am not asking for a data sync - what I want is to only sync the structure of the two databases.

Thanks in advance, regards.

This seems like an odd request, but you could set up DDL triggers (learn.microsoft.com/en-us/sql/relational-databases/triggers/…). — Gordon Linoff
– Gordon Linoff, Commented Sep 15, 2018 at 12:49
So what's the big deal? You make a change in one and you immediately do the same thing in the other. Do this with scripts and it is trivial to accomplish. If you use SSMS gui - well, don't. Do it with scripts. You will become a better, more effective, and more proficient developer. — SMor
– SMor, Commented Sep 15, 2018 at 13:05
Dear SMor, in this scenario, it hasn't happen. I just updated one db, now I want to update other one accordingly. Doing this with scripts one by one will take lot of time also need to search which table needs to be added and which is not. — Babar Khalid
– Babar Khalid, Commented Sep 15, 2018 at 13:08
Are both tables in the same database Or do you need to sync 2 databases (which is common task and have lot of tools for this) ? — Ronen Ariely
– Ronen Ariely, Commented Sep 15, 2018 at 13:20
There are tools - SSDT is one - that will compare schemas and generate a script to make them the same. — SMor
– SMor, Commented Sep 15, 2018 at 13:21

David Browne - Microsoft · Accepted Answer · 2018-09-15 18:31:00Z

3

What you are asking for is called "Schema Compare", and there are several tools that can do this for you, including the free SQL Server Data Tools, available for download here.

Views and stored procedures can simply be scripted from one database and applied to the other, but for tables you need to calculate the differences between the existing and new table and generate a script to apply the changes without loosing the data currently in the target tables.

edited Sep 15, 2018 at 18:31

answered Sep 15, 2018 at 13:21

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

How to synchronize Tables (Structure not Data) of two SQL Server databases?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related