Backup-AllDatabases.ps1

# SQL Server Database Backup Script - Auto-detects SQL Instance
# Works on any server without modification
# Auto-elevates permissions if needed (requires Domain Admin rights)

param(
    [string]$BackupPath = "C:\SQLBackups",  
    [bool]$IncludeSystemDatabases = $false,
    [bool]$UseCompression = $true,
    [bool]$UseWindowsAuth = $true,  # Set to false for SQL Auth
    [string]$Username = "",  # Only used if UseWindowsAuth = false
    [string]$Password = "",  # Only used if UseWindowsAuth = false
    [bool]$SkipPermissionErrors = $true,  # Continue even if some DBs fail due to permissions
    [bool]$AutoElevate = $false  # Set to true to enable automatic elevation
)

# Configuration Class
class Config {
    static [string]$LogFileName = "backup_log.csv"
    static [string]$DateFormat = "yyyy-MM-dd_HHmmss"
    static [int]$QueryTimeout = 0  # 0 = unlimited
    static [string[]]$SystemDatabases = @('master', 'model', 'msdb', 'tempdb')
    static [int]$ServiceTimeout = 60  # Seconds to wait for service operations
}

# SQL Permission Elevator Class
class SQLPermissionElevator {
    static [bool] CheckSysAdminAccess([hashtable]$connParams) {
        try {
            $query = "SELECT IS_SRVROLEMEMBER('sysadmin') as IsSysAdmin"
            $result = Invoke-Sqlcmd @connParams -Query $query -ErrorAction Stop
            return $result.IsSysAdmin -eq 1
        } catch {
            return $false
        }
    }
    
