[DB][PowerShell] SQL Server バックアップを曜日単位で共有フォルダにコピー

SQL Server 製品版では、定期的なバックアップをメンテナンスプランで構成することが多いと思います。

BACKUP DATABASE コマンドの出力先に共有フォルダを指定することも可能ではありますが、SQL Server 実行ユーザーに権限が必要ですし、ローカルにバックアップを出力したうえでリモートにもコピーして多重化したい、ということもあるでしょう。
また、データベースと同期をとるべきリソースファイル群も、同じタイミングでリモートにバックアップできれば便利です。

ここでは、ローカルに出力したデータベースバックアップとリソースファイル群を共有フォルダにコピーする方法をご紹介します。
バックアップのサイクルは日次で、データベースバックアップは一週間分の世代を保持するものとします。
リソースファイル群は差分のみ適用して同期をとります。

1. SQL Server 構成マネージャから、エージェントサービスの実行ユーザーをリモートアクセス権限のあるアカウントに設定します。

※仮想アカウント「NT SERVICE\SQLSERVERAGENT」の既定構成では共有フォルダにアクセスできません。

2. バックアップ先のリモートPCに共有フォルダを作成し、「1」のアカウントに変更権限を付与します。

3. Management Studio を起動してデータベースに接続します。

4. メンテナンスプランを作成します。

5. サブプランを追加してスケジュールを設定します。

6. [ツールボックス] から「T-SQL ステートメントの実行タスク」をドラッグして完全バックアップ文を登録します。

BACKUP DATABASE [Northwind] TO DISK = N'C:\Backup\SQLServer\Northwind.bak' WITH INIT, NAME = N'Northwind_backup'

※「データベースのバックアップ タスク」では、バックアップファイル名を同一にして上書きすることができません。

7. [SQL Server エージェント] から、バックアップファイルを曜日ごとにコピーするジョブを作成します。

　種類：PowerShell

Copy-Item -Force -Path "C:\Backup\SQLServer\Northwind.bak" -Destination ("Microsoft.PowerShell.Core\FileSystem::\\remote-pc\Backup\SQLServer\Northwind_{0}.bak" -f (get-date).DayOfWeek)

※「(get-date).DayOfWeek」で「Sunday」など曜日文字列を付加しています。

※コピー先の共有フォルダパスは「Microsoft.PowerShell.Core\FileSystem::」で修飾します。これをつけ忘れると、ジョブ履歴に成功と記録されるものの、実際にはコピーされません。履歴の詳細を確認すると PowerShell で次のエラーが発生していることがわかります。

「PowerShell によって返されたエラー情報: 'コピー元パスとコピー先パスが同じプロバイダーを解決しませんでした。 」

8. [SQL Server エージェント] から、リソースファイル群の同期ジョブを作成します。

　種類：PowerShell

robocopy "C:\Backup\Resources" "\\remote-pc\Backup\Resources" /E /DCOPY:T /MIR

※robocopy はコピーに成功したときの戻り値が 0 でないため、[種類] に「オペレーティング システム（CmdExec）」を指定すると既定で失敗扱いにされてしまいます。

9. サブプランのデザインに戻ります。

10. [ツールボックス] から「SQL Server エージェント ジョブの実行タスク」をドラッグして矢印で接続し、 [編集] から「7」のジョブを選択します。

11. [ツールボックス] から「SQL Server エージェント ジョブの実行タスク」をドラッグして矢印で接続し、 [編集] から「8」のジョブを選択します。

12. 作成したメンテナンスプランは [Integration Services] に接続してエクスポートできます。

　[格納済のパッケージ]
　　[MSDB]
　　　[Maintenance Plans]

13. バックアップフォルダのイメージ

Northwind.bak
Northwind_Friday.bak
Northwind_Monday.bak
Northwind_Saturday.bak
Northwind_Sunday.bak
Northwind_Thursday.bak
Northwind_Tuesday.bak
Northwind_Wednesday.bak