SQL Server Express:データベースのフル(完全)バックアップを毎日自動で取得、バックアップファイルは5日分残して削除する

SQL Server Expressのデータベースのフル(完全)バックアップを毎日自動で取って、ネットワーク経由で安全な場所(NAS)に移動、5日以上たったバックアップファイルは自動で削除されるという設定をしたので覚書。

バックアップ運用の概要

地味に重要なデータを保管しているSQL Server Express上のデータベースが全くバックアップが取られていない状況だったので、きちんとしたバックアップ運用ができるようにしました。

バックアップ処理の流れとしては以下のとおりです。

1. SQL Server Expressの特定のデータベースのフル(完全)バックアップを取得
2. 取得したバックアップファイルをNASのバックアップ保管ディレクトリに移動
3. バックアップは、5日分だけ残してローテーション(削除)させる
4. 上記の処理をバッチファイルにして毎日自動で実行されるようにタスクに登録

SQL Server Expressのフルバックアップ方法

バッチファイルでフルバックアップを実行する必要があるので”sqlcmd”を使います。

“sqlcmd”は、SQL Serverのコマンドライン操作用のコマンドです。

通常は、SQL Server Expressをインストールした以下のディレクトリにあります。

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\

“sqlcmd”がない場合は、Microsoftのサイトからダウンロードすることができますので以下のサイトを参照してください。

sqlcmd ユーティリティ - SQL Server

docs.microsoft.com

SQL Server Expressがインストールされたマシンのコマンドプロンプトで以下の用に入力して”sqlcmd”のヘルプが表示されれば、”sqlcmd”がインストールされていて使える(パスがと覆っている)ことが確認できます。

sqlcmd /?

sqlcmd コマンドによるデータベースへの接続とフルバックアップの実行

sqlcmdコマンドでフルバックアップを実行する場合、フルバックアップのスクリプトを実行します。

その際の書式は以下のとおりです。
※画面の都合上改行されていますが、実際には改行コードは入っていません。

sqlcmd -S サーバー名\インスタンス名 -U ユーザー名 -P パスワード -i スクリプトファイル名(フルパス)

サーバー名が”localhost”、インスタンス名”SQLExpress”、接続の際に使用するユーザー名を”sa”、パスワードが”pass@123″、実行するフルバックアップのスクリプトファイルが”c:\work\northwind_backup.sql”とした場合、以下のようになります。

sqlcmd -S localhost\SQLExpress -U sa -P pass@123 -i c:\work\northwind_backup.sql

フルバックアップ実行スクリプトについて

sqlcmdコマンドで呼び出すフルバックアップ実行スクリプトファイルですが、以下の手順で作成ができます。

「Microsoft SQL Server Management Studio」を起動し、対象のSQL Server Expressに接続します。

フルバックアップを取りたいデータベースを右クリックし、[タスク] > [バックアップ]をクリックします。

[データベースのバックアップ]画面が表示されるので、[ソース]欄の[データベース]にバックアップ対象のデータベースが指定されていること、[バックアップの種類]が”完全”、[バックアップ先]が”ディスク”になっていることを確認します。

次に[データベースのバックアップ]画面左上部にある[スクリプト]の下三角ボタンをクリックし、[スクリプト操作をファイルに保存]をクリックします。

[名前を付けて保存]画面が表示されるので、[ファイル名]ボックスに任意のフィル名(ここでは、northwind_backup.sqlとしています)を入力し、[保存]をクリックします。

保存が終了すると[データベースのバックアップ]画面に戻るので、[キャンセル]ボタンで閉じてしまってかまいません。

ちなみに保存されたスクリプト(sql)ファイルを開くと以下のような記載になっています。

Microsoft SQL Server Management Studio について

「Microsoft SQL Server Management Studio」は、SQL Serverの管理用GUIアプリケーションです。

インストールされていない場合は、以下のサイトからダウンロードできます。

SQL Server Management Studio (SSMS) のダウンロード - SQL Server Management Studio (SSMS)

docs.microsoft.com

バックアップバッチファイルの作成

Windowsのタスクスケジュールに設定して毎日自動でデータベースのフルバックアップをするためにバッチファイルを作成します。

またバッチファルの中で以下の処理を追加します。

1. 採取されたバックアップファイルのリネーム
2. NASの共有フォルダへの接続とファイルの移動
3. 5日以上経過した古いフルバックアップファイルの削除(ローテーション)
4. NASの共有フォルダの切断

採取されたバックアップファイルのリネーム

バックアップファイルは、5日分残してローテーションさせるので、ファイル名の先頭にバックアップ実行した日付をyyyymmddを付加することにします。

リネーム自体は、バックアップファイルをNASの共有フォルダに”move”コマンドで移動させる際に名前を変えて移動するようにバッチファイル内に以下のように記載します。

move 移動元ファイル名(フルパス)  移動先ファイル名(フルパス)

ファイル名の先頭に日付(yyyymmdd)を付加する

ファイル名の先頭にバックアップ実行した日付をyyyymmdd形式で付加して、「20190529_バックアップファイル名」としたいので、バッチファイルの”move”コマンドの移動先ファイル名の先頭に以下の文字列を付加します。

%date:~0,4%%date:~5,2%%date:~8,2%_

上記の文字列は、”date”コマンドの結果から”/”抜いた文字列を抽出してくっつけることを意味しています。

NASの共有フォルダへの接続

採取されたバックアップファイルの移動先であるNASの共有フォルダへの接続には、”net use”コマンドを使用します。

net use ドライブレター: \\NAS名\共有フォルダ名 /user:ユーザー名 パスワード

例えば、xドライブに”nas01″の”backup_datas\sqlserver_express”という共有フォルダをマッピングして、NAS接続時のユーザー名が”admin”、パスワードが”p@ss01″だった場合以下のようになります。

net use x: \\nas01\backup_datas\sqlserver_express /user:admin p@ss01

バックアップファイルを直近5日分だけ残して削除する

保管するバックアップファイルは、”forfiles”コマンドを使って直近5日分だけ残してそれ以上古いものは自動的に削除させて必要以上にファイルが増え続けないようにします。

“fofiles”コマンドの記述は以下のようになります。

forfiles /p ファイルパス /d -日数 /m 削除ファイル /c “cmd /c del @file”

xドライブに保管された先頭に日付がついたバックアップファイル”yyyymmdd_northwind.bak”を5日分残す場合の実際のコマンドは以下のとおりです。

forfiles /p x:\ /d -5 /m *northwind.bak /c “cmd /c del @file”

共有フォルダとの接続を切断する

すべての処理が終わったら、共有フォルダとの接続を切断します。

共有フォルダの切断は、”net use”コマンドを使います。

net use /delete x:

バッチファイルの内容

最終的に出来上がったバッチファイルの内容は以下のとおりです。
※各種パス、ファイル名等は以下の設定を前提としています。

Windowsタスクスケジュールの設定

作成したバッチファイルをWindowsのタスクスケジュールで毎日指定の時間に実行するように設定してしまえば終了です。

タスクスケジュールの設定手順については、特に難しいことはないと思うので省略します。

失ったあとで後悔しても遅いので、きちんとバックアップは取っておきましょう。