Get-SqlEFMigrationHistory.ps1

<#
.SYNOPSIS
    Discovers EF Core migration history across multiple SQL Server instances.

.DESCRIPTION
    Scans every online user database on one or more SQL Server instances for the __EFMigrationsHistory
    table (created by Entity Framework Core), collects all migration records, and returns them as
    PowerShell objects. Answers "which EF migrations are applied where?" across dev, QA, UAT, and
    production environments.

    Optionally writes the consolidated results to a central DBA tracking table for historical
    reporting. Optionally compares migrations between two environments to identify drift (migrations
    present on one server but missing from another).

    Uses a cursor over sys.databases instead of sp_MSforeachdb for reliability (sp_MSforeachdb is
    undocumented and silently skips databases with special characters in their names).

.PREREQUISITES
    - dbatools module v2.0+ (Install-Module dbatools)
    - SQL Server 2016+ on target instances
    - Permissions: VIEW SERVER STATE and db_datareader on every target database
    - If using -DestinationInstance: db_datawriter on the destination database/table

.PARAMETER SqlInstance
    One or more SQL Server instance names to scan for EF migration history.
    Accepts pipeline input.

.PARAMETER SqlCredential
    Optional PSCredential for SQL Server authentication. If omitted, Windows authentication is used.

.PARAMETER ExcludeDatabase
    Database names to skip when scanning. System databases (master, model, msdb, tempdb) are
    always excluded automatically.

.PARAMETER DestinationInstance
    SQL Server instance to write consolidated results to. If omitted, results are returned to the
    pipeline only and nothing is persisted.

.PARAMETER DestinationCredential
    Optional PSCredential for the destination SQL Server instance. If omitted, Windows
    authentication is used. Ignored unless -DestinationInstance is specified.

.PARAMETER DestinationDatabase
    Database on the destination instance to write results to.
    Default: DBA

.PARAMETER DestinationTable
    Table name on the destination instance to write results to. The table is created automatically
    if it does not exist.
    Default: AllEfMigrationsHistory

.PARAMETER CompareSourceInstance
    When specified together with -CompareTargetInstance, compares migration records between the
    two environments and outputs migrations that exist on one but not the other.

.PARAMETER CompareTargetInstance
    The second instance for comparison. Required when -CompareSourceInstance is specified.

.USAGE NOTES
    - The script only reads from __EFMigrationsHistory tables; it does not modify any source databases.
    - If -DestinationInstance is specified, the destination table is truncated before each run so
      that results reflect the current state. The table is created idempotently if it does not exist.
    - Comparison mode (-CompareSourceInstance / -CompareTargetInstance) operates on already-collected
      results and does not require additional database connections beyond the initial scan.

.EXAMPLE
    .\Get-SqlEFMigrationHistory.ps1 -SqlInstance "DevSQL01","UATSQL01","ProdSQL01"
    Scans all three instances and returns EF migration records to the pipeline.

.EXAMPLE
    .\Get-SqlEFMigrationHistory.ps1 -SqlInstance "DevSQL01","ProdSQL01" -ExcludeDatabase "StagingDB","TestDB"
    Scans both instances, skipping the StagingDB and TestDB databases.

.EXAMPLE
    .\Get-SqlEFMigrationHistory.ps1 -SqlInstance "DevSQL01","ProdSQL01" -DestinationInstance "MonitorSQL01" -DestinationDatabase "DBA" -DestinationTable "AllEfMigrationsHistory"
    Scans and writes consolidated results to a central tracking table.

.EXAMPLE
    .\Get-SqlEFMigrationHistory.ps1 -SqlInstance "DevSQL01","ProdSQL01" -CompareSourceInstance "DevSQL01" -CompareTargetInstance "ProdSQL01"
    Scans both instances, returns all migration records, and outputs a comparison showing which
    migrations are on Dev but not Prod, and vice versa.

.EXAMPLE
    .\Get-SqlEFMigrationHistory.ps1 -SqlInstance "ProdSQL01" -SqlCredential (Get-Credential)
    Scans using SQL Server authentication.

.LICENSE
    MIT License - https://opensource.org/licenses/MIT

.LINK
    https://github.com/mbentham/sql-server-scripts
#>

[CmdletBinding()]
param (
    [Parameter(Mandatory, ValueFromPipeline, ValueFromPipelineByPropertyName)]
    [ValidateNotNullOrEmpty()]
    [string[]]$SqlInstance,

    [PSCredential]$SqlCredential,

    [string[]]$ExcludeDatabase,

    [string]$DestinationInstance,

    [PSCredential]$DestinationCredential,

    [ValidateNotNullOrEmpty()]
    [string]$DestinationDatabase = 'DBA',

    [ValidateNotNullOrEmpty()]
    [string]$DestinationTable = 'AllEfMigrationsHistory',

    [string]$CompareSourceInstance,

    [string]$CompareTargetInstance
)

