Compare two databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Compare two databases

28 posts, Page 1 of 3

1 2 3 »»»

Compare two databases

Rate Topic

Display Mode

Topic Options

Author

Message

Khalidhussain

Posted Wednesday, September 24, 2003 3:59 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, November 11, 2004 2:47 AM
Points: 24, Visits: 1

I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?
I want to compare following:
Table name in Development and Test databases
Table structure in Development and Test databases (including identity seed and identity increment in each table)
View name in Development and Test databases
View structure in Development and Test databases
Stored procedure name in Development and Test databases
Stored procedure structure in Development and Test databases
We have ‘system data’ tables they hold data, which we need in our VB6 system.
These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.
I need to compare those system data in Development and Test databases as well.
Thank you

Post #16600

ThomasRushton

Posted Wednesday, September 24, 2003 6:37 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 6:30 AM
Points: 966, Visits: 372

quote:

I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?

We use AdeptSQL's SQLDiff tool for the database structures, and it's not overly expensive. They also do a tool to compare data within tables.
http://www.adeptsql.com

Post #80684

joachim.verhagen

Posted Wednesday, September 24, 2003 7:21 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 7:26 AM
Points: 224, Visits: 118

You can write a script that makes select statements from the information_schema views and do a windiff between the results.

Something like:
select * from information_schema.columns order by table_schema,table_name, column_name
select * from information_schema.views order by table_schema, table_name
select routine_schema, routine_name, routine_definition from information_schema.routines order by routine_schema,routine_name

Post #80685

wmp789

Posted Wednesday, September 24, 2003 4:36 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 06, 2006 5:14 PM
Points: 468, Visits: 1

We use Embarcadero Change Manager. Great tool!

Post #80686

rregan

Posted Wednesday, September 24, 2003 10:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 02, 2004 12:00 AM
Points: 2, Visits: 1

Here's a nice freeware tool:

http://www.davidemauri.it/dabcos/default.aspx

Post #80687

G.R.Prithiviraj Kulasingham

Posted Thursday, September 25, 2003 4:04 AM

SSC-Addicted

Group: General Forum Members
Last Login: Sunday, October 21, 2012 8:59 PM
Points: 421, Visits: 362

I too faced your situation and finally wrote the scripts myself.
I'll be happy if my scripts are usefull
Please check

http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771

There are many wonderfull tools available in the market. But for my need, any where I can control, scripts are the best.

Cheers,

Preethiviraj Kulasingham

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/

Post #80688

cneuhold

Posted Thursday, September 25, 2003 4:19 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 20, 2011 8:50 AM
Points: 245, Visits: 11

hi!

concerning own scripts, you could maybe use the following as a starting point:

http://www.sqlservercentral.com/scripts/contributions/246.asp

best regards,
chris.

Post #80689

Andy Warren

Posted Thursday, September 25, 2003 4:58 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Two other products in the space that I know of, one from Lockwood, one from Red Gate (along with Adept, all three advertise with us!).

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #80690

Andy DBA

Posted Thursday, September 25, 2003 7:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:16 AM
Points: 137, Visits: 441

I've found Viktor Gorodnichenko's sp_CompareDB stored procedure to be very helpful.

http://www.sql-server-performance.com/viktor_gorodnichenko.asp

Post #80691

Russ Bell

Posted Thursday, September 25, 2003 8:27 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 12, 2004 1:47 PM
Points: 14, Visits: 1

We had had great success using Red-Gate comparison tools.

http://www.red-gate.com/sql/summary.htm

Very affordable and can product a script to syncronize in either direction.

Post #80692

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions