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 123»»»

Compare two databases Expand / Collapse
Author
Message
Posted Wednesday, September 24, 2003 3:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, September 24, 2003 6:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 4, 2014 5:29 AM
Points: 967, Visits: 396
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
Posted Wednesday, September 24, 2003 7:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 31, 2014 6:13 AM
Points: 224, Visits: 135
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
Posted Wednesday, September 24, 2003 4:36 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 6, 2006 5:14 PM
Points: 468, Visits: 1
We use Embarcadero Change Manager. Great tool!




Post #80686
Posted Wednesday, September 24, 2003 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 2, 2004 12:00 AM
Points: 2, Visits: 1
Here's a nice freeware tool:

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



Post #80687
Posted Thursday, September 25, 2003 4:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363
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
Posted Thursday, September 25, 2003 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 5, 2013 1:19 AM
Points: 245, Visits: 13
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
Posted Thursday, September 25, 2003 4:58 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 8:46 AM
Points: 6,783, Visits: 1,881
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #80690
Posted Thursday, September 25, 2003 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:32 AM
Points: 147, Visits: 536
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
Posted Thursday, September 25, 2003 8:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase 123»»»

Permissions Expand / Collapse