result.md

Okay, this is a task best suited for a PowerShell script that calls a T-SQL query. Here's a solution that combines both:

PowerShell Script (Find-OrphanedDbFiles.ps1)

[CmdletBinding()]
param(
    [Parameter(Mandatory = $true, HelpMessage = "SQL Server instance name (e.g., 'SERVERNAME\SQLEXPRESS' or 'localhost').")]
    [string]$SqlServerInstance,

    [Parameter(Mandatory = $true, HelpMessage = "Array of folder paths to scan for MDF/LDF files.")]
    [string[]]$FolderPaths,

    [Parameter(HelpMessage = "Recurse into subdirectories of the specified folder paths.")]
    [switch]$Recurse,

    [Parameter(HelpMessage = "Optional: SQL Server credential to use if Windows Authentication is not sufficient.")]
    [System.Management.Automation.PSCredential]$Credential
)

#Requires -Modules SqlServer # Ensure the SqlServer module is available

# --- Configuration ---
$ErrorActionPreference = "Stop" # Exit on error

# --- Helper Functions ---
function Test-FolderExists($Path) {
    if (-not (Test-Path -Path $Path -PathType Container)) {
        Write-Warning "Folder not found: $Path. Skipping."
        return $false
    }
    return $true
}

# --- Main Logic ---
try {
    Write-Host "Step 1: Querying SQL Server '$SqlServerInstance' for currently used database files..." -ForegroundColor Cyan

    $sqlQuery = @"
SELECT
    LOWER(mf.physical_name) AS PhysicalPath
FROM
    sys.master_files mf
WHERE
    mf.type_desc IN ('ROWS', 'LOG'); -- ROWS = MDF/NDF, LOG = LDF
"@

    $invokeSqlCmdParams = @{
        ServerInstance = $SqlServerInstance
        Query          = $sqlQuery
        ErrorAction    = 'Stop'
    }
    if ($PSBoundParameters.ContainsKey('Credential')) {
        $invokeSqlCmdParams.Credential = $Credential
    }

    $usedDbFilesRaw = Invoke-Sqlcmd @invokeSqlCmdParams
    
    # Create a HashSet for efficient lookup, normalizing paths to lowercase
    $usedDbFilePaths = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)
    if ($usedDbFilesRaw) {
        foreach ($file in $usedDbFilesRaw) {
            [void]$usedDbFilePaths.Add($file.PhysicalPath)
        }
    }
    Write-Host "Found $($usedDbFilePaths.Count) database files currently in use by SQL Server." -ForegroundColor Green
    Write-Host ""


    Write-Host "Step 2: Scanning specified folder locations for *.mdf and *.ldf files..." -ForegroundColor Cyan
    $potentialOrphanFiles = [System.Collections.Generic.List[string]]::new()

    foreach ($folderPath in $FolderPaths) {
        $normalizedFolderPath = (Resolve-Path -Path $folderPath).Path # Get a clean, absolute path
        if (-not (Test-FolderExists -Path $normalizedFolderPath)) {
            continue
        }

        Write-Host "Scanning folder: $normalizedFolderPath"
        $gciParams = @{
            Path        = $normalizedFolderPath
            Include     = "*.mdf", "*.ldf"
            File        = $true # Only files, not directories
            ErrorAction = 'SilentlyContinue' # In case of permission issues on subfolders when not recursing
        }
        if ($Recurse) {
            $gciParams.Recurse = $true
        }

        $filesInFolder = Get-ChildItem @gciParams
        
        if ($filesInFolder) {
            foreach ($file in $filesInFolder) {
                # Normalize path to lowercase for comparison
                $potentialOrphanFiles.Add($file.FullName.ToLower())
            }
        }
    }
    
    # Remove duplicates if folders overlapped or if Recurse found same file multiple times (unlikely with distinct folders)
    $distinctPotentialOrphanFiles = $potentialOrphanFiles | Select-Object -Unique
    Write-Host "Found $($distinctPotentialOrphanFiles.Count) *.mdf/*.ldf files in the specified folder(s)." -ForegroundColor Green
    Write-Host ""


    Write-Host "Step 3: Identifying orphaned files..." -ForegroundColor Cyan
    $orphanedFiles = [System.Collections.Generic.List[string]]::new()

    foreach ($diskFilePath in $distinctPotentialOrphanFiles) {
        if (-not $usedDbFilePaths.Contains($diskFilePath)) {
            $orphanedFiles.Add($diskFilePath)
        }
    }

    if ($orphanedFiles.Count -gt 0) {
        Write-Host "Orphaned *.mdf and *.ldf files found:" -ForegroundColor Yellow
        $orphanedFiles | ForEach-Object { Write-Host "- $_" }
    } else {
        Write-Host "No orphaned *.mdf or *.ldf files found in the specified locations for instance '$SqlServerInstance'." -ForegroundColor Green
    }

}
catch {
    Write-Error "An error occurred: $($_.Exception.Message)"
    if ($_.Exception.InnerException) {
        Write-Error "Inner Exception: $($_.Exception.InnerException.Message)"
    }
    # Exit with a non-zero code to indicate failure
    exit 1
}

