Post reply

How to sync database schema from a database to another?

  • December 9, 2008 at 2:35 am

    #92900

    Hi,

    Here is the scenario.

    Our company have 2 databases, database DEV is used for development and database PROD is the actual database used for our applications.

    The developers are using DEV database to create or alter tables, store procedures and so on. After they finish the development phase, they need to deploy the modification to PROD database.

    The point is this deployment is not a backup and recover process that means we only sync the database schema from DEV to PROD and we do not sync any data or record.

    Could anyone please give me a suggestion to automatically achieve this requirement by sql query?

    Thanks,

    Wayne

  • December 9, 2008 at 4:59 am

    #909551

    Iam not sure how much you can automate this task or would even want to automate unless iam dealing with a very large number of changes.

    Anyway i do it manually so i dont know exactly how to automate.

    First you would need all the change scripts.

    The you can use some scripting language to take these change scripts and run them in your target server in the CORRECT Order.

    Sorry i cant help you much...

    "Keep Trying"

  • December 9, 2008 at 5:47 am

    #909570

    There are a number of MS SQL database comparison tools on the market that will compare your database schema's and generate change scripts. A great one is by our sponsors and called SQL Compare, there is one by xSQL called xSQL Object that I use (it's pretty good), Quest software makes one that is free if your schema has only a few objects, and I am sure if you search on Google for a bit you will find a number of others.

  • December 9, 2008 at 7:48 am

    #909650

    I agree with Michael that RedGate's SQL Compare is a fine product, we use it for the very same purpose you're looking for where I work. I've also worked with another good product called SQL Examiner:

    http://www.sqlaccessories.com/

    Both of those tools will do a compare and produce a script for you of the changes to apply to the database.

    If you need a cheaper solution, there's SQL Accord, (recently recommended in the article free DBA tools part 2) which does have a free community edition that doesn't produce scripts for you but still has a very nice user interface to find all the differences:

    http://www.sqleffects.com/

  • December 10, 2008 at 8:00 am

    #910388

    This has been extensively discussed on the forum along with some suggestions, including use of a proper source control system.

    See my message (474053) as to how we do it in our company at:

    http://www.sqlservercentral.com/Forums/Topic472950-146-1.aspx#bm474053

    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • December 10, 2008 at 11:18 pm

    #910834

    Iam with you on that .

    "Keep Trying"

  • August 9, 2010 at 12:58 pm

    #1204343

    I agree SQL Compare is a good tool for what you are looking for.

Viewing 7 posts - 1 through 6 (of 6 total)

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