    static [void] DiagnoseSysAdminStatus([hashtable]$connParams) {
        Write-Host "`n[DIAGNOSTIC] Checking current login and sysadmin status..." -ForegroundColor Cyan
        
        try {
            # Check current login and role membership
            $query1 = @"
SELECT 
    SUSER_SNAME() as CurrentLogin,
    IS_SRVROLEMEMBER('sysadmin') as IsSysAdmin,
    IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME()) as IsSysAdminExplicit
"@
            
            $result1 = Invoke-Sqlcmd @connParams -Query $query1 -ErrorAction Stop
            Write-Host "[DIAGNOSTIC] Current Login: $($result1.CurrentLogin)" -ForegroundColor Cyan
            Write-Host "[DIAGNOSTIC] IS_SRVROLEMEMBER('sysadmin'): $($result1.IsSysAdmin)" -ForegroundColor Cyan
            Write-Host "[DIAGNOSTIC] IS_SRVROLEMEMBER('sysadmin', SUSER_SNAME()): $($result1.IsSysAdminExplicit)" -ForegroundColor Cyan
            
            # Check all sysadmin members
            $query2 = @"
SELECT 
    r.name AS role_name,
    m.name AS member_name,
    m.type_desc AS member_type
FROM sys.server_role_members rm 
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id 
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id 
WHERE r.name = 'sysadmin'
ORDER BY m.name
"@
            
            $result2 = Invoke-Sqlcmd @connParams -Query $query2 -ErrorAction Stop
            Write-Host "`n[DIAGNOSTIC] All sysadmin members:" -ForegroundColor Cyan
            $result2 | ForEach-Object {
                Write-Host "[DIAGNOSTIC]   - $($_.member_name) ($($_.member_type))" -ForegroundColor Cyan
            }
            
        } catch {
            Write-Host "[DIAGNOSTIC] Error running diagnostic queries: $_" -ForegroundColor Red
        }
    }
    
    static [bool] ElevatePermissions([string]$instance, [string]$currentUser) {
        Write-Host "`n" ("=" * 60) -ForegroundColor Yellow
        Write-Host "PERMISSION ELEVATION REQUIRED" -ForegroundColor Yellow
        Write-Host ("=" * 60) -ForegroundColor Yellow
        Write-Host "This will restart SQL Server to grant sysadmin permissions." -ForegroundColor Yellow
        Write-Host "The process takes about 30-60 seconds." -ForegroundColor Yellow
        
        # Ask for confirmation
        $confirm = Read-Host "`nDo you want to proceed with automatic elevation? (Y/N)"
        if ($confirm -ne 'Y') {
            Write-Host "Permission elevation cancelled by user." -ForegroundColor Yellow
            return $false
        }
        
        # Get SQL Server service name
        $serviceName = if ($instance -match "\\(.+)$") {
            "MSSQL`$$($Matches[1])"
        } else {
            "MSSQLSERVER"
        }
        
        Write-Host "`nSQL Service detected: $serviceName" -ForegroundColor Cyan
        
        # Verify service exists
        $service = Get-Service -Name $serviceName -ErrorAction SilentlyContinue
        if (!$service) {
            Write-Host "ERROR: SQL Server service '$serviceName' not found!" -ForegroundColor Red
            return $false
        }
        
        # Find sqlcmd.exe
        $sqlcmdPath = $null
        $possiblePaths = @(
            "${env:ProgramFiles}\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\sqlcmd.exe",
            "${env:ProgramFiles}\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\sqlcmd.exe",
            "${env:ProgramFiles}\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd.exe",
            "${env:ProgramFiles}\Microsoft SQL Server\*\Tools\Binn\sqlcmd.exe"
        )
        
        foreach ($path in $possiblePaths) {
            $found = Get-Item -Path $path -ErrorAction SilentlyContinue | Select-Object -First 1
            if ($found) {
                $sqlcmdPath = $found.FullName
                break
            }
        }
        
        # Try to find in PATH
        if (!$sqlcmdPath) {
            $sqlcmdPath = (Get-Command sqlcmd -ErrorAction SilentlyContinue).Path
        }
        
        if (!$sqlcmdPath) {
            Write-Host "ERROR: sqlcmd.exe not found! Please ensure SQL Server client tools are installed." -ForegroundColor Red
            return $false
        }
        
        Write-Host "Found sqlcmd at: $sqlcmdPath" -ForegroundColor Green
        
        # Store original state for recovery
        $elevationSuccess = $false
        
        try {
            # Stop SQL Server with timeout
            if ($service.Status -eq 'Running') {
                Write-Host "`nStopping SQL Server service..." -ForegroundColor Yellow
                Stop-Service $serviceName -Force -ErrorAction Stop
                
                # Wait for service to stop with timeout
                $stopTimeout = [DateTime]::Now.AddSeconds([Config]::ServiceTimeout)
                while ((Get-Service $serviceName).Status -ne 'Stopped' -and [DateTime]::Now -lt $stopTimeout) {
                    Start-Sleep -Seconds 1
                }
                
                if ((Get-Service $serviceName).Status -ne 'Stopped') {
                    throw "Service failed to stop within timeout period"
                }
                Write-Host "✓ SQL Server stopped" -ForegroundColor Green
            }
            
            # Start SQL Server in single-user mode - FIXED APPROACH
            Write-Host "Starting SQL Server in single-user mode..." -ForegroundColor Yellow
            
            # Use net start with /m parameter for single user mode - matching working approach
            $netStartCmd = if ($serviceName -eq "MSSQLSERVER") {
                "net start MSSQLSERVER /m"
            } else {
                "net start `"$serviceName`" /m"
            }
            
            $process = Start-Process -FilePath "cmd.exe" -ArgumentList "/c $netStartCmd" -Wait -PassThru -NoNewWindow
            
            if ($process.ExitCode -ne 0) {
                throw "Failed to start SQL Server in single-user mode"
            }
            
            Start-Sleep -Seconds 3
            Write-Host "✓ SQL Server started in single-user mode" -ForegroundColor Green
            
            # FIXED: Use direct sqlcmd approach that worked manually
            Write-Host "Granting sysadmin role to: $currentUser" -ForegroundColor Yellow
            
            # Create interactive sqlcmd session to match working approach
            $elevateCommands = @"
ALTER SERVER ROLE [sysadmin] ADD MEMBER [$currentUser]
GO
SELECT IS_SRVROLEMEMBER('sysadmin', '$currentUser') AS VerifySuccess
GO
exit
"@
            
            # Write commands to temp file
            $tempCommandFile = Join-Path $env:TEMP "elevate_commands.sql"
            $elevateCommands | Out-File -FilePath $tempCommandFile -Encoding ASCII -Force
            
            # Execute using sqlcmd with input file - matching successful approach
            $sqlcmdArgs = @(
                "-S", "localhost",  # Use localhost like manual approach
                "-E",               # Windows Authentication
                "-i", $tempCommandFile  # Input file
            )
            
            Write-Host "Executing: sqlcmd -S localhost -E -i $tempCommandFile" -ForegroundColor Cyan
            
            $process = Start-Process -FilePath $sqlcmdPath -ArgumentList $sqlcmdArgs -Wait -PassThru -NoNewWindow -RedirectStandardOutput "$env:TEMP\sqlcmd_out.txt" -RedirectStandardError "$env:TEMP\sqlcmd_err.txt"
            
            if ($process.ExitCode -ne 0) {
                $errorContent = Get-Content "$env:TEMP\sqlcmd_err.txt" -ErrorAction SilentlyContinue
                throw "Failed to grant permissions. Error: $errorContent"
            }
            
            # Check the output for verification
            $outputContent = Get-Content "$env:TEMP\sqlcmd_out.txt" -ErrorAction SilentlyContinue
            Write-Host "SQL Output:" -ForegroundColor Green
            $outputContent | ForEach-Object { Write-Host "  $_" -ForegroundColor Green }
            
            # Look for success indicator in output (should show 1 for VerifySuccess)
            if ($outputContent -match "1\s*$") {
                Write-Host "✓ Role assignment verified successful" -ForegroundColor Green
                $elevationSuccess = $true
            } else {
                Write-Host "✗ Role assignment verification failed" -ForegroundColor Red
                $elevationSuccess = $false
            }
            
        } catch {
            Write-Host "✗ Error during elevation: $_" -ForegroundColor Red
            $elevationSuccess = $false
        } finally {
            # ALWAYS restore normal operation
            Write-Host "`nRestoring SQL Server to normal mode..." -ForegroundColor Yellow
            
            try {
                # Stop SQL Server
                Stop-Service $serviceName -Force -ErrorAction SilentlyContinue
                
                # Wait for stop
                $stopTimeout = [DateTime]::Now.AddSeconds(30)
                while ((Get-Service $serviceName).Status -ne 'Stopped' -and [DateTime]::Now -lt $stopTimeout) {
                    Start-Sleep -Seconds 1
                }
                
                # Start normally
                Start-Service $serviceName -ErrorAction Stop
                
                # Wait for start
                $startTimeout = [DateTime]::Now.AddSeconds(30)
                while ((Get-Service $serviceName).Status -ne 'Running' -and [DateTime]::Now -lt $startTimeout) {
                    Start-Sleep -Seconds 1
                }
                
                if ((Get-Service $serviceName).Status -eq 'Running') {
                    Write-Host "✓ SQL Server restored to normal operation" -ForegroundColor Green
                } else {
                    Write-Host "WARNING: SQL Server may not have started properly. Please check the service." -ForegroundColor Yellow
                }
                
            } catch {
                Write-Host "ERROR: Failed to restore SQL Server to normal mode!" -ForegroundColor Red
                Write-Host "Please manually restart the SQL Server service: $serviceName" -ForegroundColor Red
                Write-Host "Error: $_" -ForegroundColor Red
            }
            
            # Clean up temp files
            try {
                Get-ChildItem "$env:TEMP\sqlcmd_*.txt" -ErrorAction SilentlyContinue | Remove-Item -Force -ErrorAction SilentlyContinue
                if (Test-Path "$env:TEMP\elevate_commands.sql") {
                    Remove-Item "$env:TEMP\elevate_commands.sql" -Force -ErrorAction SilentlyContinue
                }
            } catch {
                # Ignore cleanup errors
            }
        }
        
        if ($elevationSuccess) {
            Write-Host "`n✓ Permission elevation completed and verified successfully!" -ForegroundColor Green
            Write-Host "Your account now has permanent sysadmin access." -ForegroundColor Green
        }
        
        return $elevationSuccess
    }
}

