Восстановление базы по расписанию из бэкапа — PowerShell

Это вариант на PowerShell + T-SQL: помимо восстановления он переименовывает логические файлы, выставляет владельца, уровень совместимости и модель восстановления. Если PowerShell на сервере использовать не хочется — та же задача решена одним T-SQL-скриптом в статье Авто-восстановление MSSQL-базы из последнего бэкапа.

Делается через powershell + tsql

Логика следующая

1. Проходится папка BackupDir с резервными копиями
2. Берется последняя резервная копия нужной базы по имени (в -Filter указываем кусок названия нужной копии)
3. Рвутся соединения к БД назначения
4. Происходит восстановление и переименование файлов базы и лога

В данном варианте происходит восстановление из бэкапа рабочей базы в тестовую копию базы.

# restores a full database backup to another database from source's latest full backup file in specified directory
 
# begin script configuration here
$TargetSqlServerInstance = "localhost"                                                                        # target server instance 
$TargetDb = "backup"                                                                                            # target database 
$BackupDir = "C:\sql_bak\daily"                                                                 # directory / share where backups are stored
$SourceLogicalDataFileName = "work"                                                                           # logical data file name of source db 
$SourceLogicalLogFileName =  "work_log"                                                                       # logical log file name of source db
$TargetLogicalDataFileName = "backup"                                                                           # logical name you want to change logical data file on target db to
$TargetLogicalLogFileName =  "backup_log"                                                                       # logical name you want to change logical log file on target db to
$TargetPhysicalDataFileName = "D:\SQL\Bases\backup.mdf"     # full path\file of target db physical data file 
$TargetPhysicalLogFileName =  "D:\SQL\Logs\backup_log.mdf" # full path\file of target db physical log file 
$CompatLevel = 140                                                                                                   # compatibility level to set target database to (2019=150, 2017=140, 2016=130, 2014=120, 2012=110, 2008/2008R2=100, 2005=90, 2000=80, 7=70) 
# end script configuration here
 
# import sqlserver module
Import-Module sqlserver
 
# latest full backup file name is dynamically determined and appended to backup directory
$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter work*.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1 
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile
 
# kill any connections in target database
$KillConnectionsSql=
"
USE master
GO
ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
ALTER DATABASE $TargetDb SET MULTI_USER
GO
USE master
GO
"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql
 
# import sqlserver module
Import-Module sqlserver
 
# restore
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName", "$TargetPhysicalDataFileName")
$RelocateLog  = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalLogFileName",  "$TargetPhysicalLogFileName")
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase
# end restore
 
# set db owner to sa
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Database $TargetDb -Query "EXEC sp_changedbowner sa"
 
# set compatibility level
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $($TargetDb) SET COMPATIBILITY_LEVEL =$($CompatLevel)"  
 
# set recovery model to simple
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $($TargetDb) SET RECOVERY SIMPLE WITH NO_WAIT"  
 
# rename logical files
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalDataFileName', NEWNAME='$TargetLogicalDataFileName')"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalLogFileName', NEWNAME='$TargetLogicalLogFileName')"
 
# dbcccheckdb
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "DBCC checkdb ($TargetDb) --WITH NO_INFOMSGS"
 
# display sp_helpdb
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "EXEC sp_helpdb $($TargetDb)" 

Нюансы

1. Может не проходит соединение защищенное при обращении к серверу SQL и ругаться на сертификат, тогда добавляем -TrustServerCertificate параметром к Invoke-Sqlcmd
2. Иногда бывает что модули для работы SQL есть без установки модуля sqlserver, в этом случае импортировать не нужно, чтобы не было лишних ошибок. И наоборот - может не быть данных функций и модуля, тогда сначала доставляем через консоль powershell - Install-Module -Name SqlServer
3. Установка владельца, уровня совместимости, проверка БД и вывод информации опциональны, их если что можно отключить

Выполнение можно повесить через планировщик или sql агент

Связанные статьи

• Авто-восстановление MSSQL-базы из последнего бэкапа — та же задача одним T-SQL-скриптом, без PowerShell;
• Создание планов резервного копирования и обслуживания MS SQL — откуда берутся .bak-файлы, из которых восстанавливаемся;
• Восстановление базы данных через SSMS — разовое восстановление вручную, без расписания.