「Microsoft SQL Server」で複数のテーブルのバックアップをタスクスケジューラーに登録して定期的に取得したくて、いい方法が無いか考えてみました。

SQLCMDとは？

「Microsoft SQL Server」に付属するバッチファイルやコマンドラインからSQLサーバーを管理するためのツールです。
「Microsoft SQL Server」がインストールされていない別な端末から実行する場合は単体でのダウンロードも可能です。
https://www.microsoft.com/ja-jp/download/details.aspx?id=52676

SQLCMDのコマンド例とオプション

今回使用するコマンドとオプションです。その他の詳しい内容はこちらを参考にしてください。
https://msdn.microsoft.com/ja-jp/library/ms162773.aspx

sqlcmd -S server0 -U sa -P password -d database0 -i SQLTXT0.sql

テーブル一覧を取得してテキストに出力

まずはテーブルの一覧を取得します。SQL文はこんな感じです。

SELECT name FROM sys.databases

前に「SET NOCOUNT ON」をつけると最後行に出力される件数が表示されなくなります。
SQLCMDコマンドで結果をテキストに出力します。

sqlcmd -S server1\database1 -E -i SQLTXT1.sql -s, -W -h -1 -o tabellist.txt

出力したテキストを読み込んで各テーブルのバックアップを取得

出力したテキストを「ReadLine」で1行づつ読み込みます。

Set readFile = objFSO.OpenTextFile(strFolder & "\tabellist.txt")
	Do While readFile.AtEndOfStream <> True
		strReadLine = Trim(readFile.ReadLine)		
	Loop
readFile.close

完成したスクリプト

最終的に完成したVBScriptはこんな感じです。

SQLTXT[n].sql ・・・ SQL文を書いたテキストファイル
server1\database1 ・・・ 対象サーバー名／データベース名
tabellist.txt ・・・ テーブル名の出力結果です。

Dim strPath, strFolder, strReadLine
Dim objShell, objFSO, objFile, outSQL1, outBat1, outSQL2, readFile

Set objShell = WScript.CreateObject("WScript.Shell") 

strPath = Wscript.ScriptFullName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(strPath) 
strFolder = objFSO.GetParentFolderName(objFile)  

'sqlcmdでデータベース名を取得する
Set outSQL1 = objFSO.OpenTextFile(strFolder & "\SQLTXT1.sql",2,True)
	outSQL1.WriteLine "SET NOCOUNT ON SELECT name FROM sys.databases"
outSQL1.close

Set outBat1 = objFSO.OpenTextFile(strFolder & "\getDB.bat",2,True)
	outBat1.WriteLine "sqlcmd -S server1\database1 -E -i SQLTXT1.sql -s, -W -h -1 -o tabellist.txt"
outBat1.close

objShell.RUN strFolder & "\getDB.bat",0

'ちょっと時間をおく
WScript.Sleep 10000

'ファイルがうまく生成されなかったときは実行しない
If Not objFso.FileExists(strFolder & "\tabellist.txt") Then
	WScript.Quit
Else
	If objFile.Size = 0 then
		WScript.Quit
	End If
End If

'sqlcmdで取得したデータベースをバックアップする
Set outSQL2 = objFSO.OpenTextFile(strFolder & "\SQLTXT2.sql",2,True)
	Set readFile = objFSO.OpenTextFile(strFolder & "\tabellist.txt")
	Do While readFile.AtEndOfStream <> True
		strReadLine = Trim(readFile.ReadLine)
		If Not strReadLine = "" Then
			outSQL2.WriteLine "BACKUP DATABASE " & strReadLine & " TO DISK = N'" & strFolder & "\bak\" & strReadLine & ".BAK' WITH INIT"
		End If
	Loop
	readFile.close
outSQL2.close

Set outBat2 = objFSO.OpenTextFile(strFolder & "\backupDB.bat",2,True)
	outBat2.WriteLine "sqlcmd -S server1\database1 -E -i SQLTXT2.sql >> sqlbaklog.txt"
outBat2.close

objShell.RUN strFolder & "\backupDB.bat",0

Set readFile = Nothing
Set outBat2 = Nothing
Set outBat1 = Nothing
Set outSQL2 = Nothing
Set outSQL1 = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set objShell = Nothing

一度テキストに書き出さなくても可能ですが、確認のためこのような形にしてみました。もっとスマートな方法もあるのでは？と思いつつ今回はこんな感じです。参考までに。

自己紹介

松田 大と申しますm(_ _)m
インディーズでミュージシャンをやっていたのですがいつのまにか…

とある企業でショップのアルバイトスタッフから正社員、支店長を経てシステム部門に異動するという、開発担当としては変わった経緯を持っている方だと思います。

「Excel VBA」からスタートして、Yamaha RTX シリーズで VPN環境構築、Hyper-V環境構築、Windowsアプリ開発などを経験した後、「 WordPress 」に出会い、どっぷりハマっています。

現在勤めているETBS合同会社では、「 WordPress 」を活用したWEBサイト、業務用WEBアプリケーション開発を中心に、記事の執筆代行や掲載に必要な情報のリサーチ、映像のテロップ入れや切りはりなどの簡単な動画編集なども、まとめて行なっています。

現在、代表兼二児のパパ。子育てを通じて、こどもたちにもプログラミングの楽しさに触れてほしいと思うようになり、「 こどもICTかつしか教室 」を開講中。最近は童心に帰り、簡単なゲーム制作なんかも楽しんでいます(^_^)。

お問い合わせ