# SQL Instance Detection Class
class SQLInstanceDetector {
    static [string[]] GetInstances() {
        Write-Host "Detecting SQL Server instances..." -ForegroundColor Yellow
        $instances = @()
        
        # Method 1: Check registry for SQL instances
        $regPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
        if (Test-Path $regPath) {
            try {
                $regInstances = Get-ItemProperty -Path $regPath -ErrorAction Stop
                foreach ($property in $regInstances.PSObject.Properties) {
                    if ($property.Name -notin @("PSPath", "PSParentPath", "PSChildName", "PSDrive", "PSProvider")) {
                        if ($property.Name -eq "MSSQLSERVER") {
                            $instances += $env:COMPUTERNAME
                        } else {
                            $instances += "$env:COMPUTERNAME\$($property.Name)"
                        }
                    }
                }
            } catch {
                Write-Host "Warning: Could not read registry for SQL instances" -ForegroundColor Yellow
            }
        }
        
        # Method 2: Try localhost if nothing found
        if ($instances.Count -eq 0) {
            Write-Host "No instances detected via registry. Using localhost..." -ForegroundColor Yellow
            $instances += "localhost"
        }
        
        return $instances | Select-Object -Unique
    }
}

# Database Backup Class
class DatabaseBackup {
    [string]$Instance
    [hashtable]$ConnectionParams
    [string]$BackupFolder
    [bool]$UseCompression
    [bool]$SkipPermissionErrors
    
