Move SQL Server Database Files

Move-SqlDatabaseFiles.ps1

<#
.SYNOPSIS
    Moves SQL Server database files to target drives with step-by-step DBA confirmation.

.DESCRIPTION
    This script moves SQL Server database files from their current locations to specified target paths.
    It processes databases one at a time, prompting for DBA confirmation at each step.
    The script handles both physical file movement and SQL Server metadata updates.

.PARAMETER ServerInstance
    The SQL Server instance to connect to (e.g., "localhost" or "SERVER\INSTANCE")

.PARAMETER TargetDataPath
    The target path for data files (default: K:\SQLDATA\)

.PARAMETER TargetLogPath
    The target path for log files (default: K:\SQLLOGS\)

.PARAMETER WhatIf
    If specified, shows what would be done without making changes

.EXAMPLE
    .\Move-SqlDatabaseFiles.ps1 -ServerInstance "localhost"

.EXAMPLE
    .\Move-SqlDatabaseFiles.ps1 -ServerInstance "PROD-SQL01" -WhatIf

.NOTES
    Version: 1.0
    Requires: SqlServer PowerShell module
#>

[CmdletBinding(SupportsShouldProcess)]
param(
    [Parameter(Mandatory = $true)]
    [string]$ServerInstance,

    [Parameter(Mandatory = $false)]
    [string]$TargetDataPath = "K:\SQLDATA\",

    [Parameter(Mandatory = $false)]
    [string]$TargetLogPath = "K:\SQLLOGS\"
)

