SQL: Скрипт резервного копирования и восстановление базы данных

20 май 2025, Вторник

143

0 0

Резервное копирование и восстановление базы данных средствами PowerShell

Ту же задачу можно решить средствами PowerShell, ниже представлены два скрипта на PowerShell: первый для выполнения резервного копирования базы данных MS SQL, а второй для восстановления базы данных из резервной копии на тестовом сервере. Эти скрипты используют модуль SqlServer, который предоставляет командлеты для работы с SQL Server.

Скрипт для выполнения резервного копирования БД
В современных версиях SQL сервер и актуальных версиях модуля PowerShell SqlServer есть командлет Backup-SqlDatabase — он позволяет сделать многое для автоматизации резервного копирования баз данных MS SQL Server, поддерживает различные сценарии, такие как полное и разностное резервное копирование, сжатие, проверка целостности и работа с несколькими базами данных. Эти примеры помогут вам адаптировать скрипты под свои потребности и интегрировать их в рабочие процессы.

Его синтаксис

Показать / Скрыть текст

Backup-SqlDatabase
      [-BackupContainer <String>]
      [-MirrorDevices <BackupDeviceList[]>]
      [-BackupAction <BackupActionType>]
      [-BackupSetName <String>]
      [-BackupSetDescription <String>]
      [-CompressionOption <BackupCompressionOptions>]
      [-CopyOnly]
      [-ExpirationDate <DateTime>]
      [-FormatMedia]
      [-Incremental]
      [-Initialize]
      [-LogTruncationType <BackupTruncateLogType>]
      [-MediaDescription <String>]
      [-RetainDays <Int32>]
      [-SkipTapeHeader]
      [-UndoFileName <String>]
      [-EncryptionOption <BackupEncryptionOptions>]
      [-Database] <String>
      [-Path <String[]>]
      [[-BackupFile] <String[]>]
      [-SqlCredential <PSObject>]
      [-BackupDevice <BackupDeviceItem[]>]
      [-PassThru]
      [-Checksum]
      [-ContinueAfterError]
      [-NoRewind]
      [-Restart]
      [-UnloadTapeAfter]
      [-NoRecovery]
      [-DatabaseFile <String[]>]
      [-DatabaseFileGroup <String[]>]
      [-BlockSize <Int32>]
      [-BufferCount <Int32>]
      [-MaxTransferSize <Int32>]
      [-MediaName <String>]
      [-Script]
      [-WhatIf]
      [-Confirm]
      [<CommonParameters>]

Простой пример для резервного копирования базы данных:

Backup-SqlDatabase -ServerInstance "ServerName" -Database "AdventureWorks" -BackupFile "C:\Backup\AdventureWorks_Full.bak" -Type Full

тот же пример, но с параметрами:

# Параметры
$ServerName = "ServerName"               # Имя сервера SQL Server
$DatabaseName = "AdventureWorks"           # Имя базы данных
$BackupPath = "C:\Backup\AdventureWorks_Full.bak" # Путь для сохранения резервной копии

# Выполнение резервного копирования
Backup-SqlDatabase -ServerInstance $ServerName -Database $DatabaseName -BackupFile $BackupPath -Type Full

Write-Host "Резервное копирование базы данных $DatabaseName завершено. Файл сохранен в $BackupPath."

Для более ранних версий можно применять альтернативу Backup-SqlDatabase, пример скрипта ниже

# Параметры для резервного копирования
$ServerName = "ServerName"               # Имя сервера SQL Server
$DatabaseName = "AdventureWorks"           # Имя базы данных
$BackupPath = "C:\Backup\AdventureWorks.bak" # Путь для сохранения резервной копии

# Подключение к SQL Server
Import-Module SqlServer
$SqlConnection = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)

# Выполнение резервного копирования
try {
    $Backup = New-Object Microsoft.SqlServer.Management.Smo.Backup
    $Backup.Action = "Database"
    $Backup.Database = $DatabaseName
    $Backup.Devices.AddDevice($BackupPath, "File")
    $Backup.SqlBackup($SqlConnection)
    Write-Host "Резервное копирование базы данных $DatabaseName завершено. Файл сохранен в $BackupPath."
} catch {
    Write-Host "Ошибка при выполнении резервного копирования: $_"
}

где,
$ServerName — имя сервера SQL Server.
$DatabaseName — имя базы данных, которую нужно заархивировать.
$BackupPath — путь, куда будет сохранен файл резервной копии (.bak).
New-Object Microsoft.SqlServer.Management.Smo.Backup — создает объект для выполнения резервного копирования.
SqlBackup — выполняет резервное копирование.

