Use a script to compare a large number of databases on 2 instances

cory.blythe

I have done a few searches and find some old references to using the command line, batch files, and xml but nothing really seems to fit what I want to do.
I have 2 instances on which we believe some of the Stored Procedures and functions have gotten out of sync.  We would like to be able to compare all these against the 2 instances without having to go through the GUI for each DB (over 200).
I was hoping there would be a way to pass in a file with a list of instance/database pairs db1 and db2 and output all the Stored Procedures and Functions with differences for that pair into a report.

I'd be happy if someone could just point me at the documentation if this is possible, I fear my ability to choose the right search terms is hampering my efforts.

Jon_Kirkwood

Hi @cory.blythe

 

Thank you for reaching out on the Redgate forums regarding your SQL Compare query.

 

It's very likely this is possible, using PowerShell to create a script to loop through all the dB's

In my example, you would use SQL Compare GUI to create a filter file that only compares Stored Procedures & Functions. 

  

Start a new project in SQL Compare for any database, once the project is loaded - set the filters in the left-hand menu to only show Stored Procedures & Functions

Save the file to a location to be accessed later.

 

 

 Then using the SQL Compare command line, work out the statement you need to run - passing in the /db1 /db2 /filter /report switches as a possible minimum

https://documentation.red-gate.com/sc/using-the-command-line

 

 I have a rough outline of a script that would achieve this.

Assuming that the db.csv file contains a comma-separated list of values of your database pairs 

 

db1,db2
database1, database2
database3, database4

 

Then an example script to loop through the list & output reports to a folder:

 

#Define the path to SQLCompare.exe, the filter file, and the CSV file
$SQLComparePath = "C:\Path\To\SQLCompare.exe"
$FilterFile = "C:\Path\To\filter.scpf"
$CsvFile = "C:\Path\To\db.csv"
$ReportFolder = "C:\Path\To\Reports"

#Create the report folder if it does not exist
if (-Not (Test-Path -Path $ReportFolder)) {
New-Item -ItemType Directory -Path $ReportFolder
}

Import the CSV file
$dbPairs = Import-Csv -Path $CsvFile

#Loop through each row in the CSV and run SQLCompare.exe for each db1/db2 pair
foreach ($pair in $dbPairs) {
    $db1 = $pair.db1
    $db2 = $pair.db2

    # Generate a unique filename for the report
    $timestamp = Get-Date -Format "yyyyMMddHHmmss"
    $reportFile = "$ReportFolder\\Report_${db1}_vs_${db2}_$timestamp.html"
 
    # Construct the SQLCompare command
    $command = "$SQLComparePath /db1:$db1 /db2:$db2 /filter:$FilterFile /Report:$reportFile /reportType:Html /synchronize"

    # Output the command to console for verification (optional)
    Write-Output $command

    # Execute the SQLCompare command
    & $SQLComparePath /db1:$db1 /db2:$db2 /filter:$FilterFile /Report:$reportFile /reportType:Html /synchronize
}

 

Hopefully this helps you on the path to achieving your goal to compare multiple databases automatically