    DatabaseBackup([string]$instance, [hashtable]$connParams, [string]$backupPath, [bool]$compression, [bool]$skipErrors) {
        $this.Instance = $instance
        $this.ConnectionParams = $connParams
        $this.UseCompression = $compression
        $this.SkipPermissionErrors = $skipErrors
        
        # Create instance-specific backup folder
        $instanceFolder = $instance -replace "\\", "_" -replace ":", ""
        $dateFolder = Get-Date -Format ([Config]::DateFormat)
        $this.BackupFolder = Join-Path -Path $backupPath -ChildPath "$instanceFolder\$dateFolder"
        New-Item -ItemType Directory -Path $this.BackupFolder -Force | Out-Null
    }
    
    [PSCustomObject[]] GetDatabases([bool]$includeSystem) {
        # Check if current user is sysadmin
        $hasSysAdmin = [SQLPermissionElevator]::CheckSysAdminAccess($this.ConnectionParams)
        
        if ($hasSysAdmin) {
            # For sysadmin users, don't check HAS_DBACCESS - just get all online databases
            $query = @"
SELECT 
    name, 
    state_desc, 
    recovery_model_desc,
    1 as has_access  -- Always return 1 for sysadmin users
FROM sys.databases 
WHERE state = 0  -- Online databases only
$(if (!$includeSystem) { "AND name NOT IN ('" + ([Config]::SystemDatabases -join "','") + "')" })
AND name != 'tempdb'  -- Never backup tempdb
ORDER BY name
"@
        } else {
            # For non-sysadmin users, use HAS_DBACCESS check
            $query = @"
SELECT 
    name, 
    state_desc, 
    recovery_model_desc,
    HAS_DBACCESS(name) as has_access
FROM sys.databases 
WHERE state = 0  -- Online databases only
$(if (!$includeSystem) { "AND name NOT IN ('" + ([Config]::SystemDatabases -join "','") + "')" })
AND name != 'tempdb'  -- Never backup tempdb
ORDER BY name
"@
        }
        
        try {
            # Use splatting correctly without parentheses
            $params = $this.ConnectionParams
            return Invoke-Sqlcmd @params -Query $query
        } catch {
            Write-Host "Error getting database list: $_" -ForegroundColor Red
            return @()
        }
    }
    
    [PSCustomObject] BackupDatabase([string]$dbName) {
        $result = [PSCustomObject]@{
            Instance = $this.Instance
            Database = $dbName
            Status = "Unknown"
            BackupFile = "-"
            SizeMB = 0
            Duration = "-"
            Error = ""
        }
        
        $backupFile = Join-Path -Path $this.BackupFolder -ChildPath "$dbName`_$(Get-Date -Format 'yyyyMMdd_HHmmss').bak"
        $result.BackupFile = $backupFile
        
        # Build backup command with better error handling
        $backupQuery = @"
BEGIN TRY
    BACKUP DATABASE [$dbName] 
    TO DISK = N'$backupFile' 
    WITH FORMAT, INIT, 
    NAME = N'$dbName - Full Backup $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')',
    SKIP, NOREWIND, NOUNLOAD, STATS = 10
    $(if ($this.UseCompression) { ", COMPRESSION" })
END TRY
BEGIN CATCH
    THROW;
END CATCH
"@
        
