Avatar of Dhiraj Mutha
Dhiraj Mutha
Flag 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

8/22/2022 - Mon
Dhiraj Mutha

ASKER
PFA the sql query we use to get the row count of all the tables in a DB.
Compare-DB.txt
arnold

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/
Dhiraj Mutha

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
arnold

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.
Anthony Perkins

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.
Dhiraj Mutha

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

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.
Dhiraj Mutha

ASKER
I want someone to create it for me.
Dhiraj Mutha

ASKER
I want someone to help me create this has I am not much aware of these scripting languages.
Your help has saved me hundreds of hours of internet surfing.
fblack61
arnold

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.
Dhiraj Mutha

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. :)
arnold

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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dhiraj Mutha

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
arnold

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
Dhiraj Mutha

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
arnold

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dhiraj Mutha

ASKER
arnold

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

ASKER
I am going to put this in VB.net post. As I feel this can be achieved in VB.Net.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.