LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@kusokamayarou

SQLServer のバックアップをローカルと NAS と S3 に保存するバッチプログラムを作成してみる

Posted at

０．はじめに

イントラ内で利用されている
SQLServer のバックアップをローカルと NAS と S3 に保存したいと思い、
作成してみました。

仕様としては、以下。

  • バッチプログラムをローカル (Windows Server) で定期実行する。
  • サーバー内の全インスタンス・全データベースをバックアップする。
  • ローカルと NAS ではリビジョンを保存し、ローテートする。
  • 何かの時の為にログファイルを出力する。
  • 実行結果をメールで通知する。

１．事前準備

  1. sqlcmd を使える様にしておく。
  2. 7-zip を使える様にしておく。
  3. AWS CLI を使える様にしておく。
  4. 保存先の S3 バケットを作成しておく。
    • ※ 保存先の S3 への権限を持ったユーザーも合わせて作成しておく。

２．プログラム

  1. バックアッププログラム (.bat)

    MSSQL_BackUpDB.bat
    @echo off
setlocal ENABLEDELAYEDEXPANSION


rem # ==========================================================================
rem # 当該サーバ内の MSSQL の 全 DB をダンプする。
rem # 
rem # ※以下の項目を設定し直すこと!!
rem #   ・Common : 各種設定
rem #   ・Compress : 圧縮操作関連情報
rem #   ・DataBase : データベース関連情報
rem # 
rem # ==========================================================================

pushd %0\..
cls

rem # --------------------------------------------------------------------------
rem # Set
rem # --------------------------------------------------------------------------

rem # ----------------------------------
rem # Common
rem # ----------------------------------
title %0
set mypath=%~dp0
set mypath=%mypath:~0,-1%
set dt=%date:~-10%
set tm=%time: =0%
set yyyymmdd=%dt:~0,4%%dt:~5,2%%dt:~8,2%%tm:~0,2%%tm:~3,2%%tm:~6,2%
set USR_INPUT_STR=
set inputfolder=
set inputfolderpath=%mypath%\%inputfolder%
set inputfile=
set inputfilepath=%inputfolderpath%\%inputfile%
set outputfolder=%~n0_%yyyymmdd%
set outputfolderpath=%mypath%\%outputfolder%
set logfile=%~n0.log
set logfilepath=%outputfolderpath%\%logfile%
set tmpfile=%~n0.tmp
set tmpfilepath=%mypath%\%tmpfile%

rem # ----------------------------------
rem # Compress
rem # ----------------------------------
rem # zip, 7z, cab
set compress_ext_type=7z
set compress_file_max=10
set compress_cab_size_max=1024000000
set compress_cmd_zip=".\7za.exe"

rem # ----------------------------------
rem # DataBase
rem # ----------------------------------
rem set db_cmd_sqlcmd=sqlcmd -b -V1 -U [USER] -P [PASSWORD]
set db_cmd_sqlcmd=sqlcmd -b -V1 -E
set db_serverlist=%COMPUTERNAME%\SQLEXPRESS
rem for /f "usebackq tokens=*" %%Z in (` ^( sqlcmd -Lc ^| findstr %COMPUTERNAME%\ ^) `) do (
rem   set db_serverlist=!db_serverlist! %%Z
rem )

rem # --------------------------------------------------------------------------
rem # Program Start
rem # --------------------------------------------------------------------------

:do_1
rem # ----------------------------------
rem # Create Output Folder
rem # ----------------------------------
md %outputfolderpath%

echo outputfolderpath : %outputfolderpath%>> "%logfilepath%"
echo logfilepath : %logfilepath%>> "%logfilepath%"
echo tmpfilepath : %tmpfilepath%>> "%logfilepath%"

:do_2
rem # ----------------------------------
rem # Remove Compress File
rem # ----------------------------------
echo.
echo [%DATE% %TIME%] Remove Compress File - Start
echo.>> "%logfilepath%"
echo [%DATE% %TIME%] Remove Compress File - Start>> "%logfilepath%"
set /A count1=0
for /F %%A in ('dir /b /O:-N "%mypath%\%~n0_*.%compress_ext_type%"') do (
  set /A count1=count1+1
  if !count1! GTR %compress_file_max% (
    echo [!count1!] %%A - Remove ^( del /f /q "%%A" ^)
    echo [!count1!] %%A - Remove ^( del /f /q "%%A" ^)>> "%logfilepath%"
    del /f /q "%%A"
  ) else (
    echo [!count1!] %%A
    echo [!count1!] %%A>> "%logfilepath%"
  )
)
echo [%DATE% %TIME%] Remove Compress File - End
echo [%DATE% %TIME%] Remove Compress File - End>> "%logfilepath%"

