Comparing changes between two SQL Server installations?

Hi all,
I've just inherited an unusual problem and I need some recommendations and
advice.
My employer has a SQL Server 2000 installation on which they run their whole
operation, with roughly 100 tables, a few dozen stored procedures and views,
and various user logins. Prior to his departure, our most recent DBA did a
good deal of new SQL Server development in our development database
environment. Unfortunately, it appears he did *everything* through the
Enterprise Manager GUI, leaving no SQL scripts of any of his changes. Now my
employer wants me to figure out (A) what changes he made, and (2) generate
SQL script for all of those changes beforehand. That way, we can make those
same changes in production.
Other than manually comparing every single table, view, stored procedure,
and login, is there any tool (built-in or 3rd party) that will allow me to
reconcile the objects between two wholly separate SQL Server 2000
installations?
Essentially, to put it another way, I need to "diff" the whole schema and
any stored procs and generate files to allow me to implement those changes
against production. And, ideally, I need scripts allowing me to rollback
changes as well.
If not, is there at least some sort of source-control tool for SQL Server we
could try to use in the future?
Thanks!
--Phil
Philip P. Obbard wrote:
> Hi all,
> I've just inherited an unusual problem and I need some
> recommendations and advice.
> My employer has a SQL Server 2000 installation on which they run
> their whole operation, with roughly 100 tables, a few dozen stored
> procedures and views, and various user logins. Prior to his
> departure, our most recent DBA did a good deal of new SQL Server
> development in our development database environment. Unfortunately,
> it appears he did *everything* through the Enterprise Manager GUI,
> leaving no SQL scripts of any of his changes. Now my employer wants
> me to figure out (A) what changes he made, and (2) generate SQL
> script for all of those changes beforehand. That way, we can make
> those same changes in production.
> Other than manually comparing every single table, view, stored
> procedure, and login, is there any tool (built-in or 3rd party) that
> will allow me to reconcile the objects between two wholly separate
> SQL Server 2000 installations?
> Essentially, to put it another way, I need to "diff" the whole schema
> and any stored procs and generate files to allow me to implement
> those changes against production. And, ideally, I need scripts
> allowing me to rollback changes as well.
> If not, is there at least some sort of source-control tool for SQL
> Server we could try to use in the future?
> Thanks!
> --Phil
There are a number of available products onthe market that perform
schema comparisons and produce the necessary DDL. We offer Speed Change
Manager from the Imceda web site. You can try it for 14-days.
David Gugick
Imceda Software
www.imceda.com
|||you should take a look at DB Ghost which can help you manage all your changes
to your schema and static data allowing you to store and use the sql
defintions of all objects and static data within your source control giving
you total automated change management with the auditability features of your
source control so this thing will never happen again and you can quickly
assertain exactly what has happened.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Philip P. Obbard" wrote:

> Hi all,
> I've just inherited an unusual problem and I need some recommendations and
> advice.
> My employer has a SQL Server 2000 installation on which they run their whole
> operation, with roughly 100 tables, a few dozen stored procedures and views,
> and various user logins. Prior to his departure, our most recent DBA did a
> good deal of new SQL Server development in our development database
> environment. Unfortunately, it appears he did *everything* through the
> Enterprise Manager GUI, leaving no SQL scripts of any of his changes. Now my
> employer wants me to figure out (A) what changes he made, and (2) generate
> SQL script for all of those changes beforehand. That way, we can make those
> same changes in production.
> Other than manually comparing every single table, view, stored procedure,
> and login, is there any tool (built-in or 3rd party) that will allow me to
> reconcile the objects between two wholly separate SQL Server 2000
> installations?
> Essentially, to put it another way, I need to "diff" the whole schema and
> any stored procs and generate files to allow me to implement those changes
> against production. And, ideally, I need scripts allowing me to rollback
> changes as well.
> If not, is there at least some sort of source-control tool for SQL Server we
> could try to use in the future?
> Thanks!
> --Phil
>
>