異地備援 SQL Server 排時自動備份+自動還原

安裝 SQL Server 模組 (以 PowerShell 執行)

Copy to Clipboard

執行程式 autoRestoreDB.ps1

Copy to Clipboard

# 載入 SQLServer 模組
Import-Module SqlServer

# 設定備份檔案所在的資料夾
$backupFolder = "D:\BACKUP\SQL備份\公司用進銷存"  # 替換成你的實際路徑
$serverInstance = "localhost"  # 伺服器名稱或 IP 地址
$databaseName = "公司用進銷存"  # 資料庫名稱

# 手動指定 MDF 和 LDF 檔案的來源路徑
$mdfFile = "D:\SQL\公司用進銷存.mdf"
$ldfFile = "D:\SQL\公司用進銷存_1.ldf"

# 找到最新的備份檔案
$latestBackupFile = Get-ChildItem -Path $backupFolder -Filter *.bak | Sort-Object LastWriteTime -Descending | Select-Object -First 1

# 如果找到備份檔案，進行還原
if ($latestBackupFile) {
    Write-Host "最新的備份檔案是: $($latestBackupFile.FullName)"

# 建立 SMO 伺服器對象
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance
    Write-Host "已成功連接到 SQL Server 伺服器: $serverInstance"

# 檢查資料庫是否存在
    if ($server.Databases[$databaseName]) {
        Write-Host "資料庫 $databaseName 已存在，準備備份並移動到新資料夾..."

# 停止資料庫使用
        $server.KillAllProcesses($databaseName)
        Write-Host "已停止所有連接至資料庫的進程"

# 建立日期時間資料夾
        $timestamp = Get-Date -Format "yyyyMMdd-HHmmss"
        $backupFolderPath = "D:\SQL\$timestamp"
        New-Item -ItemType Directory -Path $backupFolderPath
        if (Test-Path $backupFolderPath) {
            Write-Host "資料夾已成功建立: $backupFolderPath"
        } else {
            Write-Host "資料夾建立失敗，腳本將停止"
            exit
        }

# 將資料庫設置為離線模式，準備移動檔案
        $db = $server.Databases[$databaseName]
        $db.DatabaseOptions.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Single
        $db.Alter()  # 應用變更
        $db.SetOffline()  # 設定資料庫離線
        Write-Host "資料庫 $databaseName 已設為離線狀態"

# 檢查 MDF 和 LDF 檔案是否存在
        if ((Test-Path $mdfFile) -and (Test-Path $ldfFile)) {
            Write-Host "MDF 和 LDF 檔案存在，準備移動..."

# 保持檔案名不變，直接移動資料庫檔案到新的資料夾
            Move-Item $mdfFile "$backupFolderPath\$(Split-Path $mdfFile -Leaf)"
            Move-Item $ldfFile "$backupFolderPath\$(Split-Path $ldfFile -Leaf)"
            Write-Host "資料庫檔案已移動到: $backupFolderPath"
        } else {
            Write-Host "未找到 MDF 或 LDF 檔案。檔案路徑可能不正確。"
            exit
        }

# 刪除現有的資料庫記錄
        $server.Databases[$databaseName].Drop()
        Write-Host "資料庫 $databaseName 已刪除"
    } else {
        Write-Host "資料庫 $databaseName 不存在，跳過備份步驟"
    }

# 還原資料庫並指定新路徑
    Write-Host "開始還原資料庫 $databaseName..."
    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Database = $databaseName
    $restore.Action = "Database"
    $restore.Devices.AddDevice($latestBackupFile.FullName, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
    $restore.ReplaceDatabase = $true

# 設定資料庫檔案的目標位置 (和手動還原保持一致)
    $mdf = "D:\SQL\公司用進銷存.mdf"
    $ldf = "D:\SQL\公司用進銷存_1.ldf"

# 設定 MDF 和 LDF 檔案的新路徑，並確保邏輯名稱與手動還原一致
    $restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("公司用進銷存_Data", $mdf)))
    $restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("公司用進銷存_Log", $ldf)))

# 設定還原選項 (使用 WITH RECOVERY)
    $restore.NoRecovery = $false  # 確保這裡使用 "WITH RECOVERY" 選項

# 開始還原
    try {
        $restore.SqlRestore($server)
        Write-Host "資料庫還原成功"
    } catch {
        Write-Host "還原失敗，錯誤訊息："
        Write-Host $_.Exception.Message
    }

} else {
    Write-Host "沒有找到備份檔案"
}

加入排程

Copy to Clipboard

# 設定任務名稱和描述
$taskName = "AutoRestoreDB"
$taskDescription = "自動執行 autoRestoreDB.ps1 來還原資料庫"

# 設定 PowerShell 腳本的位置
$scriptPath = "D:\SCRIPT\autoRestoreDB.ps1"

# 建立排程任務動作 (執行 PowerShell 腳本)
$action = New-ScheduledTaskAction -Execute "powershell.exe" -Argument "-ExecutionPolicy Bypass -File $scriptPath"

# 讓使用者選擇排程選項
Write-Host "請選擇執行頻率："
Write-Host "1. 每小時執行"
Write-Host "2. 每天午夜12點執行"
$choice = Read-Host "輸入選項 (1 或 2)"

if ($choice -eq "1") {
    # 設置觸發器，每 1 小時執行一次，重複 1 天的時間
    $trigger = New-ScheduledTaskTrigger -Once -At (Get-Date) -RepetitionInterval (New-TimeSpan -Hours 1) -RepetitionDuration (New-TimeSpan -Days 1)
    Write-Host "已選擇每小時執行。"
} elseif ($choice -eq "2") {
    # 設置觸發器，每天午夜 12 點執行
    $trigger = New-ScheduledTaskTrigger -Daily -At "00:00"
    Write-Host "已選擇每天午夜12點執行。"
} else {
    Write-Host "無效的選擇，腳本將退出。"
    exit
}

# 設置排程任務的一般屬性
$settings = New-ScheduledTaskSettingsSet -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries -StartWhenAvailable

# 註冊排程任務到 Windows 任務排程中
Register-ScheduledTask -Action $action -Trigger $trigger -TaskName $taskName -Description $taskDescription -Settings $settings -User "SYSTEM" -RunLevel Highest

Write-Host "任務已成功加入排程。"

異地備援 SQL Server 排時自動備份+自動還原

分享至您的平台

相關文章：

server 2019 安裝 termainl service 後注意事項及問題

查詢 MS SQL Server 的SQL指令log(2016以上)

開放 PowerShell ISE 測試權限