:do_3
rem # ----------------------------------
rem # DB BackUp
rem # ----------------------------------
echo.
echo [%DATE% %TIME%] DB BackUp - Start
echo.>> "%logfilepath%"
echo [%DATE% %TIME%] DB BackUp - Start>> "%logfilepath%"
set csvfile=%~n0.csv
set csvfilepath=%outputfolderpath%\!csvfile!
echo csvfilepath : !csvfilepath!>> "%logfilepath%"
set /A count1=0
for %%Z in (%db_serverlist%) do (
  set /A count1=count1+1
  set /A count2=0
  set tmp_instance=
  for /f "usebackq tokens=2 delims=\ " %%X in ('%%Z') do (
    set tmp_instance=%%X
  )
  rem # Output Folder Access Set
  ICACLS %outputfolderpath% /grant SQLSERVERMSSQLUSER$%COMPUTERNAME%$!tmp_instance!:F /T>NUL 2>&1
  rem # Sqlcmd Check
  ( %db_cmd_sqlcmd% -S %%Z -h -1 -Q "set nocount on ; select name from sys.databases ;" ) > nul 2>nul
  if !errorlevel! equ 0 (
    for /f "usebackq " %%A in (` ^( %db_cmd_sqlcmd% -S %%Z -h -1 -Q "set nocount on ; select name from sys.databases ;" ^) `) do (
      set /A count2=count2+1
      set tmp_db=%%A
      rem # BackUp
      echo [!count1!,!count2!] %db_cmd_sqlcmd% -S %COMPUTERNAME%\!tmp_instance! -Q "backup database [!tmp_db!] to disk=N'%outputfolderpath%\%~n0_!tmp_instance!_!tmp_db!.bak' with init;"
      echo [!count1!,!count2!] %db_cmd_sqlcmd% -S %COMPUTERNAME%\!tmp_instance! -Q "backup database [!tmp_db!] to disk=N'%outputfolderpath%\%~n0_!tmp_instance!_!tmp_db!.bak' with init;">> "%logfilepath%"
      %db_cmd_sqlcmd% -S %COMPUTERNAME%\!tmp_instance! -Q "backup database [!tmp_db!] to disk=N'%outputfolderpath%\%~n0_!tmp_instance!_!tmp_db!.bak' with init;"
      echo !tmp_instance!,!tmp_db!,%~n0_!tmp_instance!_!tmp_db!.bak>> "!csvfilepath!"
    )
  ) else (
    echo [!count1!,!count2!] Z:[%%Z] - Sqlcmd Error .
    echo [!count1!,!count2!] Z:[%%Z] - Sqlcmd Error .>> "%logfilepath%"
  )
)
echo [%DATE% %TIME%] DB BackUp - End
echo [%DATE% %TIME%] DB BackUp - End>> "%logfilepath%"

:do_3.5
rem # ----------------------------------
rem # Sync S3
rem # ----------------------------------
set aws_access_key_id=[ACCESSKEY]
set aws_secret_access_key=[SECRETKEY]
set region=ap-northeast-1
"C:\Program Files\Amazon\AWSCLI\aws.exe" s3 sync "%outputfolderpath%" s3://[保存先の S3 バケット]>> "%logfilepath%" 2>&1

:do_4
rem # ----------------------------------
rem # Compress Output Folder
rem # ----------------------------------
echo.
echo [%DATE% %TIME%] Compress Output Folder - Start
echo.>> "%logfilepath%"
echo [%DATE% %TIME%] Compress Output Folder - Start>> "%logfilepath%"
rem # Compress
if "%compress_ext_type%"=="cab" (
  rem # cab
  del /f /q "!tmpfilepath!" %mypath%\setup.* >nul 2>nul
  for /f "usebackq " %%A in (` ^( dir /b /a-d "%outputfolderpath%\" ^) `) do (
    echo "%outputfolderpath%\%%A">> "!tmpfilepath!"
  )
  echo makecab /D MaxDiskSize=%compress_cab_size_max% /D CabinetNameTemplate=%outputfolder%_*.%compress_ext_type% /D DiskDirectoryTemplate= /F "!tmpfilepath!"
  echo makecab /D MaxDiskSize=%compress_cab_size_max% /D CabinetNameTemplate=%outputfolder%_*.%compress_ext_type% /D DiskDirectoryTemplate= /F "!tmpfilepath!">> "%logfilepath%"
  makecab /D MaxDiskSize=%compress_cab_size_max% /D CabinetNameTemplate=%outputfolder%_*.%compress_ext_type% /D DiskDirectoryTemplate= /F "!tmpfilepath!"
  del /f /q "!tmpfilepath!" %mypath%\setup.* >nul 2>nul
) else if "%compress_ext_type%"=="zip" (
  rem # zip
  echo %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\"
  echo %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\">> "%logfilepath%"
  %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\"
) else if "%compress_ext_type%"=="7z" (
  rem # 7z
  echo %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\"
  echo %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\">> "%logfilepath%"
  %compress_cmd_zip% a "%outputfolderpath%.%compress_ext_type%" "%outputfolderpath%\"
) else (
  echo compress_ext_type:[%compress_ext_type%] - Extension Type Error .
  echo compress_ext_type:[%compress_ext_type%] - Extension Type Error .>> "%logfilepath%"
  goto end
)
rem echo [%DATE% %TIME%] Compress Output Folder - End
rem echo [%DATE% %TIME%] Compress Output Folder - End>> "%logfilepath%"

