March 25, 2010 at 4:08 pm
Hi all. Today's issue is that I need to come up with a script that will loop thru the databases on a server and compare each database for objects that are different. I want to know what is on my staging server that is not on my production server or what objects are different on each server. I want to ultimately run it as a job, writing it off to a file that can be looked at on a regular basis.
Am I asking for the moon?? Has anyone already done this and can give me some good advice?? Thank you in advance....as this a PRI 1 on my plate right now! :w00t:
March 25, 2010 at 4:27 pm
Check out SQL Compare by RedGate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2010 at 4:33 pm
Angelindiego (3/25/2010)
Hi all. Today's issue is that I need to come up with a script that will loop thru the databases on a server and compare each database for objects that are different. I want to know what is on my staging server that is not on my production server or what objects are different on each server. I want to ultimately run it as a job, writing it off to a file that can be looked at on a regular basis.Am I asking for the moon?? Has anyone already done this and can give me some good advice?? Thank you in advance....as this a PRI 1 on my plate right now! :w00t:
Different how? Schema, data? You may be better off with one of the many database compare tools around.
March 25, 2010 at 5:05 pm
March 25, 2010 at 5:07 pm
sys.objects would be insufficient. You also need to compare sys.columns and sys.indexes. Then their are the Primary Keys and foreign keys that also need to be compared.
You could create a script to do it. It would be faster with the third party tools though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2010 at 5:35 pm
I'm pretty sure some of the Compare Tools have Command Lines utilities to enable automating/scheduling Compares.
March 25, 2010 at 5:48 pm
March 25, 2010 at 6:04 pm
Angelindiego (3/25/2010)
sorry all....I wasn't thinking earlier...the reason it has to be done by script and automated thru a job is that the servers being queried are in a place where we cannot install third party tools.....so script or nothing.....
Are you unable to query the server remotely? I.e. can you connect to the Server from your workstation via SSMS?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2010 at 6:11 pm
March 25, 2010 at 6:27 pm
Despite that kind of restriction, you could still use the redgate tools to perform the comparison. Redgate allows you to compare files for differences. Thus you would only need to script out the databases on each server and then compare them on your local system via redgate.
Otherwise, you will be building a script for quite some time.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply