SQL Serverの複数のデータベースを一括リストアする

はじめに

サーバーリプレースなどで複数のデータベースを新環境にリストアするようなシーンにおいて、SSMSのGUI操作で１つずつ復元することには苦痛を感じます。
そこで、一括リストアするTSQLスクリプトを作成するPowerShellスクリプトを作成しました。

PowerShellスクリプトの使い方

• PowerShellスクリプト(以下、PSスクリプト）と同じ場所に復元対象のバックアップファイル名を記したテキストファイルを置いておきます（ここでは、backupfiles.txt）。
• 私は、PowerShell機能拡張をインストールしたVSCode上で実行しています。
• PSスクリプトを実行する前に、必要に応じて各変数の値を編集します。
• PSスクリプトを実行したら、PSスクリプトと同じ場所に script.sql が出力されます。

前提事項

• バックアップファイルは、SQL Server既定のバックアップフォルダに配置してあること。
• バックアップファイル名は、《データベース名》_backup_xxxxxx.bak であること（データベース名は、バックアップファイル名から切り出して取得しています）。
• データは複数または単一、ログは単一であること。

PowerShellスクリプトのソース

#================================================================================
#各変数の値は環境に合わせて適宜設定
$backup_list = "backupfiles.txt"
$serverName = "sqlsv"
$userName = "sa"
$userPassword = "sapassword"
$basepath = "C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL"
#================================================================================
$bakpath = "${basepath}\backup"
$datpath = "${basepath}\DATA"
$outfile = "script.sql"

# *** 関数定義 ***
# T-SQLを実行する
Function Invoke-TSQL ($tsql) {
	$tsqlResult = sqlcmd -S ${serverName} -U ${userName} -P ${userPassword} -Q ${tsql}
	return $tsqlResult
}

# RESTORE FILELISTONLYの結果行から論理名を取得する
Function Get-LogicalNameFromLine ($tsqlResultLine) {
	$columns = ($tsqlResultLine -split '\s{2,}').Split("\n")
    $logicalName = $columns[0]
    $fieldId = $columns[6]

    return $logicalName, $fieldId
}

# データ・ログの論理名を取得する
# データは複数、ログは単一であることを前提としている
Function Get-LogicalName ($backup_device) {
    # バックアップデバイスのファイルリストを読み取る
	$tsql = "RESTORE FILELISTONLY FROM DISK = N'${backup_device}'"
	$tsqlResult = Invoke-TSQL($tsql)
	$lines = $tsqlResult.Split("\n");

    # 配列初期化
    $LogiData = @();

    # 2行目まではヘッダなので、3行目から読み込む
    for ($i = 2 ; $i -lt $lines.Count ; $i++) {
        $result = Get-LogicalNameFromLine($lines[$i])
        $logicalName = $result[0]
        $fieldId = $result[1]

        # $fieldIdが数値でなくなったらブレーク
        if ([Int32]::TryParse($fieldId, [ref]$null) -ne $true) {
            break
        }

        # $filedIdが2ならログファイル、それ以外はデータファイル
        if ($fieldId -eq 2) {
            $LogiLog = $logicalName
        } else {
            $LogiData += $logicalName
        }
    }

    return $LogiData, $LogiLog
}

# *** 処理本体 ***
if (Test-Path $outfile) {
    Remove-Item $outfile
}

# バックアップデバイスリストを読み込む
$bakfiles = Get-Content $backup_list

foreach ($bakfile in $bakfiles) {
	# BAKファイル名からDB名を取得
	$backup_file = Split-Path $bakfile -Leaf
	$dbname = $backup_file.split("_backup")[0]

	# 論理名を取得
	$results = Get-LogicalName("${bakpath}\${bakfile}")
	$LogiDatas = $results[0]
	$LogiLog  = $results[1]

    # バックアップデバイスを復元するスクリプトを出力
    # （１）スクリプトの冒頭
    $sb = New-Object System.Text.StringBuilder
    [void]$sb.AppendLine("-- データベース ${dbname} の復元スクリプト")
	[void]$sb.AppendLine("RESTORE DATABASE ${dbname} FROM DISK='${bakpath}\${backup_file}' WITH RECOVERY, ")
    # （２）データファイル
    $i = 1
    foreach ($LogiData in $LogiDatas) {
        if ($i -eq 1) {
            $newname = "${dbname}"
        } else {
            $newname = "${dbname}_${i}"
        }
        [void]$sb.Append("MOVE '${LogiData}' TO '${datpath}\${newname}.mdf', ")
        $i++
    }
    # （３）ログファイル
	[void]$sb.AppendLine("MOVE '${LogiLog}'  TO '${datpath}\${dbname}_log.ldf'")

    # 論理名がdbnameに基づくものでない場合、リネームするスクリプトを出力
    # 同じ論理名がすでに他のデータベース内に存在するとまずいようだし、規則性がなくバラバラなのもこの際揃えてしまいたい。
    # （１）データファイル
    $i = 1
    foreach ($LogiData in $LogiDatas) {
        if ($i -eq 1) {
            $newname = $dbname
        } else {
            $newname = "${dbname}${i}"
        }
        if ($LogiData -ne $newname) {
            [void]$sb.AppendLine("ALTER DATABASE [${dbname}] MODIFY FILE (NAME=N'${LogiData}', NEWNAME=N'${dbname}')")
        }
        $i++
    }
    # （２）ログファイル
    if ($LogiLog -ne "${dbname}_Log") {
        [void]$sb.AppendLine("ALTER DATABASE [${dbname}] MODIFY FILE (NAME=N'${LogiLog}' , NEWNAME=N'${dbname}_Log')")
    }

    # 結果をファイルに追記出力
    Write-Output $sb.ToString() | Out-File -Append $outfile
}

backupfiles.txtの例

database01_backup_2024_03_21_000000_000000.bak
database02_backup_2024_03_21_000000_000000.bak
database03_backup_2024_03_21_000000_000000.bak
database04_backup_2024_03_21_000000_000000.bak
database05_backup_2024_03_21_000000_000000.bak
database06_backup_2024_03_21_000000_000000.bak

最後に

これまで、一括バックアップについては使い切りのメンテナンスプランを作って実行していましたのでそれほど困っていませんでした。しかし、一括リストアについてはなかなか決め手となるやり方に巡り合えませんでした。
このスクリプトを作成したことで、ほぼ望んでいたことを実現できました。

なお、一括バックアップを使い切りのメンテナンスプランよりお手軽に行う方法については、@tfukumoriさんのこちらの記事がおススメです。