Как пересоздать индексы mssql в новую файловую группу с помощью PowerShell и dbatools с логированием и бэкапом ver 2

автор: автор автор
5 мая, 20265 мая, 2026
Перенос индексов SQL Server в другую файловую группу — задача, требующая высокой точности и контроля. Ручное выполнение связано с риском потери метаданных, длительными блокировками и отсутствием аудита изменений. В статье представлен готовый к эксплуатации PowerShell-скрипт, который полностью автоматизирует процесс безопасного пересоздания индексов. Решение поддерживает гибкую настройку целевой файловой группы (вплоть до выбора ФГ по умолчанию для каждой БД), автоматически генерирует резервные копии скриптов CREATE INDEX перед изменением, ведёт логирование в файл и системную SQL-таблицу, пропускает индексы со встроенными ограничениями (FULLTEXT, XML, Spatial) и по завершении проводит финальную сверку фактического расположения всех индексов. Дополнительно реализованы автоматическая очистка старых артефактов, прогресс-бар, периодическое подтверждение выполнения и тонкая настройка параметров ONLINE, MAXDOP, SORT_IN_TEMPDB и FILLFACTOR. Скрипт опирается на модуль dbatools и готов к использованию в плановых окнах обслуживания или при оптимизации дисковой подсистемы.
🔑 Ключевые возможности

✅ Точечный выбор файловой группы или автоматический fallback на ФГ по умолчанию для каждой БД
💾 Автоматический бэкап DDL-скриптов индексов перед пересозданием
📜 Двойное логирование: текстовый файл + запись в SQL-таблицу DBATools.dbo.CheckProjectTables_log
🔍 Финальная валидация: проверка фактического расположения всех индексов после выполнения
⚠️ Безопасный пропуск индексов с FULLTEXT, XML и пространственными ключами
⚙️ Гибкие параметры выполнения: ONLINE, MAXDOP, SORT_IN_TEMPDB, DROP_EXISTING, FILLFACTOR
🧹 Автоочистка логов и бэкапов старше заданного количества дней
📊 Прозрачный контроль: прогресс-бар, цветной вывод, периодическое подтверждение каждые N индексов
📦 Зависимость только от dbatools (устанавливается одной командой)
<#
.SYNOPSIS
    Пересоздание индексов в новой файловой группе с полным логированием и резервным копированием
.DESCRIPTION
    Скрипт автоматически переносит индексы в указанную файловую группу с учётом всех ограничений:
    - Если TargetFileGroup пустой = используется ФГ по умолчанию для каждой БД
    - Пересоздаются ТОЛЬКО индексы, не находящиеся в целевой ФГ
    - Бэкап создаётся ТОЛЬКО для индексов, которые будут пересоздаваться
    - Индексы с ограничениями (полнотекстовый ключ, XML) пропускаются с предупреждением
    - Полное логирование в файл и в таблицу DBATools.dbo.CheckProjectTables_log
    - Автоматическая очистка старых логов и бэкапов (>7 дней)
    - Финальная проверка всех индексов после выполнения
#>

# ============================================================================
# ПАРАМЕТРЫ КОНФИГУРАЦИИ (ОТРЕДАКТИРУЙТЕ ПОД СВОИ НУЖДЫ)
# ============================================================================
$Config = @{
    # ----- Основные параметры подключения и цели -----
    SqlInstance            = "db"                     # Экземпляр SQL Server
    TargetFileGroup        = ""                             # Целевая файловая группа (пусто = ФГ по умолчанию для каждой БД)
    Databases              = @("dbname")  # Массив БД для обработки (если пусто = все, кроме исключённых)
    ExcludeDatabases       = @("master", "model", "msdb", "tempdb")   # БД, которые не трогаем
    Tables                 = @()                            # Ограничиться только этими таблицами (если не пусто)

    # ----- Режимы работы -----
    RecreateUnconditionally = $false                        # Пересоздавать ВСЕ индексы, даже если уже в целевой ФГ
    NonClusteredOnly       = $false                         # Только некластерные индексы (кластерные не трогать)
    PreviewOnly            = $false                         # Только сгенерировать SQL-скрипты, не выполнять
    ProcessClusteredFirst  = $true                          # Сначала обрабатывать кластерные индексы (рекомендуется)

    # ----- Параметры выполнения команд CREATE/ALTER INDEX -----
    IndexOnline            = $true                          # ONLINE = ON (минимальная блокировка)
    IndexMaxDop            = 24                             # MAXDOP = ... (0 = автоматически)
    IndexSortInTempdb      = $true                          # SORT_IN_TEMPDB = ON
    IndexDropExisting      = $true                          # DROP_EXISTING = ON (для пересоздания индекса)
    DefaultFillFactor      = 80                             # Значение fill factor по умолчанию (используется, если в БД не задан свой)

    # ----- Таймауты -----
    CommandTimeoutSeconds  = 3600                           # Таймаут выполнения одной команды в секундах

    # ----- Пути и логирование -----
    LogPath                = "C:\temp\IndexRebuild"         # Папка для логов выполнения
    LogRetentionDays       = 7                              # Через сколько дней удалять старые файлы логов
    BackupIndexes          = $true                          # Создавать резервные копии (SQL-скрипты) перед пересозданием
    BackupPath             = "C:\temp\IndexRebuild\Backups" # Папка для бэкапов скриптов индексов
    BackupRetentionDays    = 7                              # Через сколько дней удалять старые бэкапы
    LogDatabase            = "DBATools"                     # База данных для системного лога
    LogTable               = "dbo.CheckProjectTables_log"   # Таблица в LogDatabase для логирования

    # ----- Поведение во время выполнения -----
    ConfirmationInterval   = 5                             # Запрашивать подтверждение каждые N индексов (0 = никогда не спрашивать)
    SkipIndexesWithConstraints = $true                      # Пропускать индексы, связанные с полнотекстовым, XML, пространственным ключом
    ShowProgressBar        = $true                          # Показывать прогресс-бар в консоли
    UseColoredOutput       = $true                          # Использовать цветной вывод в консоли
}

