DTS for Database Schema Synchronization | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS for Database Schema Synchronization

Hello, I have same database installed on two server and now I have changed schema in one server and need to to synchronize the database schema between two. Both the database contain some data and which I dont what to distrub. Do u have any idea what would be the way to go? Can I use DTS to synchornize database schema between two databases? Let me know. Regards,
Nilay.
You could use a script like this:
http://www.sql-server-performance.com/vg_database_comparison_sp.asp Or a product like this:
http://www.red-gate.com/SQL_Compare.htm /Argyle
I tried out sp_CompareDB
It’s documentation seems to meet our requirement perfectly.
However, I am not able to execute it on database names like SCOPES-LAPTOP etc.
The error I get is : "Line 1: Incorrect syntax near ‘-‘."
Please advise …
To get the script to work with database names with dash or space in them you need to add two lines to the SP: set @db1 = ‘[‘ + @db1 + ‘]’
set @db2 = ‘[‘ + @db2 + ‘]’ Add them after the database name checks and before the comparison starts (on line 116 or close to it). Replace:
print Replicate(‘-‘,LEN(@db1)+LEN(@db2)+25)
print ‘Comparing databases ‘[email protected]+’ and ‘[email protected]
print Replicate(‘-‘,LEN(@db1)+LEN(@db2)+25) With:
set @db1 = ‘[‘ + @db1 + ‘]’
set @db2 = ‘[‘ + @db2 + ‘]’
print Replicate(‘-‘,LEN(@db1)+LEN(@db2)+25)
print ‘Comparing databases ‘[email protected]+’ and ‘[email protected]
print Replicate(‘-‘,LEN(@db1)+LEN(@db2)+25) /Argyle
Infact I tried sending the DB names in square brackets .. as parameters to the SP
eg: sp_CompareDB ‘[SCOPES-LAPTOP]’,'[SCOPES-SERVER]’ It still gave the same error
Sending in the name in brackets will not work. That’s why I said you need to change the code as mentioned in my previous post. "Add them after the database name checks and before the comparison starts (on line 116 or close to it)." And then execute it as:
sp_CompareDB ‘SCOPES-LAPTOP’,’SCOPES-SERVER’ /Argyle
It worked ! Thanks !
However, I really wouldn’t know how to use the much of information that the SP results in.
I basically want to generate a .sql file or any other method by which I can generate "ALTER" scripts for all tables,SPs, Views, Triggers, Functions etc.
In short, programatically generating a DB installation script without disrupting the data.
Then I would recommend you to try outhttp://www.red-gate.com/SQL_Compare.htm to see if it fit your needs. Think it can generate change scripts. /Argyle
]]>