Скрипт для восстановления БД из резервной копии
Командлет Restore-SqlDatabase из модуля SqlServer позволяет достаточно удобный способ восстановления базы данных MS SQL Server из резервной копии. Он поддерживает различные сценарии, такие как восстановление на тестовом сервере, откат изменений или перенос данных.
Его синтаксис

Показать / Скрыть текст

Restore-SqlDatabase
       [-ClearSuspectPageTable]
       [-KeepReplication]
       [-Partial]
       [-ReplaceDatabase]
       [-RestrictedUser]
       [-Offset <Int64[]>]
       [-RelocateFile <RelocateFile[]>]
       [-AutoRelocateFile]
       [-FileNumber <Int32>]
       [-RestoreAction <RestoreActionType>]
       [-StandbyFile <String>]
       [-StopAtMarkAfterDate <String>]
       [-StopAtMarkName <String>]
       [-StopBeforeMarkAfterDate <String>]
       [-StopBeforeMarkName <String>]
       [-ToPointInTime <String>]
       [-Database] <String>
       [-Path <String[]>]
       [[-BackupFile] <String[]>]
       [-SqlCredential <PSObject>]
       [-BackupDevice <BackupDeviceItem[]>]
       [-PassThru]
       [-Checksum]
       [-ContinueAfterError]
       [-NoRewind]
       [-Restart]
       [-UnloadTapeAfter]
       [-NoRecovery]
       [-DatabaseFile <String[]>]
       [-DatabaseFileGroup <String[]>]
       [-BlockSize <Int32>]
       [-BufferCount <Int32>]
       [-MaxTransferSize <Int32>]
       [-MediaName <String>]
       [-Script]
       [-AccessToken <PSObject>]
       [-TrustServerCertificate]
       [-HostNameInCertificate <String>]
       [-Encrypt <String>]
       [-ProgressAction <ActionPreference>]
       [-WhatIf]
       [-Confirm]
       [<CommonParameters>]

Ниже приведены пример использования этого командлета (с параметрами)

# Параметры
$ServerName = "TestServerName"           # Имя тестового сервера SQL Server
$DatabaseName = "AdwentureWorks"           # Имя базы данных
$BackupPath = "C:\Backup\AdwentureWorks_Full.bak" # Путь к файлу резервной копии

# Выполнение восстановления
Restore-SqlDatabase -ServerInstance $ServerName -Database $DatabaseName -BackupFile $BackupPath -ReplaceDatabase

Write-Host "Восстановление базы данных $DatabaseName завершено."

Для более ранних версий можно применять альтернативу Restore-SqlDatabase, пример скрипта ниже

# Параметры для восстановления
$ServerName = "TestServerName"           # Имя тестового сервера SQL Server
$DatabaseName = "AdventureWorks"           # Имя базы данных
$BackupPath = "C:\Backup\AdventureWorks_Full.bak" # Путь к резервной копии
$DataFilePath = "C:\Data\AdventureWorks.mdf" # Путь к файлу данных (.mdf)
$LogFilePath = "C:\Data\AdventureWorks_log.ldf" # Путь к файлу журнала (.ldf)

# Подключение к SQL Server
Import-Module SqlServer
$SqlConnection = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)

# Остановка использования базы данных, если она активна
if ($SqlConnection.Databases[$DatabaseName] -ne $null) {
    $SqlConnection.KillAllProcesses($DatabaseName)
}

# Выполнение восстановления
try {
    $Restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $Restore.Action = "Database"
    $Restore.Database = $DatabaseName
    $Restore.Devices.AddDevice($BackupPath, "File")
    $Restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("YourDatabaseName", $DataFilePath)))
    $Restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("YourDatabaseName_log", $LogFilePath)))
    $Restore.ReplaceDatabase = $true
    $Restore.SqlRestore($SqlConnection)
    Write-Host "Восстановление базы данных $DatabaseName завершено."
} catch {
    Write-Host "Ошибка при восстановлении базы данных: $_"
}

где,
$ServerName — имя тестового сервера SQL Server.
$DatabaseName — имя базы данных, которую нужно восстановить.
$BackupPath — путь к файлу резервной копии (.bak).
$DataFilePath и $LogFilePath — пути к файлам данных (.mdf) и журналов (.ldf) соответственно.
RelocateFiles — указывает новые пути для файлов данных и журналов.
ReplaceDatabase = $true — позволяет заменить существующую базу данных, если она уже существует.

Скрипты для резервного копирования и восстановления баз данных являются важным инструментом для обеспечения надежности, безопасности и гибкости работы с данными. Они помогают защитить информацию от потери, упростить процессы разработки и тестирования, а также минимизировать простои в случае сбоев. Использование PowerShell делает эти процессы более автоматизированными, масштабируемыми и удобными для управления.