Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Compare two databases


Compare two databases

Author
Message
Khalidhussain
Khalidhussain
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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



ThomasRushton
ThomasRushton
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1852 Visits: 2217
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
joachim.verhagen
joachim.verhagen
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 190
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



wmp789
wmp789
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 1
We use Embarcadero Change Manager. Great tool!



rregan
rregan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
Here's a nice freeware tool:

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



G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367
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/
cneuhold
cneuhold
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 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.



Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: Moderators
Points: 7253 Visits: 2679
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
Andy DBA
Andy DBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 753
I've found Viktor Gorodnichenko's sp_CompareDB stored procedure to be very helpful.

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



Russ Bell
Russ Bell
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: 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.



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