SQL Server Expressを自動バックアップ

前回はMySQLの自動バックアップを行ったので、次はSQLServerについても同様にバックアップを。

有償版のSQL ServerならSQL Server Management StudioのSQL Serverエージェントで定期バックアップが可能ですが、Express版ではタスクスケジューラなどを使う必要があるみたいです。
使い方は下記2つのファイルを同じフォルダに置き、ソース内のバックアップ設定場所などを適宜設定してください。

タスクスケジューラに登録するときはbackupSQLServerAll.batのほうを登録。
なお、データベース毎にバックアップファイルを生成するためファイル数が多くなってしまうので前回のMySQLバックアップとは異なり、日付のフォルダを生成してからバックアップファイルを生成するようにしました。ただ前回と同様、バックアップファイルは自動で削除されないのでお気をつけて。

---

2014/03/07 追記
WindowsServer2008R2のタスクスケジューラで動かない場合は、下記をご参考ください
Windows 2008 Server R2上のタスクスケジューラで動かないバッチ - 通勤ノート

---

backupSQLServerAll.bat

@ECHO OFF
REM ------------------------------------
REM SQLServer全体バックアップBatch
REM backupSQLServerAll.bat
REM ------------------------------------
 
setlocal
 
REM --- userSetting start --------------
 
REM uDirにバックアップ先フォルダを指定します。
set uDir=D:\DBBACKUP\sqlserver\
 
REM uSrvNameに接続するデータベースサーバーのインスタンス名を指定します。
set uSrvName=localhost\sqlexpress
 
REM uConnectに接続方式を指定します。
REM     Windows認証の場合は -E
REM     SQL Server認証の場合は -U [ユーザー名] -P [パスワード]
set uConnect=-E
 
REM --- userSetting end ----------------
 
cd /d %~dp0
set dt=%date%
set fDate=%dt:~-10,4%%dt:~-5,2%%dt:~-2,2%
set fDir=%uDir%%fDate%
 
mkdir %fDir%
 
sqlcmd -S %uSrvName% %uConnect% -i sqlserverBackup.sql
 
endlocal

sqlserverBackup.sql

/*
* データベース自動バックアップScript
* sqlserverBackup.sql
*/
 
--- userSetting START ------------------
 
-- uPathにバックアップ保存先のフォルダを指定します。
DECLARE @uPath varchar(200)= 'D:\DBBackup\sqlserver\'
 
-- uSVNameに任意のサーバー名を指定します。
DECLARE @uSVName varchar(200)= 'devPC'
 
--- userSetting END --------------------
 
DECLARE @fDate datetime = GETDATE()
DECLARE @fDateStr char(8) = CONVERT(char(8), @fDate, 112)
DECLARE @fExt varchar(200) = '.bak'
 
-- DB名の一覧を取得します。
-- WHERE以下でシステムDBを除外のつもり。（問題があればWHERE以下を削除してください。）
DECLARE dbCursor CURSOR FOR
SELECT name FROM sys.databases WHERE owner_sid <> 0x01
 
OPEN dbCursor
DECLARE @dbName varchar(200)
FETCH NEXT FROM dbCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @fComment varchar(200) = @dbName + '-完全バックアップ'
DECLARE @fName varchar(600) = @uPath + @fDateStr + '\\' + @uSVName + '-' + @dbName + '-' + @fDateStr + @fExt
 
BACKUP DATABASE @dbName TO DISK = @fName WITH INIT, NAME = @fComment
 
FETCH NEXT FROM dbCursor INTO @dbName
END
CLOSE dbCursor
DEALLOCATE dbCursor
GO