Automating SQL Server Stored Procedure Execution Across Multiple Databases with PowerShell

 In many enterprise environments, database administrators (DBAs) often need to execute scripts across multiple databases on several SQL Server instances. Doing this manually can be time-consuming and error-prone, especially when managing a large number of servers. Automating this task using PowerShell can significantly streamline the process, ensuring consistency and saving valuable time.

In this post, we'll walk through a PowerShell script that automates the execution of a stored procedure (sp_read) across all databases on multiple SQL Server instances. The script also captures the execution output and logs the status (success or failure) for each database in a detailed log file.

# Define a list of SQL Server instances

$servers = @("Server1", "Server2", "Server3")  # Add your server names here

# Define the output log file path

$logFile = "C:\data\ajay\LogFile.txt"  # change the path as per you're requirement 

# SQL query to execute the stored procedure on each database

$sqlQuery = @"

USE [?];

EXEC sp_read;  -- Executes the stored procedure 

"@

# Initialize the log file

Write-Output "Execution started at $(Get-Date)" | Out-File $logFile

# Loop through each server

foreach ($server in $servers) {

    try {

        # Connect to the SQL Server instance

        $connectionString = "Server=$server;Database=master;Integrated Security=True;"

        

        # Get the list of databases excluding system databases

        $query = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'model', 'msdb', 'tempdb')"

        $databases = Invoke-Sqlcmd -ConnectionString $connectionString -Query $query

        foreach ($db in $databases) {

            $dbName = $db.name

            # Replace the placeholder [?] with the actual database name

            $finalQuery = $sqlQuery.Replace("[?]", "[$dbName]")

            try {

                # Execute the query

                $result = Invoke-Sqlcmd -ConnectionString $connectionString -Query $finalQuery -ErrorAction Stop

                Write-Host "Successfully executed on $server - $dbName"

                # Log the success and output

                $logEntry = "[$(Get-Date)] SUCCESS: Executed on $server - $dbName`nOutput:`n$result"

                $logEntry | Out-File -Append $logFile

            }

            catch {

                # Log the failure

                $logEntry = "[$(Get-Date)] ERROR: Failed to execute on $server - $dbName. Error: $_"

                Write-Host $logEntry -ForegroundColor Red

                $logEntry | Out-File -Append $logFile

            }

        }

    }

    catch {

        # Log the failure for the server connection

        $logEntry = "[$(Get-Date)] ERROR: Failed to connect to $server. Error: $_"

        Write-Host $logEntry -ForegroundColor Red

        $logEntry | Out-File -Append $logFile

    }

}

# Final log entry

Write-Output "Execution completed at $(Get-Date)" | Out-File -Append $logFile