使用 PowerShell 產生批次進行 BAK 還原作業所需 Restore Scritps

2023-10-18

說明如何將大量的 bak 檔案，藉由 PowerShell 製作成 SQL Restore Scripts，達到批次還原資料庫伺服器的便利作用。

SQL Server Logo

說明

不囉嗦，直接上 PowerShell Script 😎

# 指定備份檔案目錄路徑
$backupDirectory = "D:\BakFilePath"

# 取得所有子資料夾
$subdirectories = Get-ChildItem -Path $backupDirectory -Directory

# 初始化還原腳本
$allRestoreScript = ""

# 針對每個子資料夾查找最新日期的 .bak 檔案並生成還原腳本
foreach ($subdirectory in $subdirectories) {
    $latestBackup = Get-ChildItem -Path $subdirectory.FullName -Filter *.bak | Sort-Object LastWriteTime -Descending | Select-Object -First 1

    if ($latestBackup -ne $null) {
        $databaseName = ($latestBackup.Name -split "_backup")[0]

        # 生成還原腳本
        $restoreScript = @"
USE [master]
RESTORE DATABASE [$databaseName] FROM DISK = N'$($latestBackup.FullName)' WITH FILE = 1, NOUNLOAD, STATS = 5;
"@

        # 將還原腳本添加到 allRestoreScript 變數
        $allRestoreScript += $restoreScript + "`r`n"
    }
}

# 將 allRestoreScript 寫入 all-restore.sql 腳本檔案
$allRestoreScriptFilePath = Join-Path -Path $backupDirectory -ChildPath "all-restore.sql"
$allRestoreScript | Set-Content -Path $allRestoreScriptFilePath

Write-Host "已生成 all-restore.sql 腳本檔案。"
#Database #SQLServer #Generated