# ============================================================================
# ФУНКЦИЯ ЛОГИРОВАНИЯ С ЦВЕТОВЫМ ВЫВОДОМ
# ============================================================================
function Write-Log {
    param(
        [Parameter(Mandatory=$true)]
        [string]$Message,
        [ValidateSet("INFO", "WARN", "ERROR", "SUCCESS")]
        [string]$Level = "INFO",
        [string]$LogFile
    )
    
    $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    $logEntry = "[$timestamp] [$Level] $Message"
    
    if ($Config.UseColoredOutput) {
        $color = switch ($Level) {
            "INFO"    { "Cyan" }
            "WARN"    { "Yellow" }
            "ERROR"   { "Red" }
            "SUCCESS" { "Green" }
            default   { "White" }
        }
        Write-Host $logEntry -ForegroundColor $color
    } else {
        Write-Host $logEntry
    }
    
    if ($LogFile) {
        try {
            $logEntry | Out-File -FilePath $LogFile -Append -Encoding utf8 -ErrorAction Stop
        } catch {
            Write-Warning "Не удалось записать в лог-файл: $_"
        }
    }
}

# ============================================================================
# ИНИЦИАЛИЗАЦИЯ ЛОГИРОВАНИЯ И ОЧИСТКА СТАРЫХ ФАЙЛОВ
# ============================================================================
function Initialize-Logging {
    param([string]$BasePath)
    
    if (-not (Test-Path $BasePath)) {
        New-Item -Path $BasePath -ItemType Directory -Force | Out-Null
        Write-Log "Создана директория для логов: $BasePath" -Level "INFO"
    }
    
    $cutoffDate = (Get-Date).AddDays(-$Config.LogRetentionDays)
    $oldLogs = Get-ChildItem -Path $BasePath -Filter "*.log" -File | 
               Where-Object { $_.LastWriteTime -lt $cutoffDate }
    
    if ($oldLogs.Count -gt 0) {
        Write-Log "Очистка старых логов (старше $($Config.LogRetentionDays) дней)..." -Level "INFO"
        $deletedCount = 0
        foreach ($log in $oldLogs) {
            try {
                Remove-Item -Path $log.FullName -Force -ErrorAction Stop
                $deletedCount++
            } catch {
                Write-Log "Не удалось удалить $($_.Name): $_" -Level "WARN"
            }
        }
        Write-Log "Удалено $deletedCount старых лог-файлов" -Level "INFO"
    }
    
    $logFileName = "IndexRebuild_$(Get-Date -Format 'yyyyMMdd_HHmmss').log"
    $logFilePath = Join-Path -Path $BasePath -ChildPath $logFileName
    
    Write-Log "Логирование в файл: $logFilePath" -Level "INFO" -LogFile $logFilePath
    return $logFilePath
}

# ============================================================================
# ОЧИСТКА СТАРЫХ БЭКАПОВ ИНДЕКСОВ
# ============================================================================
function Cleanup-OldBackups {
    param(
        [string]$BackupPath,
        [int]$RetentionDays,
        [string]$LogFile
    )
    
    if (-not (Test-Path $BackupPath)) {
        Write-Log "Папка бэкапов не найдена: $BackupPath" -Level "WARN" -LogFile $LogFile
        return
    }
    
    $cutoffDate = (Get-Date).AddDays(-$RetentionDays)
    $oldBackups = Get-ChildItem -Path $BackupPath -Filter "*.sql" -File | 
                  Where-Object { $_.LastWriteTime -lt $cutoffDate }
    
    if ($oldBackups.Count -gt 0) {
        Write-Log "Очистка старых бэкапов (старше $RetentionDays дней)..." -Level "INFO" -LogFile $LogFile
        $deletedCount = 0
        foreach ($backup in $oldBackups) {
            try {
                Remove-Item -Path $backup.FullName -Force -ErrorAction Stop
                $deletedCount++
                Write-Log "Удалён бэкап: $($backup.Name)" -Level "INFO" -LogFile $LogFile
            } catch {
                Write-Log "Не удалось удалить бэкап $($backup.Name): $_" -Level "WARN" -LogFile $LogFile
            }
        }
        Write-Log "Удалено $deletedCount старых бэкапов" -Level "INFO" -LogFile $LogFile
    } else {
        Write-Log "Старые бэкапы для удаления не найдены" -Level "INFO" -LogFile $LogFile
    }
}