Write-Host "`nScript finished." -ForegroundColor Cyan

How to Run from Shell (PowerShell Console):

1. Save the Script: Save the code above as Find-OrphanedDbFiles.ps1 in a directory (e.g., C:\Scripts).

2. Install SqlServer Module (if not already installed):

   Install-Module SqlServer -Scope CurrentUser -Force
   Import-Module SqlServer

3. Open PowerShell Console: Navigate to the directory where you saved the script or use the full path.

4. Execute the Script:

   • Using Windows Authentication (most common):

     .\Find-OrphanedDbFiles.ps1 -SqlServerInstance "YOUR_SERVER\YOUR_INSTANCE" -FolderPaths "C:\SQLData\MSSQL\Data", "D:\SQLBackups\OldData"

     (Replace "YOUR_SERVER\YOUR_INSTANCE" with your actual SQL Server instance name, and the paths with your target folders.)

   • With recursion for subfolders:

     .\Find-OrphanedDbFiles.ps1 -SqlServerInstance "localhost" -FolderPaths "C:\Program Files\Microsoft SQL Server" -Recurse

   • Using SQL Authentication (if needed):

     $cred = Get-Credential # Prompts for username and password
     .\Find-OrphanedDbFiles.ps1 -SqlServerInstance "YOUR_SERVER\YOUR_INSTANCE" -FolderPaths "C:\SQLData" -Credential $cred

Explanation:

1. Parameters:

   • $SqlServerInstance: The name of the SQL Server instance (e.g., SQLEXPRESS, MSSQLSERVER, SERVERNAME\INSTANCE_NAME).
   • $FolderPaths: An array of strings, where each string is a path to a folder to check.
   • $Recurse: A switch parameter. If present (-Recurse), the script will look in subdirectories of the specified $FolderPaths.
   • $Credential: Optional. If you need to connect to SQL Server using SQL Authentication.

2. #Requires -Modules SqlServer: This line helps ensure the necessary PowerShell module is available. If not, it provides a clear error.

3. Step 1: Query SQL Server (Invoke-Sqlcmd)

   • It connects to the specified SQL Server instance.
   • Executes a T-SQL query against the sys.master_files system view.
   • sys.master_files contains information about all files (data and log) for all databases on the instance.
   • LOWER(mf.physical_name): Retrieves the full path of each database file and converts it to lowercase for case-insensitive comparison later.
   • The results are stored in a HashSet[string] called $usedDbFilePaths. A HashSet provides very fast lookups (.Contains() method), which is efficient when comparing against many files found on disk. StringComparer.OrdinalIgnoreCase ensures case-insensitive comparison.

4. Step 2: Scan Folders (Get-ChildItem)

   • It iterates through each path provided in $FolderPaths.
   • Test-FolderExists: A small helper function to check if a folder actually exists before trying to scan it.
   • Resolve-Path: Ensures the folder path is absolute and clean.
   • Get-ChildItem:
     • -Path: The folder to scan.
     • -Include "*.mdf", "*.ldf": Filters for files with these extensions.
     • -File: Ensures only files (not directories) are returned.
     • -Recurse (optional): If the -Recurse switch was used when calling the script.
   • The full paths of all found .mdf and .ldf files are converted to lowercase and added to $potentialOrphanFiles.
   • Select-Object -Unique: Ensures we don't process the same file path multiple times if, for example, folder paths overlap.

5. Step 3: Identify Orphans

   • It iterates through each file path found on the disk ($distinctPotentialOrphanFiles).
   • For each disk file, it checks if its path exists in the $usedDbFilePaths HashSet (the files known to SQL Server).
   • If a file from the disk is not found in the SQL Server's list, it's considered an orphan and added to the $orphanedFiles list.

6. Output:

   • The script then prints the list of orphaned files or a message indicating that none were found.

7. Error Handling:

   • $ErrorActionPreference = "Stop" makes the script halt on most errors.
   • The try...catch block catches any terminating errors, prints a message, and exits with a non-zero status code if an error occurs (useful for automation).

This script provides a comprehensive and robust way to achieve your goal from a single PowerShell command.