SQLCMDで複数のテーブルをバックアップする方法

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


データベース初心者でも扱いやすいマイクロソフトの最新データベースです。リアルタイム運用分析、モバイル デバイスのリッチな視覚化、組み込みの高度な分析など新たにが搭載されています。

SQL Server 2016 Standard 日本語版 10CAL付


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
オプション 説明
-S サーバ名
-U ログイン
-P パスワード
-d データベース
-i スクリプトファイル

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

まずはテーブルの一覧を取得します。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
オプション 説明
-E Windows認証を使用してデータベースに接続
-s 区切り文字
-W 余計な空白を省く
-h -1でヘッダ非表示
-o 出力ファイル

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

出力したテキストを「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

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

応援よろしくお願いします。
いいね ! してもらえると嬉しいです。

Twitter で

自己紹介

インディーズでミュージシャンをやっていたのですがいつのまにか...

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

昨年の3月に結婚し、尻敷かれ男子の仲間入りを果たしました(笑)

現在勤めているJTクラウドシステムでは、「 Excel VBA 」を活用した効率化ツールの開発、「 WordPress 」を活用したWEBサイト、業務用WEBアプリケーション開発を中心に「 ネットワーク・サーバー構築 」や「 Office365製品の導入支援 」、最近は、ホームページ制作に加え、記事の執筆代行や掲載に必要な情報のリサーチ、テロップ入れや映像の切りはりなどの簡単な動画編集なども、まとめて行なっています。

お問い合わせはJTクラウドシステムホームページからお願いします。
スポンサーリンク