We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored procedures and functions.
In server database we are changing stored procedures, functions, as well as tables. These changes are to be synchronized into Local DB i.e.
Common at both databases:
table 1 (id, name, price)
Changes happened in Server DB:
table 1 (id, name, price, IsActive)
We want IsActive field to be synchronized into local db, same way we need to sync stored procedures and functions.
So in simple words we need a solution to sync stored procedures, functions, and table schema without messing up existing data.
Can any one suggest any tool or code with which we can satisfy the same? With some google and search we found a codeproject article but that can be used for fresh database. Still sharing the link.
Thanks
Best Answer
SQL Server Data Tools (SSDT) has the ability to do just this. It can compare a source and target database, determine the differences between them for many classes of object including tables and executables. It can produce a script to make the target look like the source. You get to choose which changes to include and which to ignore.
It is not magic, however. If you had new column and declare it as NOT NULL in the source, then try and apply that to a target table that contains data it will fail because no default is specified. Be sure to properly account for this and similar circumstances.