# ============================================================================
# РЕЗЕРВНОЕ КОПИРОВАНИЕ ИНДЕКСОВ
# ============================================================================
function Backup-Indexes {
    param(
        [string]$SqlInstance,
        [hashtable]$IndexesToBackup,
        [string]$BackupPath,
        [string]$LogFile,
        [string]$LogDatabase,
        [string]$LogTable
    )
    
    Write-Log "Начало создания резервных копий индексов..." -Level "INFO" -LogFile $LogFile
    
    if (-not (Test-Path $BackupPath)) {
        New-Item -Path $BackupPath -ItemType Directory -Force | Out-Null
    }
    
    $totalBackups = 0
    $server = $null
    
    try {
        $server = Connect-DbaInstance -SqlInstance $SqlInstance -ErrorAction Stop
    } catch {
        Write-Log "Не удалось подключиться для логирования бэкапов: $_" -Level "ERROR" -LogFile $LogFile
    }
    
    foreach ($dbName in $IndexesToBackup.Keys) {
        $indexes = $IndexesToBackup[$dbName]
        if (-not $indexes -or $indexes.Count -eq 0) {
            Write-Log "В БД $dbName нет индексов для бэкапа" -Level "INFO" -LogFile $LogFile
            continue
        }
        
        Write-Log "Создание бэкапа для $($indexes.Count) индексов в БД: $dbName" -Level "INFO" -LogFile $LogFile
        
        foreach ($idx in $indexes) {
            try {
                $createCommand = ""
                
                if ($idx.is_primary_key -or $idx.is_unique_constraint) {
                    $constraintType = if ($idx.is_primary_key) { "PRIMARY KEY" } else { "UNIQUE" }
                    $createCommand = "ALTER TABLE [$($idx.SchemaName)].[$($idx.TableName)] " +
                                     "ADD CONSTRAINT [$($idx.IndexName)] $constraintType $($idx.IndexType)($($idx.KeyColumns))"
                    if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) {
                        $createCommand += " INCLUDE ($($idx.IncludedColumns))"
                    }
                    if ($idx.has_filter) {
                        $createCommand += " WHERE $($idx.filter_definition)"
                    }
                    if ($idx.fill_factor -gt 0 -and $idx.fill_factor -ne $Config.DefaultFillFactor) {
                        $createCommand += " WITH (FILLFACTOR = $($idx.fill_factor))"
                    }
                    $createCommand += " ON [$($idx.FileGroupName)];"
                } else {
                    $isUnique = if ($idx.is_unique) { "UNIQUE " } else { "" }
                    $createCommand = "CREATE ${isUnique}$($idx.IndexType) INDEX [$($idx.IndexName)] " +
                                     "ON [$($idx.SchemaName)].[$($idx.TableName)]($($idx.KeyColumns))"
                    if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) {
                        $createCommand += " INCLUDE ($($idx.IncludedColumns))"
                    }
                    if ($idx.has_filter) {
                        $createCommand += " WHERE $($idx.filter_definition)"
                    }
                    if ($idx.fill_factor -gt 0 -and $idx.fill_factor -ne $Config.DefaultFillFactor) {
                        $createCommand += " WITH (FILLFACTOR = $($idx.fill_factor))"
                    }
                    $createCommand += " ON [$($idx.FileGroupName)];"
                }
                
                $safeIndexName = $idx.IndexName -replace '[\\/:*?"<>|]', '_'
                $backupFile = Join-Path -Path $BackupPath -ChildPath "${dbName}_${idx.SchemaName}_${idx.TableName}_${safeIndexName}_Backup_$(Get-Date -Format 'yyyyMMdd_HHmmss').sql"
                $createCommand | Out-File -FilePath $backupFile -Encoding utf8 -Force
                
                $totalBackups++
                Write-Log "✓ Бэкап сохранён: $backupFile" -Level "SUCCESS" -LogFile $LogFile
                
                if ($server -and $LogDatabase) {
                    try {
                        $logQuery = @"
INSERT INTO $LogTable 
(DatabaseName, SchemaName, TableName, IndexName, Command, CommandType, CommandSubType, StartTime)
VALUES (@db, @schema, @table, @index, @cmd, 'BACKUP', 'Backup', GETDATE());
"@
                        $null = Invoke-DbaQuery -SqlInstance $server -Database $LogDatabase -Query $logQuery -SqlParameter @{
                            db = $dbName; schema = $idx.SchemaName; table = $idx.TableName; index = $idx.IndexName; cmd = $createCommand
                        } -ErrorAction Stop
                    } catch {
                        Write-Log "Не удалось записать бэкап в лог БД: $_" -Level "WARN" -LogFile $LogFile
                    }
                }
            } catch {
                Write-Log "✗ Ошибка при создании бэкапа для индекса $($idx.IndexName): $_" -Level "ERROR" -LogFile $LogFile
            }
        }
    }
    
    Write-Log "Резервное копирование завершено. Создано $totalBackups файлов" -Level "SUCCESS" -LogFile $LogFile
    return $totalBackups
}

