Post reply

how to sync database code between two databases

  • April 8, 2009 at 8:19 pm

    #219898

    Hi guys,

    Thanks in advance...

    I have an issue going on right now. actually we have a QA database and dev db at remote locations.

    the QA database is up to date and dev is not. so we need to sync the both qa and dev database code(stored procs,views,schemas,functions etc..) not data. and also any future updates in qa should reflect in dev....

    I have idea of using db artisan change manager or sql compare..

    what is the best idea for this or if there is any code to get only code from the database.

    Please give me some idea about what is the best option I can do..

  • April 8, 2009 at 8:57 pm

    #974810

    As long as there are no Table metadata changes, script everything except tables (DROP & CREATE) form QA, and then execute the script on DEV.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 9, 2009 at 1:47 am

    #974873

    There are also some excellent tools on the market that can automate this process for you and they are quite reasonably priced.

    Take a look at SQLCompare [/url]from RedGate and Quest Software have a tool called Change Director too.

    Cheers,

    John Sansom (@sqlBrit) | www.johnsansom.com

  • April 9, 2009 at 5:28 am

    #974933

    There is no "best" tool, there's what works for you and your situation. I can make a few suggestions.

    Start treating your database like code. Check all the scripts into source control (VSS, TFS, whatever) along with the application code. Manage your releases in conjunction with the code releases, labeling and versioning your database along side the application.

    Then, implement processes & tools to management migration. My two favorite tools are either the Visual Studio Team System Database Edition (soon to be folded into the Developers edition) or Red Gate's SQL Compare. Either of these will facilitate you working with your database as code.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 9, 2009 at 1:07 pm

    #975367

    Thanks for your suggestions..

    and sorry for the posting twice...

    But I want to know is there any local sql server feature or tool which can help to sync the

    code between two databases.

    Please provide me if yo have a code to just copy the structure or ddl of the database..

    Thanks in advance

  • April 9, 2009 at 1:18 pm

    #975378

    HVPR (4/9/2009)

    Thanks for your suggestions..

    and sorry for the posting twice...

    But I want to know is there any local sql server feature or tool which can help to sync the

    code between two databases.

    Please provide me if yo have a code to just copy the structure or ddl of the database..

    Thanks in advance

    The Script Generator is built into Management Studio. What do you need that it does not provide?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 9, 2009 at 1:23 pm

    #975380

    HVPR (4/9/2009)

    Thanks for your suggestions..

    and sorry for the posting twice...

    But I want to know is there any local sql server feature or tool which can help to sync the

    code between two databases.

    Please provide me if yo have a code to just copy the structure or ddl of the database..

    Thanks in advance

    Yep, what Barry says is right.

    If that's not doing what you need, you can write code against the System Management Object (SMO). It provides quite a few scripting options, but it requires more than a little bit of work on your part.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 10, 2009 at 9:13 am

    #975823

    I just renewed my subscription to RedGate and it's incredible. I've saved so much time just using thier tools. I found the scripting option works much better and faster than the microsoft tools. I also use the data compare tool on my reference tables and I can create insert/upate statements for Dev, QA and production.

    They also have tools to capture schemas from client sites that you can ship back to the office and run compares. Also, when writing SQL the intellisense saves me some time also.

    Download the 14 day trial and see how easy it is. I will easlily recoup my investment and do it with greater accuracy.

    Doug

  • April 24, 2009 at 3:55 am

    #983435

    Have you tried using replication. You could just replicate the procs, functions etc;

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply