Перейти к содержимому

Как копировать данных между SQL Server с фильтром по дате при помощи PowerShell и dbatools

Задача: перенести данные из таблицы на одном сервере SQL Server на другой, оставив только записи за последние N месяцев, с автоматическим созданием структуры таблицы на приёмнике.

Решение: PowerShell 7 + модуль dbatools. Скрипт сам определяет столбцы с датой, предлагает выбрать нужный (если их несколько), формирует фильтр и выполняет высокопроизводительное копирование через SqlBulkCopy.

Возможности скрипта

  • ✅ Автоматическое создание таблицы на целевом сервере (если её нет).

  • ✅ Очистка целевой таблицы перед вставкой (опционально).

  • ✅ Сохранение значений IDENTITY (опционально).

  • ✅ Выбор столбца для фильтрации, если в таблице несколько дата-столбцов.

  • ✅ Настройка периода (например, последние 3, 6, 12 месяцев).

  • ✅ Контроль производительности через размер пакета (BatchSize) и таймауты.

  • ✅ Подробный вывод хода работы.

Требования

  • PowerShell 7 или новее.

  • Модуль dbatools

Как это работает

  1. Подключение к источнику через Invoke-DbaQuery.

  2. Поиск столбцов с типами date, datetime, datetime2, smalldatetime в указанной таблице.

    • Если найден один – он используется автоматически.

    • Если несколько – выводится интерактивное меню с номерами столбцов, пользователь вводит нужный.

Преимущества подхода

  • Скорость – SqlBulkCopy работает на порядки быстрее обычной вставки через INSERT.

  • Безопасность – можно сначала запустить с -WhatIf для имитации.

  • Масштабируемость – обрабатывает миллионы строк без перегрузки памяти.

  • Гибкость – параметры легко адаптировать под любую таблицу и период.

<#
.SYNOPSIS
    Копирует данные из таблицы на сервере-источнике в таблицу на сервере-приёмнике.
    Данные фильтруются за последние N месяцев по выбранному столбцу с датой.
    Если столбцов с датой несколько – скрипт предложит выбрать нужный.
#>

# ===================== НАСТРОЙКИ (меняйте под себя) =====================
$sourceInstance     = "DB1.adminbd.RU"          # Экземпляр-источник
$sourceDatabase     = "hds"                # База-источник
$sourceTable        = "dbo.t_Exte"            # Таблица-источник (включая схему)

$destInstance       = "db2.adminbd.ru"          # Экземпляр-приёмник
$destDatabase       = "whds"                # База-приёмник
$destTable          = "dbo.t_Exte"            # Таблица-приёмник (включая схему)

$monthsBack         = 3                            # За сколько месяцев копировать

# Параметры Copy-DbaDbTableData (можно менять)
$truncateDest       = $true                        # Очистить целевую таблицу перед вставкой
$keepIdentity       = $true                        # Сохранить значения identity-столбцов
$batchSize          = 50000                        # Строк в одном пакете
$bulkCopyTimeout    = 0                            # Таймаут (0 = бесконечно)
$autoCreateTable    = $true                        # Создать таблицу, если её нет
# ========================================================================

# Импортируем модуль dbatools (если установлен)
if (-not (Get-Module -ListAvailable -Name dbatools)) {
    Write-Error "Модуль dbatools не найден. Установите: Install-Module dbatools -Force"
    exit 1
}
Import-Module dbatools -Force

# ----- 1. Определяем столбцы с датой в исходной таблице -----
$dateColumnsQuery = @"
SELECT c.name AS ColumnName
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('$sourceTable')
AND t.name IN ('date', 'datetime', 'datetime2', 'smalldatetime')
ORDER BY c.column_id
"@

try {
    $dateColumns = Invoke-DbaQuery -SqlInstance $sourceInstance -Database $sourceDatabase -Query $dateColumnsQuery
    if ($dateColumns.Count -eq 0) {
        Write-Error "В таблице $sourceTable не найдено столбцов с типами даты/времени."
        exit 1
    }
    elseif ($dateColumns.Count -eq 1) {
        $selectedDateColumn = $dateColumns[0].ColumnName
        Write-Host "Найден столбец с датой: '$selectedDateColumn'" -ForegroundColor Green
    }
    else {
        Write-Host "Найдено несколько столбцов с датой:" -ForegroundColor Yellow
        for ($i = 0; $i -lt $dateColumns.Count; $i++) {
            Write-Host "[$i] $($dateColumns[$i].ColumnName)"
        }
        $choice = Read-Host "Введите номер столбца, по которому нужно фильтровать данные"
        if ($choice -match '^\d+$' -and $choice -ge 0 -and $choice -lt $dateColumns.Count) {
            $selectedDateColumn = $dateColumns[$choice].ColumnName
            Write-Host "Выбран столбец: '$selectedDateColumn'" -ForegroundColor Green
        } else {
            Write-Error "Неверный выбор."
            exit 1
        }
    }
}
catch {
    Write-Error "Ошибка при получении списка столбцов: $_"
    exit 1
}

# ----- 2. Формируем запрос с фильтром по дате -----
$query = @"
SELECT *
FROM $sourceTable
WHERE $selectedDateColumn >= DATEADD(MONTH, -$monthsBack, GETDATE())
"@

# ----- 3. Выполняем копирование -----
Write-Host "Источник: $sourceInstance.$sourceDatabase.$sourceTable" -ForegroundColor Cyan
Write-Host "Приёмник: $destInstance.$destDatabase.$destTable" -ForegroundColor Cyan
Write-Host "Фильтр: $selectedDateColumn >= сегодня минус $monthsBack месяцев" -ForegroundColor Yellow

$copyParams = @{
    SqlInstance         = $sourceInstance
    Destination         = $destInstance
    Database            = $sourceDatabase
    DestinationDatabase = $destDatabase
    Table               = $sourceTable
    DestinationTable    = $destTable
    Query               = $query
    AutoCreateTable     = $autoCreateTable
    Truncate            = $truncateDest
    KeepIdentity        = $keepIdentity
    BatchSize           = $batchSize
    BulkCopyTimeout     = $bulkCopyTimeout
    EnableException     = $true
}

try {
    Copy-DbaDbTableData @copyParams
    Write-Host "✅ Копирование успешно завершено!" -ForegroundColor Green
}
catch {
    Write-Error "❌ Ошибка: $_"
    exit 1
}

Заключение

Скрипт решает типовую задачу переноса «среза» данных между серверами. Благодаря dbatools он остаётся компактным, надёжным и лёгким в настройке. Подходит как для разовых миграций, так и для регулярных обновлений (например, в заданиях планировщика).

Similar Posts:

Метки:

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

Яндекс.Метрика