# ============================================================================
# ПРОВЕРКА ИНДЕКСОВ В ФАЙЛОВОЙ ГРУППЕ (ФИНАЛЬНАЯ ПРОВЕРКА)
# ============================================================================
function Check-IndexesInFileGroup {
    param(
        [string]$SqlInstance,
        [string[]]$Databases,
        [hashtable]$TargetFileGroups,
        [string]$LogFile
    )
    
    Write-Log "=== ФИНАЛЬНАЯ ПРОВЕРКА ИНДЕКСОВ ===" -Level "INFO" -LogFile $LogFile
    
    $totalIndexes = 0
    $indexesInTargetFG = 0
    $indexesNotInTargetFG = @()
    
    foreach ($dbName in $Databases) {
        $targetFG = $TargetFileGroups[$dbName]
        
        try {
            $checkQuery = @"
SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    ds.name AS FileGroupName,
    i.type_desc AS IndexType,
    i.is_primary_key,
    i.is_unique_constraint
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE i.index_id > 0
    AND i.is_disabled = 0
    AND t.is_ms_shipped = 0
    AND t.name <> 'sysdiagrams'
ORDER BY s.name, t.name, i.name;
"@
            $indexes = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $dbName -Query $checkQuery -ErrorAction Stop
            
            $dbTotal = $indexes.Count
            $dbInTarget = ($indexes | Where-Object { $_.FileGroupName -eq $targetFG }).Count
            $dbNotInTarget = $dbTotal - $dbInTarget
            
            Write-Log "БД: $dbName - Всего индексов: $dbTotal, В целевой ФГ ($targetFG): $dbInTarget, Не в целевой ФГ: $dbNotInTarget" -Level "INFO" -LogFile $LogFile
            
            $totalIndexes += $dbTotal
            $indexesInTargetFG += $dbInTarget
            
            if ($dbNotInTarget -gt 0) {
                $notInTarget = $indexes | Where-Object { $_.FileGroupName -ne $targetFG }
                foreach ($idx in $notInTarget) {
                    $indexesNotInTargetFG += [PSCustomObject]@{
                        DatabaseName = $dbName
                        SchemaName = $idx.SchemaName
                        TableName = $idx.TableName
                        IndexName = $idx.IndexName
                        CurrentFileGroup = $idx.FileGroupName
                        TargetFileGroup = $targetFG
                        IndexType = $idx.IndexType
                        IsPrimaryKey = $idx.is_primary_key
                        IsUniqueConstraint = $idx.is_unique_constraint
                    }
                }
            }
        } catch {
            Write-Log "Ошибка при проверке индексов в БД $dbName : $_" -Level "ERROR" -LogFile $LogFile
        }
    }
    
    Write-Log "=== ИТОГИ ПРОВЕРКИ ===" -Level "INFO" -LogFile $LogFile
    Write-Log "Всего индексов: $totalIndexes" -Level "INFO" -LogFile $LogFile
    Write-Log "Индексов в целевой ФГ: $indexesInTargetFG" -Level "INFO" -LogFile $LogFile
    Write-Log "Индексов НЕ в целевой ФГ: $($indexesNotInTargetFG.Count)" -Level "INFO" -LogFile $LogFile
    
    if ($indexesNotInTargetFG.Count -gt 0) {
        Write-Log "Список индексов, которые НЕ были перенесены в целевую ФГ:" -Level "WARN" -LogFile $LogFile
        $indexesNotInTargetFG | ForEach-Object {
            Write-Log "  $($_.DatabaseName).$($_.SchemaName).$($_.TableName).$($_.IndexName) - Текущая ФГ: $($_.CurrentFileGroup)" -Level "WARN" -LogFile $LogFile
        }
        $notInTargetFile = Join-Path -Path $Config.LogPath -ChildPath "IndexesNotInTargetFG_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"
        $indexesNotInTargetFG | Format-Table -AutoSize | Out-File -FilePath $notInTargetFile -Encoding utf8
        Write-Log "Список сохранён в файл: $notInTargetFile" -Level "WARN" -LogFile $LogFile
    } else {
        Write-Log "✓ ВСЕ индексы успешно перенесены в целевую файловую группу!" -Level "SUCCESS" -LogFile $LogFile
    }
    
    return $indexesNotInTargetFG
}

# ============================================================================
# ГЛАВНЫЙ СКРИПТ
# ============================================================================
$logFilePath = Initialize-Logging -BasePath $Config.LogPath
Write-Log "=== НАЧАЛО ПЕРЕСОЗДАНИЯ ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Сервер: $($Config.SqlInstance)" -Level "INFO" -LogFile $logFilePath
Write-Log "Целевая файловая группа: $(if ([string]::IsNullOrWhiteSpace($Config.TargetFileGroup)) { 'ФГ по умолчанию для каждой БД' } else { $Config.TargetFileGroup })" -Level "INFO" -LogFile $logFilePath
Write-Log "Базы данных: $($Config.Databases -join ', ')" -Level "INFO" -LogFile $logFilePath

if (-not (Get-Module -Name dbatools -ListAvailable)) {
    Write-Log "Модуль dbatools не установлен. Установите командой: Install-Module dbatools -Scope CurrentUser -Force" -Level "ERROR" -LogFile $logFilePath
    exit 1
}
Import-Module dbatools -ErrorAction Stop
Write-Log "✓ Модуль dbatools загружен" -Level "SUCCESS" -LogFile $logFilePath

try {
    $server = Connect-DbaInstance -SqlInstance $Config.SqlInstance -ErrorAction Stop
    Write-Log "✓ Подключено к: $($Config.SqlInstance)" -Level "SUCCESS" -LogFile $logFilePath
} catch {
    Write-Log "✗ Не удалось подключиться: $_" -Level "ERROR" -LogFile $logFilePath
    exit 1
}

$databasesToProcess = Get-DbaDatabase -SqlInstance $server -Status Normal | 
    Where-Object {
        ($Config.Databases.Count -eq 0 -or $_.Name -in $Config.Databases) -and 
        $_.Name -notin $Config.ExcludeDatabases -and
        $_.Name -notin @("master", "model", "msdb", "tempdb")
    }

if ($databasesToProcess.Count -eq 0) {
    Write-Log "Не найдено подходящих баз данных для обработки" -Level "WARN" -LogFile $logFilePath
    exit 0
}

Write-Log "Найдено баз данных для обработки: $($databasesToProcess.Count)" -Level "INFO" -LogFile $logFilePath
foreach ($db in $databasesToProcess) {
    Write-Log "  → $($db.Name)" -Level "INFO" -LogFile $logFilePath
}

$targetFileGroups = @{}
foreach ($db in $databasesToProcess) {
    if ([string]::IsNullOrWhiteSpace($Config.TargetFileGroup)) {
        $fgQuery = "SELECT name FROM sys.filegroups WHERE is_default = 1"
        try {
            $defaultFG = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query $fgQuery -ErrorAction Stop | Select-Object -ExpandProperty name
            $targetFileGroups[$db.Name] = $defaultFG
            Write-Log "БД $($db.Name): используется ФГ по умолчанию: $defaultFG" -Level "INFO" -LogFile $logFilePath
        } catch {
            Write-Log "Не удалось определить ФГ по умолчанию для БД $($db.Name): $_" -Level "ERROR" -LogFile $logFilePath
            exit 1
        }
    } else {
        $fgExists = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query "SELECT COUNT(*) AS Cnt FROM sys.filegroups WHERE name = @fg" -SqlParameter @{ fg = $Config.TargetFileGroup } -ErrorAction SilentlyContinue | Select-Object -ExpandProperty Cnt
        if ($fgExists -eq 0) {
            Write-Log "ФГ '$($Config.TargetFileGroup)' отсутствует в БД $($db.Name)" -Level "ERROR" -LogFile $logFilePath
            exit 1
        }
        $targetFileGroups[$db.Name] = $Config.TargetFileGroup
        Write-Log "БД $($db.Name): целевая ФГ: $($Config.TargetFileGroup)" -Level "INFO" -LogFile $logFilePath
    }
}

