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

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

仕様としては、以下。

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

# １．事前準備

1. sqlcmd を使える様にしておく。
    * [sqlcmd を単体でインストールして使用する方法 \- Microsoft SQL Server \- Project Group](https://www.projectgroup.info/tips/SQLServer/MSSQL_00000002.html)
<br><br>
1. 7-zip を使える様にしておく。
    * [Download](http://www.7-zip.org/download.html)
<br><br>
1. AWS CLI を使える様にしておく。
    * [Microsoft Windows で AWS Command Line Interface をインストールする \- AWS Command Line Interface](http://docs.aws.amazon.com/ja_jp/cli/latest/userguide/awscli-install-windows.html)
<br><br>
1. 保存先の S3 バケットを作成しておく。
    * ※ 保存先の S3 への権限を持ったユーザーも合わせて作成しておく。

# ２．プログラム

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

    ```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
    ```

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

    ```VBScript: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 へのリストアを登校しようかと思っています。