Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to sync database schema from a database to another? Expand / Collapse
Author
Message
Posted Tuesday, December 9, 2008 2:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 1, 2010 2:58 AM
Points: 6, Visits: 22
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
Post #615984
Posted Tuesday, December 9, 2008 4:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 2,366, Visits: 1,845
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"
Post #616034
Posted Tuesday, December 9, 2008 5:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #616055
Posted Tuesday, December 9, 2008 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:24 AM
Points: 1,874, Visits: 2,020
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/
Post #616152
Posted Wednesday, December 10, 2008 8:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:38 AM
Points: 1,182, Visits: 1,972
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



(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.
Post #617054
Posted Wednesday, December 10, 2008 11:18 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 2,366, Visits: 1,845
Iam with you on that .


"Keep Trying"
Post #617610
Posted Monday, August 9, 2010 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 16, 2011 2:50 PM
Points: 3, Visits: 9
I agree SQL Compare is a good tool for what you are looking for.
Post #966176
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse