【SQLServer】バックアップするバッチファイル～sqlcmd編～

【SQLServer】バックアップするバッチファイル～sqlcmd編～

最近の記事。開発ネタが多いのだが、開発する事がもともと好きだからなぁ。

開発自体が趣味なんですよｗｗｗ

外遊びも好きなんだが、パソコンいじりも好きだし。
インドアでもアウトドアでもイケる感じ。
体が足りないですわ。テントで開発すればいいか？。。。

さて今回はSQLServerネタ。
この仕事をしていると、バックアップ体制はどうするか？なんてことを考えておかないといけない。
データに不具合ができたり、そもそも物理的に破損してしまったり。
定期バックアップは必須ですね。
それをsqlcmdで行うバッチファイルとSQLファイルを作成する。

早速。

バックアップ用のSQL作成

まずはバックアップ時にバッチファイルが実行するSQLファイルを作成する。

backup.sql

/* データベース名 */
DECLARE @DATABASE_NAME  VARCHAR(MAX)    = 'hoge_database';

/* 拡張子 */
DECLARE @EXTENSION_NAME VARCHAR(MAX)    = '.bak';

/* ディレクトリ名 */
DECLARE @DIRECTRY_PATH  VARCHAR(MAX)    = 'E:\backup\';

/* ファイル名 */
DECLARE @FILE_NAME      VARCHAR(MAX)    = '';

/* ファイルフルパス作成 */
SET @FILE_NAME = @DIRECTRY_PATH + FORMAT(CURRENT_TIMESTAMP,'yyyyMMddHHmmss') + '_' + @DATABASE_NAME + @EXTENSION_NAME;

/* バックアップ実行 */
BACKUP DATABASE @DATABASE_NAME TO DISK=@FILE_NAME WITH INIT
GO

単純にファイル名を作成して指定された保存先にバックアップを出力する、というSQLを作成。

バッチファイルの作成

次に先ほど作成したSQLファイルを実行するバッチファイルを作成する。

rem バックアップスクリプト実行
sqlcmd -d hoge_database -E -S piyo_server -i backup.sql > Backup.log

hoge_serverはデータベース名。
piyo_serverはサーバ名。

パラメタはこんな感じ。
・-d データベース名の使用
・-E セキュリティ接続
・-S サーバー
・-i 入力ファイル

各パラメタ

各パラメタの意味が分からん。
なんて時に。
ヘルプを表示するには、
こうだ。

sqlcmd -?
Microsoft (R) SQL Server コマンド ライン ツール
バージョン 14.0.2027.2 NT
Copyright (C) 2017 Microsoft Corporation. All rights reserved.

使用法: Sqlcmd            [-U ログイン ID]          [-P パスワード]
  [-S サーバー]        [-H ホスト名]              [-E セキュリティ接続]
  [-N 暗号化接続]      [-C サーバー証明書を信頼]
  [-d データベース名の使用]  [-l ログイン タイムアウト] [-t クエリ タイムアウト]
  [-h ヘッダー]        [-s 列の区切り文字]        [-w 表示幅]
  [-a パケット サイズ] [-e 入力エコー]            [-I 識別子を引用符で区切る]
  [-c コマンド終了]    [-L[c] サーバー一覧[クリーン アウトプット]]
  [-q "コマンドライン クエリ"]          [-Q "コマンドライン クエリ" と終了]
  [-m エラー レベル]   [-V 重大度レベル]          [-W 余分なスペースを削除]
  [-u ユニコード出力]  [-r[0|1] stderr へメッセージを出力]
  [-i 入力ファイル]    [-o 出力ファイル]          [-z パスワード変更]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z パスワード変更と終了]
  [-k[1|2] 制御文字の削除[置き換え]]
  [-y 可変長型の表示幅]
  [-Y 固定長型の表示幅]
  [-p[1] 統計情報の出力[コロン区切り形式]]
  [-R クライアントの地域設定を使用]
  [-K アプリケーション インテント]
  [- M マルチサブネット フェールオーバー]
  [-b エラー時にバッチを中止]
  [-v var = "value"...][-A 専用管理者接続]
  [-X[1] コマンド、スタートアップ スクリプト、環境変数を無効化 [終了]]
  [-x 変数の代入を無効化]
  [-j 未加工エラー メッセージを印刷]

結果

バックアップファイルのファイル名に日時を埋め込んでいるからいつのバックアップかわかりますね。

ちなみにbackup.logの内容はこんな感じ。

データベース 'hoge_database' の 728 ページ、ファイル 1 のファイル 'hoge_database' を処理しました。
データベース 'hoge_database' の 2 ページ、ファイル 1 のファイル 'hoge_database_log' を処理しました。
BACKUP DATABASE により 730 ページが 0.348 秒間で正常に処理されました (16.388 MB/秒)。

あとはスケジューラ等で定期的に実行してあげればよいかと。
ファイルの削除処理も忘れずに追加する必要あり。毎日バックアップしてたら肥大化してしまいますもの。