        try {
            $startTime = Get-Date
            
            # Execute backup with proper error handling - use splatting correctly
            $params = $this.ConnectionParams
            Invoke-Sqlcmd @params -Query $backupQuery -QueryTimeout ([Config]::QueryTimeout) -Verbose:$false
            
            $endTime = Get-Date
            $duration = $endTime - $startTime
            
            # Check if file exists before getting info
            if (Test-Path $backupFile) {
                $fileInfo = Get-Item $backupFile -ErrorAction SilentlyContinue
                if ($fileInfo) {
                    $result.SizeMB = [math]::Round($fileInfo.Length / 1MB, 2)
                }
            }
            
            $result.Status = "Success"
            $result.Duration = $duration.ToString('mm\:ss')
            
            Write-Host "  ✓ Backup completed successfully" -ForegroundColor Green
            if ($result.SizeMB -gt 0) {
                Write-Host "  Size: $($result.SizeMB) MB | Duration: $($result.Duration)" -ForegroundColor Gray
            }
            
        } catch {
            $result.Status = "Failed"
            $result.Error = $_.Exception.Message
            
            # Check for specific error types
            if ($_.Exception.Message -like "*server principal*is not able to access the database*" -or
                $_.Exception.Message -like "*BACKUP DATABASE permission*") {
                Write-Host "  ✗ Permission denied for database: $dbName" -ForegroundColor Yellow
                if (!$this.SkipPermissionErrors) {
                    throw
                }
            } elseif ($_.Exception.Message -like "*is not accessible*" -or 
                      $_.Exception.Message -like "*database is being recovered*" -or
                      $_.Exception.Message -like "*database is in*state*") {
                Write-Host "  ⚠ Database not accessible (may be offline/recovering): $dbName" -ForegroundColor Yellow
            } elseif ($_.Exception.Message -like "*Cannot open backup device*" -or
                      $_.Exception.Message -like "*Access is denied*") {
                Write-Host "  ✗ File system access denied: $dbName" -ForegroundColor Red
                Write-Host "    Check backup path permissions: $($this.BackupFolder)" -ForegroundColor Yellow
            } else {
                Write-Host "  ✗ Backup failed: $($_.Exception.Message)" -ForegroundColor Red
            }
        }
        
        return $result
    }
}