begin {
    $ErrorActionPreference = 'Stop'

    # Validate comparison parameters together
    if ($CompareSourceInstance -and -not $CompareTargetInstance) {
        throw "When -CompareSourceInstance is specified, -CompareTargetInstance is required."
    }
    if ($CompareTargetInstance -and -not $CompareSourceInstance) {
        throw "When -CompareTargetInstance is specified, -CompareSourceInstance is required."
    }

    # Collect all results across pipeline input
    $allResults = [System.Collections.Generic.List[object]]::new()

    # Map user-supplied instance names to @@SERVERNAME values for reliable comparison
    $serverNameMap = @{}

    # System databases to always exclude
    $systemDatabases = @('master', 'model', 'msdb', 'tempdb')

    # -----------------------------------------------------------------
    # T-SQL: Enumerate online user databases (replaces sp_MSforeachdb)
    # -----------------------------------------------------------------
    $sqlGetDatabases = @"
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND database_id > 4
  AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY name;
"@

    # -----------------------------------------------------------------
    # T-SQL: Check for __EFMigrationsHistory and return its rows
    # Parameterised with the database name injected via string replacement
    # because USE [dbname] cannot be parameterised in T-SQL
    # -----------------------------------------------------------------
    $sqlGetMigrations = @"
USE [{0}];

IF EXISTS (
    SELECT 1
    FROM sys.tables
    WHERE name = '__EFMigrationsHistory'
      AND schema_id = SCHEMA_ID('dbo')
)
BEGIN
    SELECT
        @@SERVERNAME     AS ServerName,
        DB_NAME()        AS DatabaseName,
        MigrationId,
        ProductVersion
    FROM [__EFMigrationsHistory];
END
"@

    # -----------------------------------------------------------------
    # T-SQL: Create destination table idempotently
    # -----------------------------------------------------------------
    $sqlCreateDestinationTable = @"
DECLARE @dbName NVARCHAR(128) = @DbParam;
DECLARE @tblName NVARCHAR(128) = @TblParam;
DECLARE @fullName NVARCHAR(400) = QUOTENAME(@dbName) + N'.[dbo].' + QUOTENAME(@tblName);
DECLARE @sql NVARCHAR(MAX);

IF OBJECT_ID(@fullName, N'U') IS NULL
BEGIN
    SET @sql = N'CREATE TABLE ' + QUOTENAME(@dbName) + N'.[dbo].' + QUOTENAME(@tblName) + N' (
        ServerName     NVARCHAR(128)  NOT NULL,
        DatabaseName   NVARCHAR(128)  NOT NULL,
        MigrationId    NVARCHAR(150)  NOT NULL,
        ProductVersion NVARCHAR(32)   NOT NULL,
        CollectedAt    DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
    );';
    EXEC sp_executesql @sql;
END
"@
}

process {
    foreach ($instance in $SqlInstance) {
        Write-Verbose "Processing instance: $instance"

        $serverConn = $null
        try {
            # Build connection parameters
            $connectParams = @{ SqlInstance = $instance }
            if ($SqlCredential) { $connectParams['SqlCredential'] = $SqlCredential }

            $serverConn = Connect-DbaInstance @connectParams

            # Resolve @@SERVERNAME for reliable comparison matching
            $actualName = Invoke-DbaQuery -SqlInstance $serverConn -Database 'master' -Query 'SELECT @@SERVERNAME AS ActualName' -EnableException
            $serverNameMap[$instance] = $actualName.ActualName

            # Get list of online user databases
            Write-Verbose "[$instance] Enumerating online user databases..."
            $databases = Invoke-DbaQuery -SqlInstance $serverConn -Database 'master' -Query $sqlGetDatabases -EnableException

            if (-not $databases) {
                Write-Verbose "[$instance] No user databases found."
                continue
            }

            foreach ($db in $databases) {
                $dbName = $db.name

                # Apply user exclusion list
                if ($ExcludeDatabase -and $ExcludeDatabase -contains $dbName) {
                    Write-Verbose "[$instance] Skipping excluded database: $dbName"
                    continue
                }

                # Skip system databases (belt and braces -- the query already filters, but
                # this protects against edge cases)
                if ($systemDatabases -contains $dbName) {
                    continue
                }

                try {
                    $query = $sqlGetMigrations -f $dbName
                    $migrations = Invoke-DbaQuery -SqlInstance $serverConn -Database 'master' -Query $query -EnableException

                    if ($migrations) {
                        Write-Verbose "[$instance][$dbName] Found $($migrations.Count) migration(s)."
                        foreach ($row in $migrations) {
                            $allResults.Add([PSCustomObject]@{
                                ServerName     = $row.ServerName
                                DatabaseName   = $row.DatabaseName
                                MigrationId    = $row.MigrationId
                                ProductVersion = $row.ProductVersion
                            })
                        }
                    }
                }
                catch {
                    Write-Warning "[$instance][$dbName] Error reading migrations: $_"
                }
            }
        }
        catch {
            Write-Warning "[$instance] Error connecting or enumerating databases: $_"
        }
        finally {
            if ($serverConn) {
                $serverConn | Disconnect-DbaInstance -ErrorAction SilentlyContinue
            }
        }
    }
}