if ($Config.BackupIndexes) {
    Write-Log "=== ОЧИСТКА СТАРЫХ БЭКАПОВ ===" -Level "INFO" -LogFile $logFilePath
    Cleanup-OldBackups -BackupPath $Config.BackupPath -RetentionDays $Config.BackupRetentionDays -LogFile $logFilePath
}

Write-Log "Проверка таблицы логирования..." -Level "INFO" -LogFile $logFilePath
$checkTableQuery = @"
IF EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'dbo' AND t.name = 'CheckProjectTables_log')
    SELECT 1 AS ExistsFlag ELSE SELECT 0 AS ExistsFlag
"@
$logTableExists = $false
try {
    $result = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query $checkTableQuery -ErrorAction Stop
    $logTableExists = ($result.ExistsFlag -eq 1)
    if ($logTableExists) {
        Write-Log "✓ Таблица логирования найдена: $($Config.LogDatabase).$($Config.LogTable)" -Level "SUCCESS" -LogFile $logFilePath
    } else {
        Write-Log "Таблица логирования не найдена. Создаём..." -Level "WARN" -LogFile $logFilePath
        $createLogTableQuery = @"
CREATE TABLE $($Config.LogTable) (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DatabaseName SYSNAME NOT NULL, SchemaName SYSNAME NOT NULL, TableName SYSNAME NOT NULL, IndexName SYSNAME NULL,
    Command NVARCHAR(MAX) NOT NULL, CommandType VARCHAR(50) NOT NULL, CommandSubType VARCHAR(20) NOT NULL,
    StartTime DATETIME2 NOT NULL DEFAULT GETDATE(), StopTime DATETIME2 NULL,
    Result VARCHAR(20) NULL, ErrorNumber INT NULL, ErrorMessage NVARCHAR(MAX) NULL)
"@
        Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query $createLogTableQuery -ErrorAction Stop
        Write-Log "✓ Таблица логирования создана" -Level "SUCCESS" -LogFile $logFilePath
    }
} catch {
    Write-Log "Не удалось проверить/создать таблицу логирования: $_" -Level "WARN" -LogFile $logFilePath
    $Config.LogDatabase = $null
}

Write-Log "Сбор метаданных индексов..." -Level "INFO" -LogFile $logFilePath
$allIndexes = @{}
$indexesToRebuild = @{}
$indexesToBackup = @{}

