SQL Server Expressのデータベースのフル(完全)バックアップを毎日自動で取って、ネットワーク経由で安全な場所(NAS)に移動、5日以上たったバックアップファイルは自動で削除されるという設定をしたので覚書。
バックアップ運用の概要
地味に重要なデータを保管しているSQL Server Express上のデータベースが全くバックアップが取られていない状況だったので、きちんとしたバックアップ運用ができるようにしました。
バックアップ処理の流れとしては以下のとおりです。
- SQL Server Expressの特定のデータベースのフル(完全)バックアップを取得
- 取得したバックアップファイルをNASのバックアップ保管ディレクトリに移動
- バックアップは、5日分だけ残してローテーション(削除)させる
- 上記の処理をバッチファイルにして毎日自動で実行されるようにタスクに登録
SQL Server Expressのフルバックアップ方法
バッチファイルでフルバックアップを実行する必要があるので”sqlcmd”を使います。
“sqlcmd”は、SQL Serverのコマンドライン操作用のコマンドです。
通常は、SQL Server Expressをインストールした以下のディレクトリにあります。
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\
“sqlcmd”がない場合は、Microsoftのサイトからダウンロードすることができますので以下のサイトを参照してください。
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)ファイルを開くと以下のような記載になっています。
1 2 | BACKUP DATABASE [Northwind] TO DISK = N'c:¥Program Files¥Microsoft SQL Server¥MSSQL11.SQLEXPRESS¥MSSQL¥Backup¥Northwind.bak' WITH NOFORMAT, NOINIT, NAME = N'Northwind-完全 データベース バックアップ', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
Microsoft SQL Server Management Studio について
「Microsoft SQL Server Management Studio」は、SQL Serverの管理用GUIアプリケーションです。
インストールされていない場合は、以下のサイトからダウンロードできます。
バックアップバッチファイルの作成
Windowsのタスクスケジュールに設定して毎日自動でデータベースのフルバックアップをするためにバッチファイルを作成します。
またバッチファルの中で以下の処理を追加します。
- 採取されたバックアップファイルのリネーム
- NASの共有フォルダへの接続とファイルの移動
- 5日以上経過した古いフルバックアップファイルの削除(ローテーション)
- 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:
バッチファイルの内容
最終的に出来上がったバッチファイルの内容は以下のとおりです。
※各種パス、ファイル名等は以下の設定を前提としています。
SQL Server Expressいスタンス名 | localhost\SQLExpress |
SQL Server Express 接続ユーザー名 | sa |
SQL Server Express 接続ユーザーパスワード | pass@01 |
バックアップ対象データベース | Northwind |
バックアップファイル名 | northwind.bak |
バックアップファイル保存パス | c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Backup\ |
フルバックアップ取得用スクリプトファイル名(フルパス) | c:\backup_datas\shell\northwind_backup.sql |
バックアップファイル移動先共有フォルダパス | \\nas01\backup_datas\sqlserver_express |
共有フォルダマッピングドライブレター | x: |
共有フォルダ接続ユーザー名 | admin |
共有フォルダ接続パスワード | p@ss01 |
作成したバッチファイル保管場所 | c:\backup_datas\shell\ |
作成したバッチファイル名 | northwind_backup.bat |
1 2 3 4 5 6 7 8 9 | sqlcmd -S localhost\SQLExpress -U sa -P pass@01 -i C:\backup_datas\shell\northwind_backup.sql net use x: ¥¥nas01¥backup_datas¥sqlserver_express /user:admin p@ss01 move c:¥Program Files¥Microsoft SQL Server¥MSSQL11.SQLEXPRESS¥MSSQL¥Backup¥northwind.bak x:¥%date:~0,4%%date:~5,2%%date:~8,2%_northwind.bak forfiles /p x:¥ /d -5 /m *northwind.bak /c "cmd /c del @file" net use /delete x: |
Windowsタスクスケジュールの設定
作成したバッチファイルをWindowsのタスクスケジュールで毎日指定の時間に実行するように設定してしまえば終了です。
タスクスケジュールの設定手順については、特に難しいことはないと思うので省略します。
失ったあとで後悔しても遅いので、きちんとバックアップは取っておきましょう。