# Ensure paths end with backslash
if (-not $TargetDataPath.EndsWith('\')) { $TargetDataPath += '\' }
if (-not $TargetLogPath.EndsWith('\')) { $TargetLogPath += '\' }

# ============================================================================
# VALIDATE LOCAL EXECUTION - Script MUST run on SQL Server VM
# ============================================================================

# Extract server name from ServerInstance parameter
$serverName = $ServerInstance.Split('\')[0].Split(',')[0].Trim()

# Check if ServerInstance refers to local machine
$isLocalExecution = $false

if ($serverName -in @('localhost', '.', '(local)', $env:COMPUTERNAME)) {
    $isLocalExecution = $true
}
elseif ($serverName -like "$env:COMPUTERNAME*") {
    $isLocalExecution = $true
}
else {
    # Try to resolve if it's an IP or hostname pointing to local machine
    try {
        $localIPs = @(Get-NetIPAddress | Where-Object { $_.AddressFamily -eq 'IPv4' } | Select-Object -ExpandProperty IPAddress)
        $localIPs += '127.0.0.1'

        if ($serverName -in $localIPs) {
            $isLocalExecution = $true
        }
    }
    catch {
        # If network cmdlets fail, continue with hostname comparison
    }
}

if (-not $isLocalExecution) {
    Write-Host "`n========================================" -ForegroundColor Red
    Write-Host "ERROR: REMOTE EXECUTION NOT SUPPORTED" -ForegroundColor Red
    Write-Host "========================================" -ForegroundColor Red
    Write-Host ""
    Write-Host "This script MUST be run locally on the SQL Server VM." -ForegroundColor Yellow
    Write-Host ""
    Write-Host "Current Machine: $env:COMPUTERNAME" -ForegroundColor Cyan
    Write-Host "Target SQL Server: $serverName" -ForegroundColor Cyan
    Write-Host ""
    Write-Host "Why? The script uses Move-Item to physically relocate database files," -ForegroundColor Gray
    Write-Host "which only works on local filesystems. Remote execution will cause:" -ForegroundColor Gray
    Write-Host "  - Database goes OFFLINE but files aren't moved" -ForegroundColor Gray
    Write-Host "  - Database stuck in OFFLINE state" -ForegroundColor Gray
    Write-Host "  - Production OUTAGE" -ForegroundColor Gray
    Write-Host ""
    Write-Host "Please follow these steps:" -ForegroundColor Yellow
    Write-Host "  1. RDP/Login to: $serverName" -ForegroundColor Yellow
    Write-Host "  2. Copy this script to the server" -ForegroundColor Yellow
    Write-Host "  3. Run: .\Move-SqlDatabaseFiles.ps1 -ServerInstance 'localhost'" -ForegroundColor Yellow
    Write-Host ""

    exit 1
}

Write-Host "✓ Validated: Script is running locally on SQL Server machine." -ForegroundColor Green
Write-Host ""

# Verify running as Administrator
$currentPrincipal = New-Object Security.Principal.WindowsPrincipal([Security.Principal.WindowsIdentity]::GetCurrent())
if (-not $currentPrincipal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)) {
    Write-Host ""
    Write-Host "================================================================" -ForegroundColor Red
    Write-Host "ERROR: ADMINISTRATOR PRIVILEGES REQUIRED" -ForegroundColor Red
    Write-Host "================================================================" -ForegroundColor Red
    Write-Host ""
    Write-Host "This script must be run as Administrator to move database files." -ForegroundColor Yellow
    Write-Host ""
    Write-Host "To fix this:" -ForegroundColor Yellow
    Write-Host "  1. Close this PowerShell window" -ForegroundColor Yellow
    Write-Host "  2. Right-click PowerShell and select 'Run as Administrator'" -ForegroundColor Yellow
    Write-Host "  3. Re-run the script" -ForegroundColor Yellow
    Write-Host ""

    exit 1
}

Write-Host "✓ Validated: Running with Administrator privileges." -ForegroundColor Green
Write-Host ""

# Prompt for target directories
Write-Host "Target Directory Configuration" -ForegroundColor Cyan
Write-Host "==============================" -ForegroundColor Cyan
Write-Host ""
Write-Host "Current defaults:" -ForegroundColor Yellow
Write-Host "  Data files: $TargetDataPath" -ForegroundColor White
Write-Host "  Log files:  $TargetLogPath" -ForegroundColor White
Write-Host ""

$newDataPath = Read-Host "Enter target path for DATA files (or press Enter to use $TargetDataPath)"
if (-not [string]::IsNullOrWhiteSpace($newDataPath)) {
    $TargetDataPath = $newDataPath
    if (-not $TargetDataPath.EndsWith('\')) { $TargetDataPath += '\' }
}

$newLogPath = Read-Host "Enter target path for LOG files (or press Enter to use $TargetLogPath)"
if (-not [string]::IsNullOrWhiteSpace($newLogPath)) {
    $TargetLogPath = $newLogPath
    if (-not $TargetLogPath.EndsWith('\')) { $TargetLogPath += '\' }
}

Write-Host ""
Write-Host "✓ Using target directories:" -ForegroundColor Green
Write-Host "  Data files: $TargetDataPath" -ForegroundColor White
Write-Host "  Log files:  $TargetLogPath" -ForegroundColor White
Write-Host ""

# Setup logging
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$logPath = ".\DatabaseMove_$timestamp.log"
$csvLogPath = ".\DatabaseMove_$timestamp.csv"

Start-Transcript -Path $logPath -Append

function Write-Log {
    param([string]$Message, [string]$Level = "INFO")
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logMessage = "[$timestamp] [$Level] $Message"
    Write-Host $logMessage
    # Don't write to log file directly - Start-Transcript captures all output
}

function Test-SqlServerModule {
    Write-Log "Checking for SqlServer PowerShell module..."
    if (-not (Get-Module -ListAvailable -Name SqlServer)) {
        Write-Log "SqlServer module not found. Attempting to install..." "WARNING"
        try {
            Install-Module -Name SqlServer -Scope CurrentUser -Force -AllowClobber
            Import-Module SqlServer
            Write-Log "SqlServer module installed successfully." "SUCCESS"
        }
        catch {
            Write-Log "Failed to install SqlServer module: $_" "ERROR"
            return $false
        }
    }
    else {
        Import-Module SqlServer -ErrorAction Stop
        Write-Log "SqlServer module loaded successfully." "SUCCESS"
    }
    return $true
}

function Test-SqlConnection {
    param([string]$ServerInstance)
    Write-Log "Testing connection to SQL Server: $ServerInstance"
    try {
        $query = "SELECT @@VERSION AS Version"
        $result = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -ErrorAction Stop
        Write-Log "Successfully connected to SQL Server" "SUCCESS"
        Write-Log "SQL Server Version: $($result.Version)"
        return $true
    }
    catch {
        Write-Log "Failed to connect to SQL Server: $_" "ERROR"
        return $false
    }
}

function Ensure-TargetDirectories {
    param([string]$DataPath, [string]$LogPath)

    Write-Log "Checking target directories..."

    if (-not (Test-Path $DataPath)) {
        Write-Log "Creating directory: $DataPath"
        if (-not $WhatIfPreference) {
            New-Item -Path $DataPath -ItemType Directory -Force | Out-Null
        }
    }

    if (-not (Test-Path $LogPath)) {
        Write-Log "Creating directory: $LogPath"
        if (-not $WhatIfPreference) {
            New-Item -Path $LogPath -ItemType Directory -Force | Out-Null
        }
    }

    Write-Log "Target directories verified." "SUCCESS"
}

function Test-DiskSpace {
    param(
        [string]$TargetPath,
        [decimal]$RequiredMB
    )

    Write-Log "Checking disk space for: $TargetPath"

    # Get the drive letter from the target path
    $driveLetter = Split-Path -Path $TargetPath -Qualifier

    try {
        $drive = Get-PSDrive -Name $driveLetter.TrimEnd(':') -ErrorAction Stop
        $freeSpaceMB = [math]::Round($drive.Free / 1MB, 2)
        $requiredWithBuffer = [math]::Round($RequiredMB * 1.2, 2)  # 20% safety buffer

        Write-Log "  Drive: $driveLetter"
        Write-Log "  Free Space: $freeSpaceMB MB"
        Write-Log "  Required (with 20% buffer): $requiredWithBuffer MB"

        if ($freeSpaceMB -lt $requiredWithBuffer) {
            Write-Log "Insufficient disk space on $driveLetter" "ERROR"
            Write-Log "  Available: $freeSpaceMB MB" "ERROR"
            Write-Log "  Needed: $requiredWithBuffer MB" "ERROR"
            return $false
        }

        Write-Log "Sufficient disk space available." "SUCCESS"
        return $true
    }
    catch {
        Write-Log "Failed to check disk space: $_" "ERROR"
        return $false
    }
}

function Test-ActiveBackups {
    param([string]$ServerInstance)

    Write-Log "Checking for active backup operations..."

    $query = @"
SELECT
    database_name,
    backup_start_date,
    type,
    DATEDIFF(MINUTE, backup_start_date, GETDATE()) AS DurationMinutes
FROM msdb.dbo.backupset
WHERE backup_finish_date IS NULL
"@

    try {
        $activeBackups = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -ErrorAction Stop

        if ($activeBackups) {
            Write-Log "WARNING: Active backup operations detected!" "WARNING"
            foreach ($backup in $activeBackups) {
                Write-Log "  Database: $($backup.database_name), Type: $($backup.type), Duration: $($backup.DurationMinutes) minutes" "WARNING"
            }
            return $false
        }

        Write-Log "No active backups detected." "SUCCESS"
        return $true
    }
    catch {
        Write-Log "Failed to check for active backups: $_" "WARNING"
        # Return true to allow continuation, but log the warning
        return $true
    }
}

function Test-SqlServiceAccountPermissions {
    param(
        [string]$ServerInstance,
        [string]$TargetDataPath,
        [string]$TargetLogPath
    )

    Write-Log "Checking SQL Server service account information..."

    $query = @"
SELECT
    servicename,
    service_account,
    startup_type_desc,
    status_desc
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server (%'
"@

    try {
        $serviceInfo = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -ErrorAction Stop

        if ($serviceInfo) {
            Write-Log "SQL Server Service Information:" "INFO"
            Write-Log "  Service: $($serviceInfo.servicename)" "INFO"
            Write-Log "  Account: $($serviceInfo.service_account)" "INFO"
            Write-Log "  Status: $($serviceInfo.status_desc)" "INFO"
            Write-Log "" "INFO"
            Write-Log "IMPORTANT: Verify that '$($serviceInfo.service_account)' has the following permissions:" "WARNING"
            Write-Log "  - Read/Write/Modify on: $TargetDataPath" "WARNING"
            Write-Log "  - Read/Write/Modify on: $TargetLogPath" "WARNING"
            Write-Log "" "INFO"

            do {
                $response = Read-Host "Have you verified SQL Server service account has proper permissions on target directories? (Y/N)"
                $response = $response.ToUpper()
            } while ($response -notin @('Y', 'N'))

            if ($response -eq 'N') {
                Write-Log "User indicated permissions are not verified. Exiting." "ERROR"
                return $false
            }

            Write-Log "User confirmed service account permissions are correct." "SUCCESS"
            return $true
        }
        else {
            Write-Log "Could not retrieve SQL Server service account information." "WARNING"
            return $true  # Allow continuation but warn
        }
    }
    catch {
        Write-Log "Failed to check SQL Server service account: $_" "WARNING"
        return $true  # Allow continuation but warn
    }
}

function Get-DatabasesForMigration {
    param([string]$ServerInstance)

    Write-Log "Querying databases that need migration..."

    $query = @"
SELECT
    db.name AS DatabaseName,
    db.database_id AS DatabaseID,
    db.is_read_only AS IsReadOnly,
    db.source_database_id AS SourceDatabaseID,
    mf.file_id AS FileID,
    mf.name AS LogicalName,
    mf.physical_name AS PhysicalName,
    mf.type_desc AS FileType,
    mf.size * 8 / 1024 AS SizeMB,
    mf.is_sparse AS IsSparse,
    CASE WHEN EXISTS (
        SELECT 1 FROM sys.database_files df
        WHERE df.type_desc = 'FILESTREAM'
        AND df.data_space_id IN (
            SELECT data_space_id FROM sys.filegroups
            WHERE type = 'FD'
        )
    ) THEN 1 ELSE 0 END AS HasFilestream
FROM
    sys.databases db
    INNER JOIN sys.master_files mf ON db.database_id = mf.database_id
WHERE
    db.database_id > 4  -- Exclude system databases
    AND LEFT(mf.physical_name, 2) <> 'K:'  -- Not already on K: drive
    AND db.state_desc = 'ONLINE'  -- Only online databases
    AND db.source_database_id IS NULL  -- Exclude database snapshots
    AND mf.type_desc <> 'FILESTREAM'  -- Exclude FILESTREAM files (require special handling)
ORDER BY
    db.is_read_only DESC,  -- Process read-only first
    db.name,
    mf.type_desc DESC  -- Data files before log files
"@

    try {
        $results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -ErrorAction Stop

        if ($results) {
            # Check for databases with FILESTREAM or that are snapshots
            $filestreamDbs = $results | Where-Object { $_.HasFilestream -eq 1 } | Select-Object -ExpandProperty DatabaseName -Unique

            if ($filestreamDbs) {
                Write-Log "WARNING: The following databases have FILESTREAM data and are excluded from migration:" "WARNING"
                foreach ($fsDb in $filestreamDbs) {
                    Write-Log "  - $fsDb (FILESTREAM requires special migration procedures)" "WARNING"
                }
                Write-Log "Refer to Microsoft documentation for FILESTREAM database migration procedures." "WARNING"
            }

            # Group by database
            $databases = $results | Group-Object -Property DatabaseName
            Write-Log "Found $($databases.Count) database(s) requiring migration." "SUCCESS"
            return $databases
        }
        else {
            Write-Log "No databases found that require migration." "INFO"
            return $null
        }
    }
    catch {
        Write-Log "Error querying databases: $_" "ERROR"
        return $null
    }
}

function Move-DatabaseFiles {
    param(
        [string]$ServerInstance,
        [object]$DatabaseGroup,
        [string]$DataPath,
        [string]$LogPath
    )

    $dbName = $DatabaseGroup.Name
    $files = $DatabaseGroup.Group
    $isReadOnly = $files[0].IsReadOnly

    Write-Host "`n================================================" -ForegroundColor Cyan
    Write-Host "DATABASE: $dbName" -ForegroundColor Cyan
    Write-Host "================================================" -ForegroundColor Cyan
    Write-Host "Read-Only: $(if($isReadOnly){'YES'}else{'NO'})"
    Write-Host "Files to move:"

    # Calculate total size and check for file conflicts
    $totalSizeMB = 0
    $hasConflicts = $false
    $conflictDetails = @()

    foreach ($file in $files) {
        $targetPath = if ($file.FileType -eq 'LOG') { $LogPath } else { $DataPath }
        $fileName = Split-Path $file.PhysicalName -Leaf
        $newPath = Join-Path $targetPath $fileName

        $totalSizeMB += $file.SizeMB

        Write-Host "  [$($file.FileType)] $($file.LogicalName)" -ForegroundColor Yellow
        Write-Host "    Current: $($file.PhysicalName)"
        Write-Host "    New:     $newPath"
        Write-Host "    Size:    $($file.SizeMB) MB"

        # Check if file already exists at destination
        if (Test-Path $newPath) {
            $hasConflicts = $true
            $conflictDetails += "    WARNING: File already exists: $newPath"
            Write-Host "    WARNING: File already exists at destination!" -ForegroundColor Red
        }
    }

    Write-Host ""
    Write-Host "Total size to move: $totalSizeMB MB" -ForegroundColor Cyan

    # Display conflict warnings if any
    if ($hasConflicts) {
        Write-Host ""
        Write-Host "FILE CONFLICTS DETECTED:" -ForegroundColor Red
        foreach ($conflict in $conflictDetails) {
            Write-Host $conflict -ForegroundColor Red
        }
        Write-Host ""
    }

    # Validate disk space for data files
    $dataFilesSize = ($files | Where-Object { $_.FileType -ne 'LOG' } | Measure-Object -Property SizeMB -Sum).Sum
    $logFilesSize = ($files | Where-Object { $_.FileType -eq 'LOG' } | Measure-Object -Property SizeMB -Sum).Sum

    if ($dataFilesSize -gt 0) {
        if (-not (Test-DiskSpace -TargetPath $DataPath -RequiredMB $dataFilesSize)) {
            Write-Host "ERROR: Insufficient disk space for data files on $DataPath" -ForegroundColor Red
            Write-Log "Skipping database $dbName due to insufficient disk space" "ERROR"
            return "SKIP"
        }
    }

    if ($logFilesSize -gt 0) {
        if (-not (Test-DiskSpace -TargetPath $LogPath -RequiredMB $logFilesSize)) {
            Write-Host "ERROR: Insufficient disk space for log files on $LogPath" -ForegroundColor Red
            Write-Log "Skipping database $dbName due to insufficient disk space" "ERROR"
            return "SKIP"
        }
    }

    Write-Host "`n"

    # Prompt for confirmation
    do {
        $response = Read-Host "Process this database? (Y=Yes, N=No/Skip, Q=Quit script)"
        $response = $response.ToUpper()
    } while ($response -notin @('Y', 'N', 'Q'))

    if ($response -eq 'Q') {
        Write-Log "User chose to quit script." "INFO"
        return "QUIT"
    }

    if ($response -eq 'N') {
        Write-Log "Skipping database: $dbName" "INFO"
        return "SKIP"
    }

    # Process the database
    try {
        Write-Log "Processing database: $dbName" "INFO"

        if ($WhatIfPreference) {
            Write-Log "[WHATIF] Would set database offline: $dbName" "INFO"
            Write-Log "[WHATIF] Would move files and update metadata" "INFO"
            Write-Log "[WHATIF] Would set database online: $dbName" "INFO"
            return "SUCCESS"
        }

        # Step 1: Set database offline
        Write-Log "Setting database OFFLINE: $dbName"
        $offlineQuery = "ALTER DATABASE [$dbName] SET OFFLINE WITH ROLLBACK IMMEDIATE;"
        Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $offlineQuery -ErrorAction Stop
        Write-Log "Database set OFFLINE successfully." "SUCCESS"

        # Step 2: Move files
        $fileMoves = @()
        foreach ($file in $files) {
            $targetPath = if ($file.FileType -eq 'LOG') { $LogPath } else { $DataPath }
            $fileName = Split-Path $file.PhysicalName -Leaf
            $newPath = Join-Path $targetPath $fileName

            Write-Log "Moving file: $($file.PhysicalName) -> $newPath"

            # Check if destination file already exists
            if (Test-Path $newPath) {
                Write-Log "WARNING: File already exists at destination: $newPath" "WARNING"
                Write-Host "`nWARNING: Destination file already exists!" -ForegroundColor Yellow
                Write-Host "File: $newPath" -ForegroundColor Yellow

                do {
                    $overwriteResponse = Read-Host "Overwrite existing file? (Y=Yes, N=No/Cancel this database)"
                    $overwriteResponse = $overwriteResponse.ToUpper()
                } while ($overwriteResponse -notin @('Y', 'N'))

                if ($overwriteResponse -eq 'N') {
                    Write-Log "User declined to overwrite existing file. Canceling database migration." "WARNING"
                    throw "User declined to overwrite existing file at $newPath"
                }

                Write-Log "User confirmed overwrite of existing file." "WARNING"
            }

            try {
                Move-Item -Path $file.PhysicalName -Destination $newPath -Force -ErrorAction Stop
                $fileMoves += @{
                    OldPath = $file.PhysicalName
                    NewPath = $newPath
                    LogicalName = $file.LogicalName
                    Success = $true
                }
                Write-Log "File moved successfully." "SUCCESS"
            }
            catch {
                Write-Log "Failed to move file: $_" "ERROR"
                $fileMoves += @{
                    OldPath = $file.PhysicalName
                    NewPath = $newPath
                    LogicalName = $file.LogicalName
                    Success = $false
                }
                throw "File move failed: $_"
            }
        }

        # Step 3: Update SQL Server metadata
        Write-Log "Updating database metadata..."
        foreach ($move in $fileMoves | Where-Object { $_.Success }) {
            $modifyQuery = "ALTER DATABASE [$dbName] MODIFY FILE (NAME = [$($move.LogicalName)], FILENAME = '$($move.NewPath)');"
            Write-Log "Executing: $modifyQuery"
            Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $modifyQuery -ErrorAction Stop
        }
        Write-Log "Metadata updated successfully." "SUCCESS"

        # Verify metadata was updated correctly
        Write-Log "Verifying metadata updates..."
        # Escape single quotes in database name to prevent SQL injection
        $safeDbName = $dbName.Replace("'", "''")
        $verifyMetadataQuery = @"
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'$safeDbName')
"@
        $currentPaths = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $verifyMetadataQuery
        Write-Log "Verified file locations in SQL Server metadata:" "SUCCESS"
        foreach ($path in $currentPaths) {
            Write-Log "  $($path.name): $($path.physical_name)"
        }

        # Step 4: Set database online
        Write-Log "Setting database ONLINE: $dbName"
        $onlineQuery = "ALTER DATABASE [$dbName] SET ONLINE;"
        Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $onlineQuery -ErrorAction Stop
        Write-Log "Database set ONLINE successfully." "SUCCESS"

        # Step 5: Verify database is accessible
        # Escape single quotes in database name to prevent SQL injection
        $safeDbName = $dbName.Replace("'", "''")
        $verifyQuery = "SELECT COUNT(*) AS TableCount FROM [$safeDbName].sys.tables;"
        $verifyResult = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $verifyQuery -ErrorAction Stop
        Write-Log "Database verification successful. Table count: $($verifyResult.TableCount)" "SUCCESS"

        # Log to CSV
        $csvEntry = [PSCustomObject]@{
            Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
            DatabaseName = $dbName
            IsReadOnly = $isReadOnly
            Status = "SUCCESS"
            FilesCount = $files.Count
        }
        $csvEntry | Export-Csv -Path $csvLogPath -Append -NoTypeInformation

        Write-Host "`nDatabase migration completed successfully!" -ForegroundColor Green
        return "SUCCESS"
    }
    catch {
        Write-Log "ERROR processing database $dbName : $_" "ERROR"
        Write-Host "`nERROR: $($_.Exception.Message)" -ForegroundColor Red

        # Attempt rollback
        Write-Log "Attempting to rollback file moves..." "WARNING"
        foreach ($move in $fileMoves | Where-Object { $_.Success }) {
            try {
                if (Test-Path $move.NewPath) {
                    Write-Log "Rolling back: $($move.NewPath) -> $($move.OldPath)"
                    Move-Item -Path $move.NewPath -Destination $move.OldPath -Force -ErrorAction Stop
                }
            }
            catch {
                Write-Log "Failed to rollback file: $_" "ERROR"
            }
        }

        # Try to bring database back online
        try {
            Write-Log "Attempting to set database ONLINE after error..."
            $onlineQuery = "ALTER DATABASE [$dbName] SET ONLINE;"
            Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $onlineQuery -ErrorAction Stop
            Write-Log "Database brought back ONLINE after rollback." "WARNING"
        }
        catch {
            Write-Log "CRITICAL: Failed to bring database ONLINE. Manual intervention required!" "ERROR"
        }

        # Log to CSV
        $csvEntry = [PSCustomObject]@{
            Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
            DatabaseName = $dbName
            IsReadOnly = $isReadOnly
            Status = "FAILED"
            FilesCount = $files.Count
        }
        $csvEntry | Export-Csv -Path $csvLogPath -Append -NoTypeInformation

        return "FAILED"
    }
}

# ============================================================================
# MAIN SCRIPT EXECUTION
# ============================================================================

Write-Host "`n========================================" -ForegroundColor Cyan
Write-Host "SQL Server Database File Migration Tool" -ForegroundColor Cyan
Write-Host "========================================`n" -ForegroundColor Cyan

Write-Log "Script started"
Write-Log "Server Instance: $ServerInstance"
Write-Log "Target Data Path: $TargetDataPath"
Write-Log "Target Log Path: $TargetLogPath"
Write-Log "WhatIf Mode: $WhatIfPreference"

# Step 1: Check prerequisites
if (-not (Test-SqlServerModule)) {
    Write-Log "Prerequisites check failed. Exiting." "ERROR"
    Stop-Transcript
    exit 1
}

if (-not (Test-SqlConnection -ServerInstance $ServerInstance)) {
    Write-Log "Cannot connect to SQL Server. Exiting." "ERROR"
    Stop-Transcript
    exit 1
}

# Step 2: Check for active backups
if (-not $WhatIfPreference) {
    if (-not (Test-ActiveBackups -ServerInstance $ServerInstance)) {
        Write-Log "Active backup operations detected. It is recommended to wait for backups to complete." "WARNING"
        do {
            $response = Read-Host "Continue anyway? (Y/N)"
            $response = $response.ToUpper()
        } while ($response -notin @('Y', 'N'))

        if ($response -eq 'N') {
            Write-Log "User chose to exit due to active backups." "INFO"
            Stop-Transcript
            exit 0
        }
    }
}

# Step 3: Ensure target directories exist
if (-not $WhatIfPreference) {
    Ensure-TargetDirectories -DataPath $TargetDataPath -LogPath $TargetLogPath
}

# Step 4: Verify SQL Server service account permissions
if (-not $WhatIfPreference) {
    if (-not (Test-SqlServiceAccountPermissions -ServerInstance $ServerInstance -TargetDataPath $TargetDataPath -TargetLogPath $TargetLogPath)) {
        Write-Log "SQL Server service account permissions not verified. Exiting." "ERROR"
        Stop-Transcript
        exit 1
    }
}

# Step 5: Check for database snapshots
Write-Log "Checking for database snapshots..."
$snapshotQuery = @"
SELECT
    db.name AS SnapshotName,
    db_name(db.source_database_id) AS SourceDatabase
FROM sys.databases db
WHERE db.source_database_id IS NOT NULL
ORDER BY db.name
"@

try {
    $snapshots = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $snapshotQuery -ErrorAction Stop

    if ($snapshots) {
        Write-Log "WARNING: Database snapshots detected on this server:" "WARNING"
        foreach ($snapshot in $snapshots) {
            Write-Log "  - Snapshot: $($snapshot.SnapshotName) (Source: $($snapshot.SourceDatabase))" "WARNING"
        }
        Write-Log "" "WARNING"
        Write-Log "Database snapshots are automatically excluded from migration." "WARNING"
        Write-Log "Note: Moving source database files will invalidate snapshots." "WARNING"
        Write-Log "Consider dropping snapshots before migration or recreating them after." "WARNING"
        Write-Log "" "INFO"

        do {
            $response = Read-Host "Continue with database migration? (Y/N)"
            $response = $response.ToUpper()
        } while ($response -notin @('Y', 'N'))

        if ($response -eq 'N') {
            Write-Log "User chose to exit due to database snapshots." "INFO"
            Stop-Transcript
            exit 0
        }
    }
    else {
        Write-Log "No database snapshots detected." "SUCCESS"
    }
}
catch {
    Write-Log "Failed to check for database snapshots: $_" "WARNING"
    # Continue anyway
}

# Step 6: Get databases for migration
$databases = Get-DatabasesForMigration -ServerInstance $ServerInstance

if (-not $databases) {
    Write-Log "No databases to process. Exiting." "INFO"
    Stop-Transcript
    exit 0
}

# Step 7: Process each database
$successCount = 0
$failedCount = 0
$skippedCount = 0

foreach ($dbGroup in $databases) {
    $result = Move-DatabaseFiles -ServerInstance $ServerInstance `
                                  -DatabaseGroup $dbGroup `
                                  -DataPath $TargetDataPath `
                                  -LogPath $TargetLogPath

    switch ($result) {
        "SUCCESS" { $successCount++ }
        "FAILED" { $failedCount++ }
        "SKIP" { $skippedCount++ }
        "QUIT" {
            Write-Log "Script terminated by user." "INFO"
            Stop-Transcript
            exit 0
        }
    }

    if ($result -ne "QUIT" -and ($databases.IndexOf($dbGroup) -lt $databases.Count - 1)) {
        Write-Host "`n"
        do {
            $continue = Read-Host "Ready to process next database? (Y=Yes, Q=Quit)"
            $continue = $continue.ToUpper()
        } while ($continue -notin @('Y', 'Q'))

        if ($continue -eq 'Q') {
            Write-Log "User chose to quit script." "INFO"
            Stop-Transcript
            exit 0
        }
    }
}

# Step 8: Summary
Write-Host "`n========================================" -ForegroundColor Cyan
Write-Host "MIGRATION SUMMARY" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host "Successful: $successCount" -ForegroundColor Green
Write-Host "Failed:     $failedCount" -ForegroundColor Red
Write-Host "Skipped:    $skippedCount" -ForegroundColor Yellow
Write-Host "========================================`n" -ForegroundColor Cyan

Write-Log "Script completed. Success: $successCount, Failed: $failedCount, Skipped: $skippedCount"
Write-Log "Log file: $logPath"
Write-Log "CSV log: $csvLogPath"

Stop-Transcript

Write-Host "Transcript saved to: $logPath" -ForegroundColor Cyan
Write-Host "CSV log saved to: $csvLogPath" -ForegroundColor Cyan