Avatar of Dhiraj Mutha
Dhiraj MuthaFlag for United States of America

asked on 

Vbscript or Powershell to Compare SQL Databases - Rowcout

HI All,

I need a help in writing a script to compare two databases. We have a SQL script which gives us the rowcount of each table in a database.

What we do is, run this query on both the databases and copy it to excel and compare them.

Hence need a VBscript or PowerShell script to compare it in one shot.

I shiuld be able to enter two db server name and database name and the script should compare based on the rowcount.
PowershellScripting LanguagesMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Dhiraj Mutha
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

PFA the sql query we use to get the row count of all the tables in a DB.
Compare-DB.txt
Avatar of arnold
arnold
Flag of United States of America image

You should look at getting a tool that will do just that.
http://dbcomparer.com/

A commercial version sql-compare from red-gate has feature including schema change report/modification script creation.
http://www.red-gate.com/products/sql-development/sql-compare/
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I am not looking for a third party solution. I am looking for a script, because we cannot have a third party installed in our production environment. Also we are planning to schedule this script for automation.
Avatar of arnold
arnold
Flag of United States of America image

Are you familiar with vbscript or powershell querying a database?
If so, you would have to chain a query from 1, using the data generate request to the other.
Row count is not a reliable indicator.
I am not looking for a third party solution
Then why even use VBScript or PowerShell?  I mean if you are that concerned than you should just consider using something like SQL Server's SQLCmd.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I am fine with that also. If it can be scheduled to run automatically. And vbscript and powershell are not third party solution. Thanks.
Avatar of arnold
arnold
Flag of United States of America image

Are you looking for a ready made script or for someone to create one for you?
The outline is fairly straight forward.
Obtain the list of tables from the reference database.
The go through getting the create directive for the tables on the reference database and comparing it to the other database.
This step will validate the database structures are identical.
The second step is to use a cursor that runs a select on data from the reference database tables, and then queries the other database table, for the identical record.
At any point a lack of response to the query should be recorded as a deviation.
Alternatively, use the suggestions made for the third party tool.

Backup the two databases. Restore them on a separate environment where the third party tools can be installed and run the comparison.
It should provide an acceptable verification even with some separation while eliminating any impact on the production environment s performance.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I want someone to create it for me.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I want someone to help me create this has I am not much aware of these scripting languages.
Avatar of arnold
arnold
Flag of United States of America image

How familiar are you with sql server. Querying master sys.state for databases. Then determining the tables within the database that you want to compare. Then get the structure of each table.
There are many examples for both vbscript and powershell dealing with querying sql.
I am still puzzled at why you are insisting on reinventing the wheel.
Red-gate has the application that provides what you are looking for as well as a "difference" dealing with what changes you need to apply to make DB1 identical to DB2 when differences are found.
I believe visual studio may also include tools that deal with schema comparison that deals with creating a package that will update/upgrade the schema when a structure DB changes from one version of an application to the next.

Restoring databases from backup on a separate system and using the two referenced tools to compare an older version of the DB should be an adequate compromise. Caveat deals with the backups should be within a reasonable time I.e within 30 minutes of each other to minimize the differences.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

Even I am not good at that also. This is my learning phaze on SQL.

To create some value for myself in the team I am looking for this script. :)
Avatar of arnold
arnold
Flag of United States of America image

Getting a script from a third party as complex as you are asking for is a rather dangerous thing.

IMHO, providing the existing and previously referenced example will show your team members that you have an understanding of a complexity of an issue and came up with an alternative.

I.e, your firm has a rather expensive vehicle that has an important component that failed. You are takes with coming up with an alternative to replace the component.  You can try taking the time to fabricate the component, or discovering the complexity and issues involved that prevent you from completing the task In a timely manner, purchasing a component from existing commercial vendors will be the wiser course of action!
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I dont want to compare the data, my only requirement is of the Row Count. These are Grocery DB's hence the data will always be different in those two DB's. But the Row Counts has to be same.

Hence need to compare the row count.

I have shared a excel macro sheet which does that but I need a VBScript or a Powershell to do the same.
Data-VAlidation.xls
Avatar of arnold
arnold
Flag of United States of America image

You take the same premise for vbscript, powershell
http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/46bb8994-0bb5-4e66-87fa-99c4b12d2bca/
This uses a DSN defined in ODBC tab.

Search for DSNless database connections if you do not want to define/setup a DSN.

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/e580a1e1-f116-4fc5-9344-bebf4489dc75/
This powershell script uses DSNless connection, but uses windows integrated security model to authenticate into ms sql.
ASKER CERTIFIED SOLUTION
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of arnold
arnold
Flag of United States of America image

There are many examples with powershell to excel output.

These excersizes will get you closer to where you want to go.
http://www.petri.co.il/export-to-excel-with-powershell.htm#

There are many other examples.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

Avatar of arnold
arnold
Flag of United States of America image

Opening a second question is not an answer.
There are many ways, programming approaches. You can use ssis, ssrs to aggregate data/queries.
Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag of United States of America image

ASKER

I am going to put this in VB.net post. As I feel this can be achieved in VB.Net.
Microsoft SQL Server 2008
Microsoft SQL Server 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.

50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo