BACKUP

SQL Server Expressのバックアップ

SQL Server Expressでは、機能制限によりSQL Server Management Studio (SSMS) でメンテナンスプランによる自動バックアップを行う事ができません。 その為、Expressでのバックアップは、バッチファイルを作成して行います。

構成例)
SQL Server稼働サーバーでDBバックアップを行い、そのバックアップファイルを他サーバーやNASへコピーする作業の自動化を行います。

SQLバックアップ構成例
  1. SQLファイルを作成
  2. バッチファイルを作成
  3. タスクスケジューラ―に登録

1. SQLファイルを作成

① メモ帳などのテキストエディタで、次の内容のファイル「backup.sql」を作成する。

コピーする時は、特殊文字(¥マークなど)の文字化けに注意してください。

DECLARE @DAY CHAR(8) = FORMAT(GETDATE(),'yyyyMMdd')
DECLARE @FILE VARCHAR(50) = N'バックアップ作成フォルダ¥DB名_' + @DAY + '.bak'
BACKUP DATABASE [DB名]
TO  DISK = @FILE
WITH NOFORMAT,NOINIT,
	NAME = N'完全 データベース バックアップ',
	SKIP, NOREWIND, NOUNLOAD, STATS = 100

1行目 :日付を取得
2行目 :DBバックアップファイルを日付毎に作成するように設定
3行目~:DBバックアップを実行

② バックアップ作成フォルダとDB名を環境に合わせて変更する。

例)
バックアップ作成フォルダ:C:¥original
DB名:exDB

※バックアップ作成フォルダは、ネットワーク先を指定する事も可能ですが、SQL Serverの設定が必要になります。

③ 動作確認を行う。

SQL Server Management Studio (SSMS)のクエリ実行から動作確認してください。

BACKUP DATABASEのオプション設定

NOFORMAT バックアップファイルの初期化をしません。複数回実行すると、バックアップセットが同一ファイルに保存されます。 初期化する場合は、FORMATを指定。
STATS=100 バックアップの進捗率を設定します。10で10%毎、100にすると100%のみ表示します。

※その他は、あまり使わないため省略

2. バッチファイルを作成

① メモ帳などのテキストエディタで、次の内容のファイル「backupsql.bat」を作成する。

コピーする時は、特殊文字(¥マークなど)の文字化けに注意してください。

set logfolder=ログファイル用フォルダ
set logfile=%logfolder%¥backupsql_%DATE:/=%.log
set originalfolder=コピー元フォルダ
echo バックアップ開始 %DATE% %TIME% >> %logfile%
sqlcmd -U ログインユーザ -P パスワード -S SQLServer接続先 -i sqlファイル用フォルダ¥backup.sql >> %logfile%
forfiles /P %originalfolder% /S /D -10 /C "cmd /c del /F /Q @path" >> %logfile%
robocopy %originalfolder% コピー先フォルダ /MIR /R:0 /NDL /NP >> %logfile%
forfiles /P %logfolder% /S /D -30 /C "cmd /c del /F /Q @path" >> %logfile%

1行目:ログ用フォルダの設定
2行目:ログファイルを日付毎に作成するように設定
3行目:コピー元(=DBバックアップファイル用フォルダ)の設定
4行目:処理開始ログの出力
5行目:SQL Serverに接続を行い、SQLファイルを実行
6行目:古い(10日経過)DBバックアップファイルを削除
7行目:DBバックアップファイルを、コピー
8行目:古い(30日経過)ログファイルを削除

② 各フォルダやSQL Serverの接続情報を環境に合わせて変更する。

例)

ログファイル用フォルダ ¥¥Network-PC¥log ※1
コピー元フォルダ ¥¥Network-PC¥original ※1
ログインユーザ sa(SQL Serverログインユーザ)※2
パスワード (SQL Serverログインパスワード)※2
SQL Server接続先 localhost
sqlファイル用フォルダ C:¥script ※1
コピー先フォルダ ¥¥Network-PC¥copyto ※1

※1 フォルダ名に半角スペース等を含む場合は、"(ダブルクォーテーション)で囲む
※2 SQL Server認証の場合です。Windows認証の場合は、「sqlcmd -S SQL Server接続先 -E -i...」

③ 動作確認を行う。

ダブルクリックで起動し、ログファイル等を確認してください。

robocopyのオプション設定

/MIR バックアップ元とバックアップ先をミラーリングします。元と先でファイルが同じ状態になります。
/R:0 コピーを失敗した場合のリトライを0回にします。コピー失敗でリトライを繰り返しいつまでも終わらない状態を回避します。
/NDL ディレクトリ名をログに記録しません。ログがスッキリします。
/NP コピーの完了率を表示しません。少し処理速度が速くなります。

DBバックアップファイル、ログファイル削除の設定

/D -30 30日経過したファイルが削除対象となります。

3. タスクスケジューラに登録

① バックアップを実行するサーバーやパソコンで、タスクスケジューラを起動する。

タスクスケジューラ

② 基本タスクの作成を選択し、各設定を行う。

基本タスクの作成 名前を任意で設定 例)バックアップSQL
トリガー 毎日を選択
毎日 開始時刻を任意で設定、間隔は1日
操作 プログラムの開始を選択
プログラムの開始 参照や直接入力で、バッチファイルを選択
タスクスケジューラ設定

③ 動作確認を行う。

タスクスケジューラライブラリから登録した基本タスクを選択し、実行を選択すると起動します。
バッチファイル時の動作確認と同様に、ログファイル等を確認してください。

タスクスケジューラ動作確認

ログオフ中に起動する場合は、バックアップタスクのプロパティから次の設定を行ってください。

全般タブ 「ユーザーがログオンしているかどうかにかかわらず実行する」をONに設定
全般タブ 「最上位の特権で実行する」をONに設定
ログオフ実行