SQL Server Performance

DTS for Database Schema Synchronization

Discussion in 'SQL Server DTS-Related Questions' started by nilayinc, Aug 6, 2003.

  1. nilayinc New Member

    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.
  2. Argyle New Member

  3. nilayinc New Member

    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 ...
  4. Argyle New Member

    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 '+@db1+' and '+@db2
    print Replicate('-',LEN(@db1)+LEN(@db2)+25)

    With:
    set @db1 = '[' + @db1 + ']'
    set @db2 = '[' + @db2 + ']'
    print Replicate('-',LEN(@db1)+LEN(@db2)+25)
    print 'Comparing databases '+@db1+' and '+@db2
    print Replicate('-',LEN(@db1)+LEN(@db2)+25)

    /Argyle
  5. nilayinc New Member

    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
  6. Argyle New Member

    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
  7. nilayinc New Member

    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.
  8. Argyle New Member

Share This Page