Monday, March 19, 2012

Compare two databases and update objects?

Is there a way to compare two databases one being an old
database and the second being a new database, I will
update the old database's objects to match the new database's
objects?

Is there SQL code that could do this easily?

How do you do this if you deal with the same scenario?

Thank youserge (sergea@.nospam.ehmail.com) writes:
> Is there a way to compare two databases one being an old
> database and the second being a new database, I will
> update the old database's objects to match the new database's
> objects?
> Is there SQL code that could do this easily?

The standard recommendation is to look at SQL Compare from Red Gate.

> How do you do this if you deal with the same scenario?

I'm avoiding it by having my code under version control, and keeping
track of what I shipped. Our load tool actuall has its own set of tables
to do this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||DB Ghost is a tool that will compare databases and upgrade a target
database to make it match a source.

It also integrates with any source control system so the 'source'
database becomes a simple set of drop/create scripts. The power of
this approach is that it enables any size of development team to work
on the schema under source control at the same time using the same
mechanism that they do for other application code such as C#, Java, VB,
C++ etc. This provides you with a full audit trail of who made what
changes to the schema, when and why.

This process is one that delivers more as your development needs
increase. For example it doesn't matter how many developers work on
the scripts in source control, the amount of time required to extract
all the scripts and perform the upgrade doesn't noticeably increase.
Also, this process works perfectly with parallel development as it
means that concepts such as isolated worksets and merges between code
lines becomes as easy as it is with normal application code. Try doing
parallel development with delta scripts, it is a completely error prone
manual process of looking through the delta scripts to work out what
has been changed!

Diff tools such as SQL Compare are great at what they do but,
ultimately, they are really just there to get you out of trouble.

This trouble is normally caused by not having proper processes and
tools to control changes to your schema.

DB Ghost plus any source control system gives you such a process.

Malcolm
www.dbghost.com
Build, Compare and Synchronize = Database Change Management for SQL
Server

No comments:

Post a Comment