end {
    Write-Verbose "Total migration records collected: $($allResults.Count)"

    # -----------------------------------------------------------------
    # Persist to destination table (optional)
    # -----------------------------------------------------------------
    if ($DestinationInstance -and $allResults.Count -gt 0) {
        $destConn = $null
        try {
            $destConnectParams = @{ SqlInstance = $DestinationInstance }
            if ($DestinationCredential) { $destConnectParams['SqlCredential'] = $DestinationCredential }

            $destConn = Connect-DbaInstance @destConnectParams

            # Ensure the destination table exists
            $createTableParams = @(
                [Microsoft.Data.SqlClient.SqlParameter]::new('DbParam', $DestinationDatabase)
                [Microsoft.Data.SqlClient.SqlParameter]::new('TblParam', $DestinationTable)
            )
            Invoke-DbaQuery -SqlInstance $destConn -Database $DestinationDatabase -Query $sqlCreateDestinationTable -SqlParameters $createTableParams -EnableException

            # Write results (truncate first so the table reflects current state)
            Write-Verbose "Writing $($allResults.Count) record(s) to [$DestinationInstance].[$DestinationDatabase].[$DestinationTable]..."
            Write-DbaDbTableData -SqlInstance $destConn -Database $DestinationDatabase -Schema 'dbo' -Table $DestinationTable -InputObject $allResults -Truncate -EnableException

            Write-Verbose "Results persisted successfully."
        }
        catch {
            Write-Warning "Error writing to destination [$DestinationInstance].[$DestinationDatabase].[$DestinationTable]: $_"
        }
        finally {
            if ($destConn) {
                $destConn | Disconnect-DbaInstance -ErrorAction SilentlyContinue
            }
        }
    }
    elseif ($DestinationInstance -and $allResults.Count -eq 0) {
        Write-Verbose "No migration records found; skipping write to destination table."
    }

    # -----------------------------------------------------------------
    # Environment comparison (optional)
    # -----------------------------------------------------------------
    if ($CompareSourceInstance -and $CompareTargetInstance -and $allResults.Count -gt 0) {
        Write-Verbose "Comparing migrations between [$CompareSourceInstance] and [$CompareTargetInstance]..."

        # Resolve user-supplied instance names to @@SERVERNAME values
        $resolvedSource = $serverNameMap[$CompareSourceInstance]
        $resolvedTarget = $serverNameMap[$CompareTargetInstance]

        if (-not $resolvedSource) {
            Write-Warning "Cannot compare: '$CompareSourceInstance' was not included in -SqlInstance and has no resolved server name."
        }
        elseif (-not $resolvedTarget) {
            Write-Warning "Cannot compare: '$CompareTargetInstance' was not included in -SqlInstance and has no resolved server name."
        }
        else {
            $sourceRecords = $allResults | Where-Object { $_.ServerName -eq $resolvedSource }
            $targetRecords = $allResults | Where-Object { $_.ServerName -eq $resolvedTarget }

            # Build lookup keys: DatabaseName + MigrationId
            $sourceKeys = $sourceRecords | ForEach-Object { "$($_.DatabaseName)|$($_.MigrationId)" }
            $targetKeys = $targetRecords | ForEach-Object { "$($_.DatabaseName)|$($_.MigrationId)" }

            $onlyOnSource = $sourceRecords | Where-Object {
                $key = "$($_.DatabaseName)|$($_.MigrationId)"
                $key -notin $targetKeys
            } | Select-Object @{N='Comparison';E={"On [$CompareSourceInstance] only"}}, DatabaseName, MigrationId, ProductVersion

            $onlyOnTarget = $targetRecords | Where-Object {
                $key = "$($_.DatabaseName)|$($_.MigrationId)"
                $key -notin $sourceKeys
            } | Select-Object @{N='Comparison';E={"On [$CompareTargetInstance] only"}}, DatabaseName, MigrationId, ProductVersion

            $comparisonResults = @($onlyOnSource) + @($onlyOnTarget)

            if ($comparisonResults.Count -gt 0) {
                Write-Warning "Migration drift detected: $($comparisonResults.Count) difference(s) between [$CompareSourceInstance] and [$CompareTargetInstance]."
                Write-Host "`n--- Migration Comparison ---"
                $comparisonResults | Format-Table -AutoSize | Out-String | Write-Host
            }
            else {
                Write-Verbose "No migration drift detected between [$CompareSourceInstance] and [$CompareTargetInstance]."
            }
        }
    }

    # Return all results to the pipeline
    $allResults
}