Перенос баз данных MS SQL между серверами

Для автоматизации переноса баз данных с одного Microsoft SQL сервера на другой можно использовать скрипт на PowerShell.
Он делает следующее:

• Подключается к серверу с которого необходимо перенести базу данных и создаёт её резервную копию;
• Подключается к серверу, на который необходимо перенести базу данных и восстанавливает её из резервной копии, сделанной на предыдущем этапе;
• Удаляет файл резервной копии.

Для корректной работы скрипта к общей папке \\share\bak\, в которую будет производится временное сохранение резервной копии, необходимо предоставить доступ учётной записи, от имени которой работают экземпляры sql-серверов, как источника, так и приёмника. Если для запуска sql-сервера не используется какая-то пользовательская учётная запись, например oldfag\sqluser, то доступ нужно предоставить для учётной записи компьютера, на котром запущен экземпляр sql-сервера. Пользователю, от имени которого запускается скрипт, так же необходим досутп к этой папке, но только для того, чтобы удалить файл резервной копии после завершения восстановления.
Скрипт можно запускать на любом компьютере, но при этом в его коде напрямую не указаны пароли для подключения к sql-серверам, они хранятся в файле passwd.xml. Функция для создания этого файла описана в одной из предыдущих заметок.
Стоит так же отметить, что в скрипте имеется запрет на перенос базы на основной сервер, чтобы избежать непредвиденных последствий.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null

If (-not(Get-InstalledModule SQLServer -ErrorAction silentlycontinue)) {
    Set-PSRepository PSGallery -InstallationPolicy Trusted
    Install-Module SQLServer -Confirm:$False -Force -Scope CurrentUser
}
Import-Module SqlServer

# Config
$backupFileDir = '\\share\bak\'
$credentialFile = "$PSScriptRoot\passwd.xml"

# Screen cleanup
# Clear-Host
    
# Request data from user
$sourceServer = Read-Host -Prompt 'Please enter source SQL server instance name'
while (!$sourceServer){
    $sourceServer = Read-Host -Prompt 'Please enter source SQL server instance name'
} 

$sourceDatabase = Read-Host -Prompt 'Please enter source database name'
while (!$sourceDatabase){
    $sourceDatabase = Read-Host -Prompt 'Please enter source database name'
}

$destServer = Read-Host -Prompt 'Please enter destination SQL server instance name (prodSQLSrv is not available) '
while (!$destServer -or $destServer -eq 'prodSQLSrv'){
    $destServer = Read-Host -Prompt 'Please enter destination SQL server instance name (prodSQLSrv is not available) '
}

$destDatabase = Read-Host -Prompt 'Please enter destination database name'
while (!$destDatabase){
    $destDatabase = Read-Host -Prompt 'Please enter destination database name'
}

Clear-Host

# Some decoration
$backupPercent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
    Write-Progress -id 1 -activity "Backing up $sourceDatabase from $sourceServer" -percentcomplete $_.Percent -status ([System.String]::Format('Progress: {0} %', $_.Percent))
}
$restorePercent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler]{
    Write-Progress -id 1 -activity "Restoring data to $destDatabase on $destServer" -percentcomplete $_.Percent -status ([System.String]::Format('Progress: {0} %', $_.Percent))
}

# Import Credentials
$rawCredentials = Import-Clixml $credentialFile
$srcCredentials = New-Object System.Management.Automation.PSCredential($rawCredentials.$sourceServer.Username, (ConvertTo-SecureString -String $rawCredentials.$sourceServer.Password -Key $rawCredentials.$sourceServer.Key))
$destCredentials = New-Object System.Management.Automation.PSCredential($rawCredentials.$destServer.Username, (ConvertTo-SecureString -String $rawCredentials.$destServer.Password -Key $rawCredentials.$destServer.Key))

# Backup
$timeStamp = Get-Date -Format yyyyMMddHHmmss
$backupFileName = $backupFileDir + $sourceDatabase + '-' + $timeStamp + '.bak'
$smoSrcServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $sourceServer
$smoSrcServer.ConnectionContext.LoginSecure = $false
$smoSrcServer.ConnectionContext.set_Login($srcCredentials.UserName)
$smoSrcServer.ConnectionContext.set_SecurePassword($srcCredentials.Password)
$smoSrcServer.ConnectionContext.StatementTimeout = 0
$smoSrcServer.ConnectionContext.ConnectTimeout = 0
$smoBackup = New-Object 'Microsoft.SqlServer.Management.Smo.Backup'
$smoBackup.Action = 'Database'
$smoBackup.Database = $sourceDatabase
$smoBackup.Devices.AddDevice($backupFileName, 'file')
$smoBackup.add_PercentComplete($backupPercent)
$smoBackup.PercentCompleteNotification = 1
$smoBackup.Incremental = $false
Write-Progress -id 1 -activity "Backing up $sourceDatabase from $sourceServer" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
$smoBackup.SqlBackup($smoSrcServer)

# Restore
$smoDestServer = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $destServer
$smoDestServer.ConnectionContext.LoginSecure = $false
$smoDestServer.ConnectionContext.set_Login($destCredentials.UserName)
$smoDestServer.ConnectionContext.set_SecurePassword($destCredentials.Password)
$smoDestServer.ConnectionContext.StatementTimeout = 0
$smoDestServer.ConnectionContext.ConnectTimeout = 0
$dataFilePath = $smoDestServer.Databases[$destDatabase].FileGroups[0].Files[0].FileName.Substring(0,$smoDestServer.Databases[$destDatabase].FileGroups[0].Files[0].FileName.LastIndexOf('\')+1)
$logFilePath = $smoDestServer.Databases[$destDatabase].LogFiles[0].FileName.Substring(0,$smoDestServer.Databases[$destDatabase].LogFiles[0].FileName.LastIndexOf('\')+1)
$smoRestore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$backupDeviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem' ($backupFileName, 'File')
$smoRestore.Action = 'Database'
$smoRestore.Database = $destDatabase
$smoRestore.ReplaceDatabase = $true
$smoRestore.Devices.Add($backupDeviceItem)
$smoRestore.add_PercentComplete($restorePercent)
$smoRestore.PercentCompleteNotification = 1
$fileList = $smoRestore.ReadFileList($smoDestServer)
foreach ($file in $fileList) {
    $relocateFile = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile'
    $relocateFile.LogicalFileName = $file.LogicalName
    if ($file.Type -eq 'D') {
        $relocateFile.PhysicalFilename = $dataFilePath + $destDatabase + ".mdf"
    }
    else {
        $relocateFile.PhysicalFileName = $logFilePath + $destDatabase + "_log.ldf"    
    }
    $smoRestore.RelocateFiles.Add($relocateFile) | Out-Null
}
$smoDestServer.KillAllProcesses($destDatabase)
Write-Progress -id 1 -activity "Restoring data to $destDatabase on $destServer" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
$smoRestore.SqlRestore($smoDestServer)

# Celanup
Remove-Item $backupFileName

Запускать скрипт можно из командной строки или с помощью bat-файла, который положить рядом с файлом

@echo off
cls
PowerShell -NoProfile -ExecutionPolicy Bypass -Command "& './SQL_DB_Transfer.ps1'"
pause