SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to sync database schema from a database to another?


How to sync database schema from a database to another?

Author
Message
gogozeebra
gogozeebra
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6785 Visits: 1865
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"
Michael Earl-395764
Michael Earl-395764
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13469 Visits: 23078
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.
Chris Harshman
Chris Harshman
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11043 Visits: 4683
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/
Mauve
Mauve
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3390 Visits: 2066
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.
ChiragNS
ChiragNS
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6785 Visits: 1865
Iam with you on that .

"Keep Trying"
Pauline Powell-387612
Pauline Powell-387612
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 10
I agree SQL Compare is a good tool for what you are looking for.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search