:do_5
rem # ----------------------------------
rem # Remove Output Folder
rem # ----------------------------------
echo rd /S /Q "%outputfolderpath%\"
rd /S /Q "%outputfolderpath%\"

:do_6
rem # ----------------------------------
rem # Remote Mirror
rem # ----------------------------------
set remotefolderpath=[保存先の NAS のパス]
robocopy "%mypath%" "%remotefolderpath%" /MIR /XA:SH /XO

:do_7
rem # ----------------------------------
rem # Mail Send
rem # ----------------------------------
echo %dt% %tm% - %date:~-10% %time: =0%> "%tmpfilepath%"
echo.>> "%tmpfilepath%"
echo ---->> "%tmpfilepath%"
dir /on "%mypath%">> "%tmpfilepath%"
echo.>> "%tmpfilepath%"
echo ---->> "%tmpfilepath%"
echo.>> "%tmpfilepath%"
dir /on "%remotefolderpath%">> "%tmpfilepath%"
echo.>> "%tmpfilepath%"
cscript.exe "%mypath%\MSSQL_BackUpDB_MailSend.vbs" "Complete" "%tmpfilepath%"
rem del /f /q "%tmpfilepath%" >nul 2>nul

rem # --------------------------------------------------------------------------
rem # Program End
rem # --------------------------------------------------------------------------

:end
rem pause

exit /b

  2. メール送信用プログラム (.vbs)

    MSSQL_BackUpDB_MailSend.vbs
    '****************************************************************
' 
'****************************************************************

strEventType = "???"
strMessage = "???"

Set oParam = WScript.Arguments

Set objWshShell = CreateObject("WScript.Shell")
For idx = 0 To oParam.Count - 1
  'WScript.echo idx & ":" & oParam(idx)
  Select Case idx
    Case 0
      strEventType = oParam(idx)
    Case 1
      Set objStream = CreateObject("ADODB.Stream")
      objStream.Charset = "Shift_JIS"
      objStream.Open
      objStream.LoadFromFile oParam(idx)
      strMessage = objStream.ReadText
      'WScript.echo "strMessage : " & strMessage
      objStream.Close
      Set objStream = Nothing
    Case Else
      '
  End Select
Next
Set objWshShell = Nothing

Set objNetwork = CreateObject("WScript.Network")
strHostName = objNetwork.ComputerName
Set objNetwork = Nothing

'****************************************************************
'メール送信元の設定
'****************************************************************

strFrom = "[送信元メールアドレス]"
strReply = "[送信元メールアドレス]"

strServer = "[メールサーバ]"
nPort = 587
strUser = "[送信ユーザーID]"
strPass = "[送信ユーザーパスワード]"

'****************************************************************
'メール送信先の設定＆メール本体の作成
'****************************************************************

strTo = "[送信先メールアドレス]"
strCc = ""
strBcc = ""

strSubject = "MSSQL DB Backup - " & strEventType & " (" & strHostName & ")"
strTextbody = strMessage

'strAddAttachment = ""

'****************************************************************
' メール送信
'****************************************************************

Set Cdo = CreateObject("CDO.Message")

Cdo.From = strFrom
Cdo.ReplyTo = strReply
Cdo.To = strTo
Cdo.Cc = strCc
Cdo.Bcc = strBcc
Cdo.Subject = strSubject
Cdo.Textbody = strTextbody
'Cdo.AddAttachment strAddAttachment

Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strServer
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = nPort
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
'Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUser
Cdo.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strPass

Cdo.Configuration.Fields.Update

on error resume next
Cdo.Send
on error goto 0

'****************************************************************
' 終了
'****************************************************************
Wscript.Quit(Err.Number)

９９．ハマりポイント

  • すいません。結構前に作成したのです、覚えてないです…。

ＸＸ．まとめ

色々と突っ込みどころのあるプログラムかも知れませんが、
その際は、コメント下さい。

よろしくお願いしますします。

次は、
S3 から RDS へのリストアを登校しようかと思っています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?