foreach ($db in $databasesToProcess) {
    Write-Log "  → Сбор из БД: $($db.Name)" -Level "INFO" -LogFile $logFilePath
    $targetFG = $targetFileGroups[$db.Name]
    
    $tableFilter = if ($Config.Tables.Count -gt 0) { "AND t.name IN ($($Config.Tables.ForEach({ "'$_'" }) -join ','))" } else { "" }
    
    $query = @"
WITH IndexDetails AS (
    SELECT s.name AS SchemaName, t.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType,
           i.is_primary_key, i.is_unique_constraint, ds.name AS CurrentFileGroup, i.fill_factor, p.rows AS RowCounts,
           i.index_id, i.object_id, CASE WHEN i.type = 1 THEN 1 ELSE 0 END AS IsClustered,
           i.has_filter, i.filter_definition, i.is_unique,
           CASE WHEN EXISTS (SELECT 1 FROM sys.fulltext_indexes fti WHERE fti.object_id = i.object_id AND fti.unique_index_id = i.index_id) THEN 1
                WHEN EXISTS (SELECT 1 FROM sys.xml_indexes xi WHERE xi.object_id = i.object_id AND xi.index_id = i.index_id) THEN 1
                WHEN EXISTS (SELECT 1 FROM sys.spatial_indexes spi WHERE spi.object_id = i.object_id AND spi.index_id = i.index_id) THEN 1
                ELSE 0 END AS HasConstraints
    FROM sys.indexes i
    INNER JOIN sys.tables t ON i.object_id = t.object_id
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    WHERE i.index_id > 0 AND i.is_disabled = 0 AND t.is_ms_shipped = 0 AND t.name <> 'sysdiagrams'
          AND (@RecreateAll = 1 OR ds.name <> @TargetFG) AND (@NonClusteredOnly = 0 OR i.type_desc <> 'CLUSTERED')
          $tableFilter
),
IndexColumns AS (
    SELECT ic.object_id, ic.index_id,
        STUFF((SELECT ', ' + QUOTENAME(c.name) + CASE WHEN ic2.is_descending_key = 1 THEN ' DESC' ELSE '' END
               FROM sys.index_columns ic2 INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
               WHERE ic2.object_id = ic.object_id AND ic2.index_id = ic.index_id AND ic2.is_included_column = 0
               ORDER BY ic2.key_ordinal FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS KeyColumns,
        STUFF((SELECT ', ' + QUOTENAME(c.name)
               FROM sys.index_columns ic2 INNER JOIN sys.columns c ON ic2.object_id = c.object_id AND ic2.column_id = c.column_id
               WHERE ic2.object_id = ic.object_id AND ic2.index_id = ic.index_id AND ic2.is_included_column = 1
               ORDER BY ic2.index_column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IncludedColumns
    FROM sys.index_columns ic GROUP BY ic.object_id, ic.index_id
)
SELECT DB_NAME() AS DatabaseName, id.SchemaName, id.TableName, id.IndexName, id.IndexType,
       id.is_primary_key, id.is_unique_constraint, id.CurrentFileGroup, id.fill_factor, id.RowCounts,
       id.IsClustered, ic.KeyColumns, ic.IncludedColumns, id.has_filter, id.filter_definition,
       id.is_unique, id.HasConstraints
FROM IndexDetails id INNER JOIN IndexColumns ic ON id.object_id = ic.object_id AND id.index_id = ic.index_id
WHERE ic.KeyColumns IS NOT NULL
ORDER BY id.IsClustered DESC, id.SchemaName, id.TableName, id.IndexName
"@
    try {
        $results = Invoke-DbaQuery -SqlInstance $server -Database $db.Name -Query $query -SqlParameter @{
            RecreateAll = [int]($Config.RecreateUnconditionally)
            TargetFG = $targetFG
            NonClusteredOnly = [int]($Config.NonClusteredOnly)
        } -ErrorAction Stop
        
        $dbIndexes = @()
        foreach ($row in $results) {
            $dbIndexes += [PSCustomObject]@{
                DatabaseName=$db.Name; SchemaName=$row.SchemaName; TableName=$row.TableName; IndexName=$row.IndexName
                IndexType=$row.IndexType; IsPrimaryKey=$row.is_primary_key; IsUniqueConstraint=$row.is_unique_constraint
                IsUnique=$row.is_unique; CurrentFileGroup=$row.CurrentFileGroup; FillFactor=$row.fill_factor
                RowCounts=$row.RowCounts; IsClustered=$row.IsClustered; KeyColumns=$row.KeyColumns
                IncludedColumns=$row.IncludedColumns; HasFilter=$row.has_filter; FilterDefinition=$row.filter_definition
                HasConstraints=$row.HasConstraints
            }
        }
        $allIndexes[$db.Name] = $dbIndexes
        $indexesToRebuild[$db.Name] = $dbIndexes
        $indexesToBackup[$db.Name] = $dbIndexes
        Write-Log "Найдено индексов для пересоздания в БД $($db.Name): $($dbIndexes.Count)" -Level "INFO" -LogFile $logFilePath
    } catch {
        Write-Log "Ошибка при сборе индексов из $($db.Name): $_" -Level "ERROR" -LogFile $logFilePath
    }
}

$totalIndexesToRebuild = ($indexesToRebuild.Values | ForEach-Object { $_.Count } | Measure-Object -Sum).Sum
if ($totalIndexesToRebuild -eq 0) {
    Write-Log "Не найдено индексов для пересоздания (все уже в правильной файловой группе)" -Level "WARN" -LogFile $logFilePath
    exit 0
}
Write-Log "✓ Всего индексов для пересоздания: $totalIndexesToRebuild" -Level "SUCCESS" -LogFile $logFilePath

if ($Config.BackupIndexes) {
    Write-Log "=== СОЗДАНИЕ РЕЗЕРВНЫХ КОПИЙ ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
    $backupCount = Backup-Indexes -SqlInstance $Config.SqlInstance -IndexesToBackup $indexesToBackup -BackupPath $Config.BackupPath -LogFile $logFilePath -LogDatabase $Config.LogDatabase -LogTable $Config.LogTable
    Write-Log "===========================================" -Level "INFO" -LogFile $logFilePath
}

Write-Log "Генерация команд пересоздания..." -Level "INFO" -LogFile $logFilePath
$commands = foreach ($db in $databasesToProcess) {
    $targetFG = $targetFileGroups[$db.Name]
    $dbIndexes = $indexesToRebuild[$db.Name]
    if (-not $dbIndexes) { continue }
    foreach ($idx in $dbIndexes) {
        $includePart = if (![string]::IsNullOrWhiteSpace($idx.IncludedColumns)) { " INCLUDE ($($idx.IncludedColumns))" } else { "" }
        $filterPart = if ($idx.HasFilter -and $idx.FilterDefinition) { " WHERE $($idx.FilterDefinition)" } else { "" }
        
        $skipIndex = $false
        if ($Config.SkipIndexesWithConstraints -and $idx.HasConstraints -eq 1) {
            $createCommand = "-- ИНДЕКС С ОГРАНИЧЕНИЯМИ (полнотекстовый ключ, XML или пространственный индекс)`n-- Требуется ручное пересоздание`nPRINT 'Индекс $($idx.IndexName) имеет ограничения. Пропускаем автоматическое пересоздание.';"
            $cmdType = "SKIP (HAS CONSTRAINTS)"
            $skipIndex = $true
        } else {
            $options = @()
            if ($Config.IndexOnline) { $options += "ONLINE = ON" } else { $options += "ONLINE = OFF" }
            if ($Config.IndexSortInTempdb) { $options += "SORT_IN_TEMPDB = ON" } else { $options += "SORT_IN_TEMPDB = OFF" }
            if ($Config.IndexDropExisting) { $options += "DROP_EXISTING = ON" }
            if ($Config.IndexMaxDop -gt 0) { $options += "MAXDOP = $($Config.IndexMaxDop)" }
            if ($idx.FillFactor -gt 0 -and $idx.FillFactor -ne $Config.DefaultFillFactor) { $options += "FILLFACTOR = $($idx.FillFactor)" }
            
            $withClause = if ($options.Count -gt 0) { " WITH (" + ($options -join ", ") + ")" } else { "" }
            $isUnique = if ($idx.IsUnique -or $idx.IsPrimaryKey -or $idx.IsUniqueConstraint) { "UNIQUE " } else { "" }
            $createCommand = "CREATE ${isUnique}$($idx.IndexType) INDEX [$($idx.IndexName)] " +
                             "ON [$($idx.SchemaName)].[$($idx.TableName)]($($idx.KeyColumns))" +
                             $includePart + $filterPart + $withClause + " ON [$targetFG];"
            $cmdType = "CREATE INDEX"
        }
        
        [PSCustomObject]@{
            DatabaseName=$idx.DatabaseName; SchemaName=$idx.SchemaName; TableName=$idx.TableName; IndexName=$idx.IndexName
            CurrentFileGroup=$idx.CurrentFileGroup; TargetFileGroup=$targetFG; Command=$createCommand
            CommandType=$cmdType; CommandSubType=if($skipIndex){"Skip"}else{"Create"}; EstimatedRows=$idx.RowCounts
            IsClustered=$idx.IsClustered; HasConstraints=$idx.HasConstraints; SkipIndex=$skipIndex
        }
    }
}

Write-Log "✓ Сгенерировано команд: $($commands.Count)" -Level "SUCCESS" -LogFile $logFilePath

Write-Log "=== ПЛАН ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Всего индексов для пересоздания: $($commands.Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "Кластерных: $($commands.Where({ $_.IsClustered -eq $true }).Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "Некластерных: $($commands.Where({ $_.IsClustered -eq $false }).Count)" -Level "INFO" -LogFile $logFilePath
Write-Log "С ограничениями (будут пропущены): $($commands.Where({ $_.HasConstraints -eq 1 }).Count)" -Level "INFO" -LogFile $logFilePath

if ($Config.PreviewOnly) {
    Write-Log "[РЕЖИМ ПРЕДПРОСМОТРА] Команды НЕ будут выполнены" -Level "WARN" -LogFile $logFilePath
    $outputFile = Join-Path -Path $Config.LogPath -ChildPath "IndexRebuildCommands_$(Get-Date -Format 'yyyyMMdd_HHmmss').sql"
    $commands | ForEach-Object { "USE [$($_.DatabaseName)];`n$($_.Command)`nGO`n" } | Out-File -FilePath $outputFile -Encoding utf8
    Write-Log "Команды сохранены в файл: $outputFile" -Level "SUCCESS" -LogFile $logFilePath
    exit 0
}

Write-Host "`n" ("!" * 80) -ForegroundColor Red
Write-Host "ВНИМАНИЕ: Будут пересозданы $($commands.Count) индексов!" -ForegroundColor Red
Write-Host "Это может занять значительное время и вызвать блокировки." -ForegroundColor Red
Write-Host ("!" * 80) -ForegroundColor Red
Write-Host "`nПродолжить выполнение? (YES/NO)" -ForegroundColor Yellow -NoNewline
$confirmation = Read-Host
if ($confirmation -notlike "YES*") {
    Write-Log "Операция отменена пользователем" -Level "WARN" -LogFile $logFilePath
    exit 0
}

# ============================================================================
# ВЫПОЛНЕНИЕ С ЛОГИРОВАНИЕМ И ПЕРИОДИЧЕСКИМ ПОДТВЕРЖДЕНИЕМ
# ============================================================================
Write-Log "=== НАЧАЛО ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath

$total          = $commands.Count
$current        = 0
$stopwatch      = [System.Diagnostics.Stopwatch]::StartNew()
$successCount   = 0
$failCount      = 0
$skippedCount   = 0

# Сортируем команды в соответствии с параметром ProcessClusteredFirst
if ($Config.ProcessClusteredFirst) {
    $sortedCommands = $commands | Sort-Object -Property @{Expression={$_.IsClustered}; Descending=$true}, DatabaseName, SchemaName, TableName
} else {
    $sortedCommands = $commands | Sort-Object DatabaseName, SchemaName, TableName, IndexName
}

foreach ($cmd in $sortedCommands) {
    $current++
    $progress = [math]::Round(($current / $total) * 100, 1)
    
    if ($Config.ShowProgressBar) {
        Write-Progress -Activity "Пересоздание индексов" `
            -Status "БД: $($cmd.DatabaseName) | Индекс: $($cmd.IndexName)" `
            -PercentComplete $progress `
            -CurrentOperation "[$current/$total] $progress%"
    }
    
    Write-Log "[$current/$total] Обработка: $($cmd.IndexName) (БД: $($cmd.DatabaseName))" -Level "INFO" -LogFile $logFilePath
    
    $logId = $null
    if ($Config.LogDatabase) {
        try {
            $logResult = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query @"
INSERT INTO $($Config.LogTable) (DatabaseName, SchemaName, TableName, IndexName, Command, CommandType, CommandSubType, StartTime)
VALUES (@db, @schema, @table, @index, @cmd, @cmdType, @cmdSubType, GETDATE());
SELECT CAST(SCOPE_IDENTITY() AS INT) AS Id;
"@ -SqlParameter @{
                db=$cmd.DatabaseName; schema=$cmd.SchemaName; table=$cmd.TableName; index=$cmd.IndexName
                cmd=$cmd.Command; cmdType=$cmd.CommandType; cmdSubType=$cmd.CommandSubType
            } -ErrorAction Stop
            $logId = $logResult.Id
        } catch {
            Write-Log "Не удалось записать в лог БД: $_" -Level "WARN" -LogFile $logFilePath
        }
    }
    
    try {
        if ($cmd.SkipIndex -eq $true) {
            $result = "Skipped"; $errorNum = 0; $errorMsg = "Индекс имеет ограничения"
            $skippedCount++
            Write-Log "  ⚠ $($cmd.IndexName) ($($cmd.DatabaseName)) - ПРОПУЩЕН" -Level "WARN" -LogFile $logFilePath
        } else {
            Invoke-DbaQuery -SqlInstance $server -Database $cmd.DatabaseName -Query $cmd.Command -QueryTimeout $Config.CommandTimeoutSeconds -ErrorAction Stop
            $result = "Success"; $errorNum = $null; $errorMsg = $null
            $successCount++
            Write-Log "  ✓ $($cmd.IndexName) ($($cmd.DatabaseName))" -Level "SUCCESS" -LogFile $logFilePath
        }
    } catch {
        $result = "Fail"
        $errorNum = if ($_.Exception.InnerException -and $_.Exception.InnerException.Number) { $_.Exception.InnerException.Number } else { 0 }
        $errorMsg = $_.Exception.Message
        $failCount++
        Write-Log "  ✗ $($cmd.IndexName) ($($cmd.DatabaseName)): $errorMsg" -Level "ERROR" -LogFile $logFilePath
    }
    
    if ($Config.LogDatabase -and $logId) {
        try {
            $null = Invoke-DbaQuery -SqlInstance $server -Database $Config.LogDatabase -Query @"
UPDATE $($Config.LogTable) SET StopTime = GETDATE(), Result = @res, ErrorNumber = @errNum, ErrorMessage = @errMsg WHERE Id = @id;
"@ -SqlParameter @{ res=$result; errNum=$errorNum; errMsg=$errorMsg; id=$logId } -ErrorAction Stop
        } catch {
            Write-Log "Не удалось обновить лог БД: $_" -Level "WARN" -LogFile $logFilePath
        }
    }

    # ЗАПРОС ПОДТВЕРЖДЕНИЯ КАЖДЫЕ N ИНДЕКСОВ (если интервал > 0)
    if ($Config.ConfirmationInterval -gt 0 -and $current % $Config.ConfirmationInterval -eq 0 -and $current -lt $total) {
        Write-Host "`n--- Обработано $current из $total индексов ($progress%) ---" -ForegroundColor Cyan
        Write-Host "Продолжить выполнение? (Y/N): " -ForegroundColor Yellow -NoNewline
        $userChoice = Read-Host
        
        if ($userChoice -notmatch '^[YyДд]') {
            Write-Log "Выполнение остановлено пользователем после $current индексов" -Level "WARN" -LogFile $logFilePath
            Write-Host "⛔ Скрипт остановлен по запросу пользователя." -ForegroundColor Red
            break
        }
        Write-Host "Продолжаем выполнение..." -ForegroundColor Green
    }
}

$stopwatch.Stop()
if ($Config.ShowProgressBar) {
    Write-Progress -Activity "Завершено" -Completed
}

# Итоговая статистика
Write-Log "=== ИТОГИ ВЫПОЛНЕНИЯ ===" -Level "INFO" -LogFile $logFilePath
Write-Log "Всего операций: $total" -Level "INFO" -LogFile $logFilePath
Write-Log "Успешно: $successCount ($([math]::Round($successCount/[math]::Max(1,$total)*100, 2))%)" -Level "SUCCESS" -LogFile $logFilePath
Write-Log "Ошибок: $failCount" -Level $(if ($failCount -gt 0) { "ERROR" } else { "SUCCESS" }) -LogFile $logFilePath
Write-Log "Пропущено (с ограничениями): $skippedCount" -Level "WARN" -LogFile $logFilePath
Write-Log "Время выполнения: $($stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -Level "INFO" -LogFile $logFilePath

$successColor = if ($successCount -eq $total) { "Green" } else { "Yellow" }
$failColor    = if ($failCount -gt 0) { "Red" } else { "Green" }

Write-Host "`n" ("=" * 80) -ForegroundColor Green
Write-Host "ИТОГИ ВЫПОЛНЕНИЯ" -ForegroundColor Green
Write-Host ("=" * 80) -ForegroundColor Green
Write-Host "Всего операций: $total" -ForegroundColor Cyan
Write-Host "Успешно: $successCount ($([math]::Round($successCount/[math]::Max(1,$total)*100, 2))%)" -ForegroundColor $successColor
Write-Host "Ошибок: $failCount" -ForegroundColor $failColor
Write-Host "Пропущено (с ограничениями): $skippedCount" -ForegroundColor Yellow
Write-Host "Время выполнения: $($stopwatch.Elapsed.ToString('hh\:mm\:ss'))" -ForegroundColor Cyan
Write-Host "Лог-файл: $logFilePath" -ForegroundColor Cyan

# Финальная проверка всех индексов
Write-Log "=== ФИНАЛЬНАЯ ПРОВЕРКА ИНДЕКСОВ ===" -Level "INFO" -LogFile $logFilePath
$indexesNotInTarget = Check-IndexesInFileGroup -SqlInstance $Config.SqlInstance -Databases $Config.Databases -TargetFileGroups $targetFileGroups -LogFile $logFilePath

if ($indexesNotInTarget.Count -gt 0) {
    Write-Host "`n⚠ Найдены индексы, которые НЕ были перенесены в целевую файловую группу!" -ForegroundColor Yellow
    Write-Host "Смотрите детали в лог-файле: $logFilePath" -ForegroundColor Yellow
} else {
    Write-Host "`n✓ ВСЕ индексы успешно перенесены в целевую файловую группу!" -ForegroundColor Green
}

Write-Log "Скрипт завершён" -Level "INFO" -LogFile $logFilePath
Write-Host "`nСкрипт завершён. Подробности в лог-файле: $logFilePath" -ForegroundColor Green
Write-Host ("=" * 80) -ForegroundColor Green
Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.
Как пересоздать индексы mssql в новую файловую группу с помощью PowerShell и dbatools с логированием и бэкапом ver 2

🔑 Ключевые возможности

Similar Posts:

Добавить комментарий Отменить ответ