# Main Execution
function Main {
    # Ensure we're running with appropriate privileges
    $currentPrincipal = [Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()
    $isAdmin = $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
    
    if (!$isAdmin -and $AutoElevate) {
        Write-Host "ERROR: Auto-elevation requires Administrator privileges!" -ForegroundColor Red
        Write-Host "Please run PowerShell as Administrator and try again." -ForegroundColor Yellow
        exit 1
    } elseif (!$isAdmin) {
        Write-Host "WARNING: Script is not running as Administrator." -ForegroundColor Yellow
        Write-Host "Some operations may fail. Consider running as Administrator.`n" -ForegroundColor Yellow
    } else {
        Write-Host "✓ Running as Administrator" -ForegroundColor Green
    }
    
    # Auto-detect SQL instances
    $sqlInstances = [SQLInstanceDetector]::GetInstances()
    
    if ($sqlInstances.Count -eq 0) {
        Write-Host "No SQL Server instances detected on this server!" -ForegroundColor Red
        exit 1
    }
    
    Write-Host "Found $($sqlInstances.Count) SQL instance(s): $($sqlInstances -join ', ')" -ForegroundColor Green
    
    # Create backup directory
    if (!(Test-Path -Path $BackupPath)) {
        try {
            New-Item -ItemType Directory -Path $BackupPath -Force | Out-Null
            Write-Host "Created backup directory: $BackupPath" -ForegroundColor Green
        } catch {
            Write-Host "ERROR: Cannot create backup directory: $BackupPath" -ForegroundColor Red
            Write-Host "Error: $_" -ForegroundColor Red
            exit 1
        }
    }
    
    # Load SQL Server module
    $moduleLoaded = $false
    $moduleName = ""
    
    # Try SqlServer module first (newer)
    try {
        Import-Module SqlServer -ErrorAction Stop
        $moduleLoaded = $true
        $moduleName = "SqlServer"
        Write-Host "Loaded module: SqlServer (latest version)" -ForegroundColor Green
    } catch {
        # Try SQLPS module (older)
        try {
            Import-Module SQLPS -DisableNameChecking -ErrorAction Stop
            $moduleLoaded = $true
            $moduleName = "SQLPS"
            Write-Host "Loaded module: SQLPS (older version)" -ForegroundColor Yellow
            Write-Host "Note: Consider updating to SqlServer module for better features" -ForegroundColor Yellow
        } catch {
            # Neither module is available
        }
    }
    
    if (!$moduleLoaded) {
        Write-Host "No SQL PowerShell module found. Installing SqlServer module..." -ForegroundColor Yellow
        try {
            Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser -ErrorAction Stop
            Import-Module SqlServer
            $moduleLoaded = $true
            $moduleName = "SqlServer"
        } catch {
            Write-Host "ERROR: Could not load SQL Server PowerShell module" -ForegroundColor Red
            Write-Host "Please install manually: Install-Module -Name SqlServer -Scope CurrentUser" -ForegroundColor Yellow
            exit 1
        }
    }
    
    # Process each instance
    $allResults = @()
    
    foreach ($instance in $sqlInstances) {
        Write-Host "`n" ("=" * 60) -ForegroundColor Cyan
        Write-Host "Processing Instance: $instance" -ForegroundColor Cyan
        Write-Host ("=" * 60) -ForegroundColor Cyan
        
        # Build connection parameters
        $connectionParams = @{
            ServerInstance = $instance
        }
        
        # Add authentication
        if (!$UseWindowsAuth -and $Username -and $Password) {
            Write-Host "Using SQL Authentication for user: $Username" -ForegroundColor Yellow
            $securePassword = ConvertTo-SecureString $Password -AsPlainText -Force
            $credential = New-Object System.Management.Automation.PSCredential ($Username, $securePassword)
            $connectionParams.Add("Credential", $credential)
        } else {
            Write-Host "Using Windows Authentication" -ForegroundColor Green
            # Windows Auth doesn't need additional parameters
        }
        
        # Test connection
        try {
            Write-Host "Testing connection to $instance..." -ForegroundColor Yellow
            $testQuery = "SELECT @@VERSION as Version, SUSER_SNAME() as LoginName"
            $testResult = Invoke-Sqlcmd @connectionParams -Query $testQuery -ErrorAction Stop
            Write-Host "Connected as: $($testResult.LoginName)" -ForegroundColor Green
            
            # Check for sysadmin permissions
            if ($AutoElevate -and $UseWindowsAuth) {
                $hasSysAdmin = [SQLPermissionElevator]::CheckSysAdminAccess($connectionParams)
                
                if (!$hasSysAdmin) {
                    Write-Host "Current user does not have sysadmin role" -ForegroundColor Yellow
                    
                    if ($isAdmin) {
                        $elevated = [SQLPermissionElevator]::ElevatePermissions($instance, $testResult.LoginName)
                        
                        if ($elevated) {
                            # Re-test connection after elevation
                            Write-Host "`nRe-testing connection with elevated permissions..." -ForegroundColor Yellow
                            $testResult = Invoke-Sqlcmd @connectionParams -Query $testQuery -ErrorAction Stop
                            Write-Host "✓ Reconnected as: $($testResult.LoginName) with sysadmin role" -ForegroundColor Green
                            
                            # Diagnose sysadmin status
                            [SQLPermissionElevator]::DiagnoseSysAdminStatus($connectionParams)
                            
                            # Update sysadmin status after elevation
                            $hasSysAdmin = $true
                        } else {
                            Write-Host "Continuing with current permissions..." -ForegroundColor Yellow
                        }
                    } else {
                        Write-Host "Script not running as Administrator. Cannot auto-elevate." -ForegroundColor Yellow
                        Write-Host "Tip: Run PowerShell as Administrator for automatic permission elevation" -ForegroundColor Yellow
                    }
                } else {
                    Write-Host "✓ User has sysadmin role" -ForegroundColor Green
                }
            } else {
                # If not using auto-elevate, check sysadmin status anyway for database filtering
                $hasSysAdmin = [SQLPermissionElevator]::CheckSysAdminAccess($connectionParams)
            }
        } catch {
            Write-Host "Failed to connect to instance: $instance" -ForegroundColor Red
            Write-Host "Error: $_" -ForegroundColor Red
            
            if ($_.Exception.Message -like "*Login failed*") {
                Write-Host "`nTip: Check SQL Server Authentication mode and ensure mixed mode is enabled" -ForegroundColor Yellow
                Write-Host "     Or use -UseWindowsAuth `$true for Windows Authentication" -ForegroundColor Yellow
            }
            continue
        }
        
        # Create backup handler
        $backupHandler = [DatabaseBackup]::new($instance, $connectionParams, $BackupPath, $UseCompression, $SkipPermissionErrors)
        
        # Get databases
        $databases = $backupHandler.GetDatabases($IncludeSystemDatabases)
        
        if ($databases.Count -eq 0) {
            Write-Host "No databases found to backup on instance: $instance" -ForegroundColor Yellow
            continue
        }
        
        Write-Host "Found $($databases.Count) database(s) to backup" -ForegroundColor Green
        
        # Final sysadmin check after any potential elevation
        $hasSysAdmin = [SQLPermissionElevator]::CheckSysAdminAccess($connectionParams)
        
        if ($hasSysAdmin) {
            Write-Host "✓ User has sysadmin rights - will attempt ALL databases" -ForegroundColor Green
            $databasesToBackup = $databases
            $skippedCount = 0
        } else {
            Write-Host "⚠ User does not have sysadmin rights - filtering by database access" -ForegroundColor Yellow
            
            # Check access to databases only for non-sysadmin users
            $accessibleDbs = @($databases | Where-Object { $_.has_access -eq 1 })
            $inaccessibleDbs = @($databases | Where-Object { $_.has_access -eq 0 })
            
            if ($inaccessibleDbs.Count -gt 0) {
                Write-Host "`nWARNING: No access to $($inaccessibleDbs.Count) database(s):" -ForegroundColor Yellow
                $inaccessibleDbs | ForEach-Object { Write-Host "  - $($_.name)" -ForegroundColor Yellow }
                
                if ($AutoElevate -and $isAdmin -and $UseWindowsAuth) {
                    Write-Host "`nTip: Run with -AutoElevate `$true to automatically gain access" -ForegroundColor Yellow
                }
            }
            
            $databasesToBackup = $accessibleDbs
            $skippedCount = $inaccessibleDbs.Count
        }
        
        $successCount = 0
        $failCount = 0
        
        foreach ($db in $databasesToBackup) {
            Write-Host "`nBacking up: $($db.name)" -ForegroundColor Cyan
            
            $result = $backupHandler.BackupDatabase($db.name)
            $allResults += $result
            
            if ($result.Status -eq "Success") {
                $successCount++
            } else {
                $failCount++
            }
        }
        
        # Instance Summary
        Write-Host "`n" ("-" * 40) -ForegroundColor Yellow
        Write-Host "Summary for $instance" -ForegroundColor Yellow
        Write-Host "Successful: $successCount" -ForegroundColor Green
        Write-Host "Failed: $failCount" -ForegroundColor $(if ($failCount -gt 0) { "Red" } else { "Gray" })
        
        # Show skipped count only if user doesn't have sysadmin rights
        if (!$hasSysAdmin) {
            Write-Host "Skipped (no access): $skippedCount" -ForegroundColor Yellow
        }
        
        # Export results
        if ($allResults.Count -gt 0) {
            $logFile = Join-Path -Path $backupHandler.BackupFolder -ChildPath ([Config]::LogFileName)
            $allResults | Export-Csv -Path $logFile -NoTypeInformation
            Write-Host "Log file: $logFile" -ForegroundColor Gray
        }
    }
    
    Write-Host "`n" ("=" * 60) -ForegroundColor Green
    Write-Host "All backup operations completed!" -ForegroundColor Green
    Write-Host "Backup location: $BackupPath" -ForegroundColor Green
}

# Run main function
Main