November 14, 2012 at 09:41

Comprehensive Automation of Firebird / InterBase Database Backups on Windows Servers

image
The material below may be useful to novice database administrators who have realized the importance of creating a backup system, but have not yet got hold of their own scripts or utilities. Below I will give a script in the form of a command bat-file used in our organization to automate the removal of backups from Firebird databases, I will analyze its key fragments and give examples of use. It is also suitable for Interbase or Yaffil databases, but I believe that with a minimal alteration of the backup utility call, it can be adapted for almost any DBMS.

First of all, I give a couple of links on which you can and should familiarize yourself with the theory of the issue:
firebirdsql.org: Firebird's gbak Backup and Restore tool (English)
ibase.ru: GBAK, Firebird and InterBase utility

A bit about the terms:
  • Backup ( b ) - the process of removing a backup from the database. At the output, we have a file in a special format, which is not a database, but which can be deployed to a new database. Usually it is much lighter than the original database file due to the lack of index bodies, not using the space reservation on the data pages, the absence of "junk" records and other factors that play into compaction.
  • Restore, or restor ( r ) - the process of creating a new database from a backup file. The resulting database will be equivalent to the original at the time the backup starts. At the recovery stage, you can additionally change the owner of the database, change the page size of the database, set a new cache size. In scripts, the restor often plays a supporting role - checking that the backup file is received correctly and suitable for restoring the database.
  • b / r - a set of backup and recovery processes, as a result of which we get a fresh, uncluttered database, with reset transaction counts, and occupying minimal disk space. If anyone did not know: Firebird / InterBase databases can only grow in size, but they never return “extra” pages to the file system. Therefore, b / r is the only way to “lose weight” for the database if it bothers you. Also, b / r is an extremely desirable option when transferring a database between servers, especially when it comes to different OSs, and even more so about different iron architectures.

So, what activities make up the database backup process, if you approach the issue comprehensively, and not just confine yourself to running gbak.exe with the necessary keys? I would single out the following main ones:
  1. Check that the destination directory exists, and if necessary create it (not all utilities will create a directory structure if it turns out that the output file needs to be placed in a non-existent directory).
  2. Removing a backup from the database using the standard utility from the server.
  3. An optional test restore of a backup copy (alas, there are occasionally cases when the server created a backup file, but could not restore it to a new database due to errors in metadata ).
  4. Optional compression of the backup file by the archiver.
  5. Optional control of stored backups by the number of files and / or by the space occupied by them.
  6. Notify the administrator of any kind of failures that occurred in the previous steps by email, NET SEND transport, or other means. It is also advisable to maintain a cumulative failure log.

Our script, of course, can do all of the above, but it can also do something else:
  • Work with any database: local or remote; with a fully specified path, or through an alias; with optional port indication, if non-standard is used.
  • In the case of a local database, backup is used through services , which can significantly reduce the operation time. The test restore is always performed on the local machine, and occurs through services.
  • By default, garbage collection in the original database is disabled at the backup stage (there is an enable option), which allows to reduce the operation time.
  • RAR is used as the backup archiver, and the degree of compression (and therefore the speed of the operation) is set from 1 (low compression, best speed) to 5 (best compression, long time). A compression value of 0 means "do not archive, save the backup file unchanged." You can easily replace rar calls with another archiver, for example, free 7zip. But rar is noticeably faster in our tests so far.
  • An undefined number of files (possibly through a mask) can be transferred to the script, which should be added to the final archive. We take this opportunity so that with each backup of the database there is an executable file of the application program that serves this database. A useful thing when you have to raise an archive 6-12 months ago: right at hand is the program that is relevant for the old database, which can work with that version of metadata.
  • There is an optional ability to add b / r logs inside the archive with backup. In case of "debriefing" ex post.
  • The number of stored backups, if not redefined, is 30 pcs. Upon reaching the specified number of copies in the destination folder, the oldest backups will be deleted, freeing up space for new ones. Setting the value of the parameter to 0, you can turn off the control of the number of backups.
  • The volume of stored backups is not controlled by default, but can be specified in bytes, Kb, Mb or GB.
  • b / r is by default executed on behalf of the built-in superuser SYSDBA. The SYSDBA password of the local server is stored in the source. And to perform backup over the network, the script can be passed a parameter with the SYSDBA password of the remote server. In especially paronoidal cases, the SYSDBA password of the local server can be deleted from the source and also passed as a parameter when called.
  • As a mailer, the well-known Blat utility is used among system administrators .
  • The output file (archive or regular backup file, if compression is disabled) contains the date and time stamp of its creation, which allows you to visually navigate in the backup directory.

The entry was delayed; under the spoiler I bring the full source code of the script.
source fb_backup.bat
@CLS
@ECHO OFF
ECHO #=============================================================================#
ECHO #                                                                             #
ECHO # Firebird/InterBase database backup, test restore, zip and rotate script     #
ECHO # Ver 3.2.8 (26.01.2013)                                                      #
ECHO #                                                                             #
ECHO # Author: arni (email:arnisoft at rambler dot ru)                             #
ECHO #                                                                             #
ECHO # Format:                                                                     #
ECHO # FB_BACKUP [host[/port]:][path]db_file_or_alias  result_dir                  #
ECHO #           [/count:backup_count]  [/space:backup_space_limit]  [/gc]         #
ECHO #           [/restore]  [/compress:level]  [/password:SYSDBA_password]        #
ECHO #           [other_files_to_compress [...]]                                   #
ECHO #                                                                             #
ECHO # Input params:                                                               #
ECHO # [host[/port]:][path]      : local or network, full-specified path or alias  #
ECHO #   db_file_or_alias            to the source database                        #
ECHO # result_dir                : result backup collecting directory              #
ECHO # /count:backup_count       : backup file number to keep (30 by default)      #
ECHO # /space:backup_space_limit : total backup size in bytes (not use by default) #
ECHO #                               you can use suffixes K, M or G.               #
ECHO # /gc                       : need to collect garbage in DB (OFF by default)  #
ECHO # /restore                  : need to do test restore (OFF by default)        #
ECHO # /compress:level           : compress ratio for RAR (2 by default):          #
ECHO #                               0: not compress, 1: fastest, 2: fast,         #
ECHO #                               3: normal, 4: good, 5: best                   #
ECHO # /password:SYSDBA_password : optional SYSDBA password for remote server      #
ECHO #                               (by default uses one from the source code)    #
ECHO # other_files_to_compress   : list of files that must be add to archive       #
ECHO #                                                                             #
ECHO #=============================================================================#
REM ==== Server ====================================================================
SET gbak="C:\Programs\FB25\bin\gbak.exe"
SET ISC_USER=SYSDBA
SET ISC_PASSWORD_LOCAL=masterkey
SET ISC_PASSWORD_REMOTE=
REM ==== Backup/restore preferences ================================================
SET temp_backup_dir=%TEMP%
SET temp_restore_dir=%TEMP%
SET backup_count=30
SET backup_space_limit=0
SET backup_ext=fbk
SET garbage_collection=-g
SET restore=0
REM ==== RAR =======================================================================
SET rar="C:\Program Files\WinRAR\rar.exe"
SET rar_options=a -y -ep -idcd
SET rar_password=
SET rar_compress_ratio=2
REM ==== Mailer (see "Blat" at http://sourceforge.net/projects/blat) ===============
SET blat="C:\Programs\Blat307\blat.exe"
SET smtp_server=smtp.mailserver.ru
SET mail_sender=foo@mailserver.ru
SET mail_login=foo
SET mail_password=1234
SET mail_receiver=
SET mail_subject=Fail while database b/r
REM ==== Other preferences =========================================================
SET include_logs_to_archive=1
SET net_send_receiver=
SET error_log=
REM ==== Define database location ==================================================
SET full_db_specification=%~1
ECHO full_db_specification   = %full_db_specification%
REM Devide DB spec to network and local parts
FOR /f "DELIMS=: TOKENS=1*" %%i IN ("%full_db_specification%") DO (
  SET network=%%i
  SET local=%%j
)
REM Test if spec. is alias with no network part
IF "%local%" == "" (
  SET network=
  SET local=%full_db_specification%
)
REM Test if spec. is full specified file with no network part
FOR /f "DELIMS=\ TOKENS=*" %%i IN ("%local%") DO IF "%local%" == "\%%i" (
  SET network=
  SET local=%full_db_specification%
)
ECHO network_specification   = %network%
IF "%network%" GTR "" (
  REM Extract port from network spec (if exists)
  FOR /f "DELIMS=/ TOKENS=1*" %%i IN ("%network%") DO (
    SET host=%%i
    SET port=%%j
    IF "%%j" GTR "" (
      ECHO network_host            = %%i
      ECHO network_port            = %%j
    )
  )
)
REM Test if DB is local or remote
SET service_mgr_host=localhost
SET is_local_db=1
IF "%network%" GTR "" IF "%host%" NEQ "127.0.0.1" IF /i "%host%" NEQ "localhost" SET is_local_db=0
IF %is_local_db% == 1 IF "%network%" GTR "" SET service_mgr_host=%network%
ECHO local_db_specification  = %local%
IF "%local%" == "" (
  SET fail=Param #1 {DB specification} missing!
  GOTO finish
)
REM Extract file (or alias) from local part of spec.
FOR /f %%i IN ("%local%") DO (
  SET local_path=%%~dpi
  SET local_file_or_alias=%%~nxi
)
IF "%local%" NEQ "%local_file_or_alias%" (
  ECHO local_path              = %local_path%
  REM Check DB file exists for local, not aliased specification
  IF %is_local_db% == 1 IF NOT EXIST "%local%" (
    SET fail=Local DB file %local% not found!
    GOTO finish
  )
)
ECHO local_db_file_or_alias  = %local_file_or_alias%
REM ==== Define result directory ===================================================
SET result_dir=%~2
ECHO result_dir              = %result_dir%
IF "%result_dir%" == "" (
  SET fail=Param #2 {backup collecting directory} missing!
  GOTO finish
)
REM Cut the result dir if it is in path-style (ends with separator)
IF "%result_dir:~-1%" == "\" SET result_dir=%result_dir:~0,-1%
REM Try to create the result directory if it is not exists yet
IF NOT EXIST "%result_dir%" (
  MD "%result_dir%"
  IF NOT EXIST "%result_dir%" (
    SET fail=Cannot create backup collecting directory!
    GOTO finish
  )
)
REM Test if it is local or remote directory (elementary, may get wrong answer)
SET is_local_result_dir=1
IF "%result_dir:~0,2%" == "\\" SET is_local_result_dir=0
REM ==== Use other command line options ============================================
:loop_options
SHIFT
SET next_param=%~2
IF "%next_param%" == "" GOTO print_options
SET prefix=%next_param:~0,1%
IF "%prefix%" == "/" SET next_param=%next_param:~1%
IF "%prefix%" == "-" SET next_param=%next_param:~1%
IF "%next_param%" GTR "" (
  FOR /f "DELIMS=: TOKENS=1*" %%i IN ("%next_param%") DO (
    SET value=%%j
    IF /i "%%i" == "count" IF "%%j" GTR "" GOTO count
    IF /i "%%i" == "space" IF "%%j" GTR "" GOTO space
    IF /i "%%i" == "gc" GOTO gc
    IF /i "%%i" == "restore" GOTO restore
    IF /i "%%i" == "compress" IF "%%j" GTR "" GOTO compress
    IF /i "%%i" == "password" IF "%%j" GTR "" GOTO password
    IF EXIST "%next_param%" GOTO add_file_to_compress
    IF "%prefix%" GTR "/" GOTO add_file_to_compress
    ECHO unknown param found: %next_param%
    GOTO loop_options
  )
) ELSE (
  ECHO empty param found!
  GOTO loop_options
)
REM ==== Define file count in the result dir =======================================
:count
SET /a backup_count=0+%value%
GOTO loop_options
REM ==== Define allowed backup space limit =========================================
:space
SET suffix=%value:~-1%
IF "%suffix%" GTR "9" (
  SET value=%value:~0,-1%
  IF /i "%suffix%" == "K" (
    SET file_size_shift=0
    SET /a value*=1000
  )
  IF /i "%suffix%" == "M" (
    SET file_size_shift=3
    SET /a value*=1000
  )
  IF /i "%suffix%" == "G" (
    SET file_size_shift=6
    SET /a value*=1000
  )
)
SET /a backup_space_limit=0+%value%
GOTO loop_options
REM ==== Define need of garbage collection =========================================
:gc
SET garbage_collection=
IF "%value%" == "0" SET garbage_collection=-g
IF /i "%value%" == "N" SET garbage_collection=-g
IF /i "%value%" == "NO" SET garbage_collection=-g
IF /i "%value%" == "OFF" SET garbage_collection=-g
GOTO loop_options
REM ==== Define need of test restore ===============================================
:restore
SET restore=1
IF "%value%" == "0" SET restore=0
IF /i "%value%" == "N" SET restore=0
IF /i "%value%" == "NO" SET restore=0
IF /i "%value%" == "OFF" SET restore=0
GOTO loop_options
REM ==== Define need of backup compression and compress ratio ======================
:compress
IF "%value%" GEQ "0" IF "%value%" LEQ "5" SET rar_compress_ratio=%value%
GOTO loop_options
REM ==== Define SYSDBA password (in addition or for replace source code given) =====
:password
if "%ISC_PASSWORD_LOCAL%" GTR "" if "%ISC_PASSWORD_REMOTE%" GTR "" (
  SET ISC_PASSWORD_LOCAL=%value%
  SET ISC_PASSWORD_REMOTE=%value%
)
if "%ISC_PASSWORD_LOCAL%" == "" SET ISC_PASSWORD_LOCAL=%value%
if "%ISC_PASSWORD_REMOTE%" == "" SET ISC_PASSWORD_REMOTE=%value%
GOTO loop_options
REM ==== Define file list to compress (in addition to backup and maybe logs) =======
:add_file_to_compress
IF "%backup_files%" == "" (
  SET backup_files="%next_param%"
) ELSE (
  SET backup_files=%backup_files% "%next_param%"
)
GOTO loop_options
REM ==== Print predefined or recognized in command line options ====================
:print_options
IF %backup_count% GTR 0 (
  ECHO backup_count            = %backup_count%
) ELSE (
  ECHO backup_count            = OFF
)
IF "%file_size_shift%" == "" SET file_size_shift=0
IF %backup_space_limit% GTR 0 (
  IF %file_size_shift% == 6 (
    ECHO backup_space_limit      = %backup_space_limit% Mb
  ) ELSE IF %file_size_shift% == 3 (
    ECHO backup_space_limit      = %backup_space_limit% Kb
  ) ELSE ECHO backup_space_limit      = %backup_space_limit% bytes
) ELSE (
  ECHO backup_space_limit      = OFF
)
IF "%garbage_collection%" == "-g" (
  ECHO garbage_collection_flag = OFF
) ELSE (
  ECHO garbage_collection_flag = ON
)
IF %restore% == 0 (
  ECHO test_restore_flag       = OFF
) ELSE (
  ECHO test_restore_flag       = ON
)
IF %rar_compress_ratio% == 0 (
  ECHO backup_compressing      = OFF
) ELSE (
  ECHO backup_compressing      = ON, RAR-ratio=%rar_compress_ratio%
)
REM ==== Define backup file and backup log =========================================
SET datetime=%date:~-2%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%
SET finish_file=%result_dir%\%local_file_or_alias%.%datetime: =0%.%backup_ext%
SET direct_backup=0
IF %rar_compress_ratio% == 0 (
  IF %is_local_result_dir% == 1 SET direct_backup=1
  IF %restore% == 0 SET direct_backup=1
)
IF %direct_backup% == 1 (
  SET backup_file=%finish_file%
) ELSE (
  SET backup_file=%temp_backup_dir%\%local_file_or_alias%.%backup_ext%
  IF NOT EXIST "%temp_backup_dir%" (
    MD "%temp_backup_dir%"
    IF NOT EXIST "%temp_backup_dir%" (
      SET fail=Cannot create backup directory!
      GOTO finish
    )
  )
)
ECHO backup_file             = %backup_file%
SET backup_log=%result_dir%\%local_file_or_alias%.backup.log
ECHO backup_log              = %backup_log%
REM ==== Define restore file and restore log =======================================
SET restore_file=%temp_restore_dir%\%local_file_or_alias%.testrest
IF %restore% GTR 0 (
  IF NOT EXIST "%temp_restore_dir%" (
    MD "%temp_restore_dir%"
    IF NOT EXIST "%temp_restore_dir%" (
      ECHO temp_restore_dir             = %temp_restore_dir%
      SET fail=Cannot create restore directory!
      GOTO finish
    )
  )
  ECHO restore_file            = %restore_file%
  SET restore_log=%result_dir%\%local_file_or_alias%.restore.log
)
IF "%restore_log%" GTR "" (
  ECHO restore_log             = %restore_log%
) else (
  SET restore_log=just_a_stub
)
REM ==== Define compresed file =====================================================
SET compressed_file=%finish_file%.rar
IF %rar_compress_ratio% GTR 0 (
  ECHO compressed_file         = %compressed_file%
  SET finish_file=%compressed_file%
)
REM ==== Delete not actual files (over defined count) ==============================
SET /a over=%backup_count%-1
IF %backup_count% == 1 (
  ECHO deleting_old_files      = %result_dir%\%local_file_or_alias%.*.%backup_ext%*
  DEL "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /q
) ELSE IF %backup_count% GTR 1 (
  FOR /f "SKIP=%over%" %%f IN ('DIR "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /a:-D /b /o:-N 2^>NUL') DO (
    IF EXIST "%result_dir%\%%f" (
      ECHO deleting_old_file       = %result_dir%\%%f
      DEL "%result_dir%\%%f" /q
    )
  )
)
REM ==== Perform backup ============================================================
IF EXIST "%backup_log%" DEL "%backup_log%" /q 
ECHO backup_start            = %date% %time:~0,8%
SET is_local_backup=0
IF %is_local_db% == 1 (
  IF %is_local_result_dir% == 1 SET is_local_backup=1
  IF %direct_backup% == 0 SET is_local_backup=1
  SET ISC_PASSWORD=%ISC_PASSWORD_LOCAL%
) ELSE (
  SET ISC_PASSWORD=%ISC_PASSWORD_REMOTE%
)
IF "%rar_password%" == "" SET rar_password=%ISC_PASSWORD%
IF %is_local_backup% == 1 (
  ECHO %gbak% -b %garbage_collection% -se %service_mgr_host%:service_mgr %local% "%backup_file%" -v -y "%backup_log%"
  %gbak% -b %garbage_collection% -se %service_mgr_host%:service_mgr %local% "%backup_file%" -v >"%backup_log%" 2>&1
) ELSE (
  ECHO %gbak% -b %garbage_collection% "%full_db_specification%" "%backup_file%" -v -y "%backup_log%"
  %gbak% -b %garbage_collection% "%full_db_specification%" "%backup_file%" -v >"%backup_log%" 2>&1
)
IF %ERRORLEVEL% GTR 0 (
  IF EXIST "%backup_log%" (
    SET fail=Backup fail! See %backup_log% for details.
  ) ELSE (
    SET fail=Backup fail!
  )
  GOTO finish
)
IF NOT EXIST "%backup_log%" (
  SET fail=Backup fail!
  GOTO finish
)
REM ==== Perform test restore ======================================================
IF %restore% GTR 0 (
  IF EXIST "%restore_log%" DEL "%restore_log%" /q
  IF "%ISC_PASSWORD_LOCAL%" GTR "" (
    SET ISC_PASSWORD=%ISC_PASSWORD_LOCAL%
  ) ELSE (
    SET ISC_PASSWORD=%ISC_PASSWORD_REMOTE%
  )
  ECHO restore_start           = %date% %time:~0,8%
  ECHO %gbak% -rep -se %service_mgr_host%:service_mgr "%backup_file%" "%restore_file%" -v -y "%restore_log%"
  %gbak% -rep -se %service_mgr_host%:service_mgr "%backup_file%" "%restore_file%" -v >"%restore_log%" 2>&1
  IF %ERRORLEVEL% GTR 0 (
    IF EXIST "%restore_log%" (
      SET fail=Test restore fail! See %restore_log% for details.
    ) ELSE (
      SET fail=Test restore fail!
    )
    GOTO finish
  )
  IF NOT EXIST "%restore_log%" (
    SET fail=Test restore fail!
    GOTO finish
  )
)
REM ==== Perform RAR-compression or copy backup into destination dir ===============
IF "%rar_password%" GTR "" SET rar_password=-p%rar_password%
SET rar_options=%rar_options% -m%rar_compress_ratio%
SET backup_files="%backup_file%" %backup_files%
IF %include_logs_to_archive% == 1 (
  IF %restore% GTR 0 (
    SET backup_files=%backup_files% "%backup_log%" "%restore_log%"
  ) ELSE (
    SET backup_files=%backup_files% "%backup_log%"
  )
)
IF %rar_compress_ratio% GTR 0 (
  ECHO compressing_start       = %date% %time:~0,8%
  ECHO %rar% %rar_options% "%compressed_file%" %backup_files%
  %rar% %rar_options% %rar_password% "%compressed_file%" %backup_files%
  IF %ERRORLEVEL% GTR 0 (
    SET fail=Compression fail!
    GOTO finish
  )
) ELSE IF %direct_backup% == 0 (
    ECHO copying_start           = %date% %time:~0,8%
    ECHO COPY "%backup_file%" "%finish_file%"
    COPY "%backup_file%" "%finish_file%"
  )
REM ==== Delete not actual files (over defined space) ==============================
IF %backup_space_limit% GTR 0 (
  SETLOCAL EnableDelayedExpansion
  IF %ERRORLEVEL% GTR 0 (
    ECHO You must enable var delayed expansion by CMD.EXE /V:ON or at registry key 
    ECHO Software\Microsoft\Command Processor\DelayedExpansion: HKLM or HKCU
    GOTO finish
  ) 
  FOR /f %%f IN ('DIR "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /a:-D /b /o:-N') DO (
    FOR %%i in ("%result_dir%\%%f") DO (
      SET size=%%~zi
      IF %file_size_shift% == 3 SET size=!size:~0,-3!
      IF %file_size_shift% == 6 SET size=!size:~0,-6!
      IF "!size!" == "" SET size=0
      IF "!total_space!" == "" (
        SET /a total_space=!size!
      ) ELSE (
        IF !total_space! LEQ %backup_space_limit% SET /a total_space+=!size!
        IF !total_space! GTR %backup_space_limit% (
          ECHO deleting_overquota_file = %result_dir%\%%f
          DEL "%result_dir%\%%f" /q
        )
      )
    )
  )
)
REM ==== Report when fail or exit ==================================================
:finish
IF "%fail%" == "" (
  ECHO Finish                  = %date% %time:~0,8%
  GOTO exit
)
ECHO #=============================================================================#
ECHO # %fail%
ECHO #=============================================================================#
SET fail=%fail%,  DB: %full_db_specification%,  Dest: %result_dir%
IF "%net_send_receiver%" GTR "" (
  ECHO NET SEND %net_send_receiver% "%fail%"
  NET SEND %net_send_receiver% "%fail%"
)
IF "%blat%" GTR "" IF "%smtp_server%" GTR "" IF "%mail_sender%" GTR "" IF "%mail_login%" GTR "" IF "%mail_receiver%" GTR "" (
  ECHO %blat% -to "%mail_receiver%" -subject "%mail_subject%" -body "%fail%" -server %smtp_server% -f "%mail_sender%" -u "%mail_login%" -pw "*******"
  %blat% -to "%mail_receiver%" -subject "%mail_subject%" -body "%fail%" -server %smtp_server% -f "%mail_sender%" -u "%mail_login%" -pw "%mail_password%"
)
SET time_ex=%time: =0%
IF "%error_log%" GTR "" (
  ECHO %date% %time_ex:~0,8%   %fail% >> "%error_log%"
)
EXIT /b 1
:exit

I will also give the log generated by running the script in the simplest form: no additional keys, all default parameters, only the database address and destination directory are transferred
fb_backup.bat localhost: p: \ MSO \ DB \ MS_ORDERS.FDB \\ 192.168.1.1 \ disk_a1 \ sharing> fb_backup.log
#=============================================================================#
#                                                                             #
# Firebird/InterBase database backup, test restore, zip and rotate script     #
# Ver 3.2.7 (11.11.2012)                                                      #
#                                                                             #
# Author: arni (email:arnisoft at rambler dot ru)                             #
#                                                                             #
# Format:                                                                     #
# FB_BACKUP [host[/port]:][path]db_file_or_alias  result_dir                  #
#           [/count:backup_count]  [/space:backup_space_limit]  [/gc]         #
#           [/restore]  [/compress:level]  [/password:SYSDBA_password]        #
#           [other_files_to_compress [...]]                                   #
#                                                                             #
# Input params:                                                               #
# [host[/port]:][path]      : local or network, full-specified path or alias  #
#   db_file_or_alias            to the source database                        #
# result_dir                : result backup collecting directory              #
# /count:backup_count       : backup file number to keep (30 by default)      #
# /space:backup_space_limit : total backup size in bytes (not use by default) #
#                               you can use suffixes K, M or G.               #
# /gc                       : need to collect garbage in DB (OFF by default)  #
# /restore                  : need to do test restore (OFF by default)        #
# /compress:level           : compress ratio for RAR (2 by default):          #
#                               0: not compress, 1: fastest, 2: fast,         #
#                               3: normal, 4: good, 5: best                   #
# /password:SYSDBA_password : optional SYSDBA password for remote server      #
#                               (by default uses one from the source code)    #
# other_files_to_compress   : list of files that must be add to archive       #
#                                                                             #
#=============================================================================#
full_db_specification   = localhost:p:\MSO\DB\MS_ORDERS.FDB
network_specification   = localhost
local_db_specification  = p:\MSO\DB\MS_ORDERS.FDB
local_path              = p:\MSO\DB\
local_db_file_or_alias  = MS_ORDERS.FDB
result_dir              = \\192.168.1.1\disk_a1\обмен
backup_count            = 30
backup_space_limit      = OFF
garbage_collection_flag = OFF
test_restore_flag       = OFF
backup_compressing      = ON, RAR-ratio=2
backup_file             = C:\WINDOWS\TEMP\MS_ORDERS.FDB.fbk
backup_log              = \\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.backup.log
compressed_file         = \\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.121111_1621.fbk.rar
backup_start            = 11.11.2012 16:21:48
"C:\Programs\FB25\bin\gbak.exe" -b -g -se localhost:service_mgr p:\MSO\DB\MS_ORDERS.FDB "C:\WINDOWS\TEMP\MS_ORDERS.FDB.fbk" -v -y "\\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.backup.log"
compressing_start       = 11.11.2012 16:21:51
"C:\Program Files\WinRAR\rar.exe" a -y -ep -idcd -m2 "\\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.121111_1621.fbk.rar" "C:\WINDOWS\TEMP\MS_ORDERS.FDB.fbk"  "\\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.backup.log"
Создание архива \\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.121111_1621.fbk.rar
Добавление C:\WINDOWS\TEMP\MS_ORDERS.FDB.fbk                                6% 12% 18% 24% 30% 36% 42% 48% 54% 60% 66% 72% 78% 84% 86%  OK 
Добавление \\192.168.1.1\disk_a1\обмен\MS_ORDERS.FDB.backup.log            92% 98%100%  OK 
Finish                  = 11.11.2012 16:21:51

Let's analyze the script fragments:
heading
The header contains a reference to authorship, the current version, but the main thing is a description of the call format: which parameters are basic, which are optional, which default values ​​accept optional parameters.
Optional parameters can go in any sequence, and it is advisable to precede them with standard characters of the command line key: a forward slash [/], or a dash [-].
@CLS
@ECHO OFF
ECHO #=============================================================================#
ECHO #                                                                             #
ECHO # Firebird/InterBase database backup, test restore, zip and rotate script     #
ECHO # Ver 3.2.8 (26.01.2013)                                                      #
ECHO #                                                                             #
ECHO # Author: arni (email:arnisoft at rambler dot ru)                             #
ECHO #                                                                             #
ECHO # Format:                                                                     #
ECHO # FB_BACKUP [host[/port]:][path]db_file_or_alias  result_dir                  #
ECHO #           [/count:backup_count]  [/space:backup_space_limit]  [/gc]         #
ECHO #           [/restore]  [/compress:level]  [/password:SYSDBA_password]        #
ECHO #           [other_files_to_compress [...]]                                   #
ECHO #                                                                             #
ECHO # Input params:                                                               #
ECHO # [host[/port]:][path]      : local or network, full-specified path or alias  #
ECHO #   db_file_or_alias            to the source database                        #
ECHO # result_dir                : result backup collecting directory              #
ECHO # /count:backup_count       : backup file number to keep (30 by default)      #
ECHO # /space:backup_space_limit : total backup size in bytes (not use by default) #
ECHO #                               you can use suffixes K, M or G.               #
ECHO # /gc                       : need to collect garbage in DB (OFF by default)  #
ECHO # /restore                  : need to do test restore (OFF by default)        #
ECHO # /compress:level           : compress ratio for RAR (2 by default):          #
ECHO #                               0: not compress, 1: fastest, 2: fast,         #
ECHO #                               3: normal, 4: good, 5: best                   #
ECHO # /password:SYSDBA_password : optional SYSDBA password for remote server      #
ECHO #                               (by default uses one from the source code)    #
ECHO # other_files_to_compress   : list of files that must be add to archive       #
ECHO #                                                                             #
ECHO #=============================================================================#

determination of default parameters
here we see 5 blocks responsible for the sections:
  • server settings;
  • b / r parameters;
  • archiver settings;
  • mailer options;
  • other parameters;
REM ==== Server ====================================================================
SET gbak="C:\Programs\FB25\bin\gbak.exe"
SET ISC_USER=SYSDBA
SET ISC_PASSWORD_LOCAL=masterkey
SET ISC_PASSWORD_REMOTE=
REM ==== Backup/restore preferences ================================================
SET temp_backup_dir=%TEMP%
SET temp_restore_dir=%TEMP%
SET backup_count=30
SET backup_space_limit=0
SET backup_ext=fbk
SET garbage_collection=-g
SET restore=0
REM ==== RAR =======================================================================
SET rar="C:\Program Files\WinRAR\rar.exe"
SET rar_options=a -y -ep -idcd
SET rar_password=
SET rar_compress_ratio=2
REM ==== Mailer (see "Blat" at http://sourceforge.net/projects/blat) ===============
SET blat="C:\Programs\Blat307\blat.exe"
SET smtp_server=smtp.mailserver.ru
SET mail_sender=foo@mailserver.ru
SET mail_login=foo
SET mail_password=1234
SET mail_receiver=
SET mail_subject=Fail while database b/r
REM ==== Other preferences =========================================================
SET include_logs_to_archive=1
SET net_send_receiver=
SET error_log=
Where:
  • gbak - path to the utility utility from the server bundle;
  • ISC_USER - the user on whose behalf b / r will occur;
  • ISC_PASSWORD_LOCAL - password of the selected user on the local machine;
  • ISC_PASSWORD_REMOTE - password of the selected user on the remote machine (it is better to pass the parameter / password: xxxxxxxx);
  • temp_backup_dir - a temporary directory where backup will be performed before it is archived in the destination directory; it is better to choose a physical disk that does not coincide with where the original database lies;
  • temp_restore_dir - the directory where the test restor will be made; it is better to choose a physical disk that does not match where the original database is located, and does not match where the backup file is;
  • backup_ext - default file extension with backup;
  • garbage_collection - the gbak.exe key, which is responsible for the default behavior regarding garbage collection during backup (it is better to control the / gc parameter);
  • restore - flag of default behavior regarding the need to perform a test recovery (it is better to control the / restore option);
  • rar_options - default keys of the RAR archiver: "a" - compress; "-y" - do not ask questions; "-ep" - exclude paths; "-idcd" - do not litter output with copyright and a readiness mark;
  • rar_password - password for the archive (if it is not set, the archiver uses the password of the Firebird user);
  • rar_compress_ratio - default compression ratio (it is better to control the / compress: level parameter)
  • blat - path to the mailer blat.exe;
  • smtp_server - SMTP server through which mailing will be performed;
  • mail_sender - mail address of the sender;
  • mail_login - sender login on the server;
  • mail_password - sender password on the server;
  • mail_receiver - mailing address of the mailing recipient;
  • mail_subject - the header of the mailing list;
  • include_logs_to_archive - flag of the need to put b / r logs inside the archive;
  • net_send_receiver - the network name of the host to which NET SEND transport failure alerts will be sent;
  • error_log - log file of the failed failures;

source database definition
Here we see:
  • Reading the specification of the source database from the first parameter passed;
  • Separation of the specification into the host name, port, local path and database file name or its alias;
  • Printing parts of the specification on the console;
  • Finding out whether this database is local or remote (it will be important later, when choosing between classic backup or backup through services);
REM ==== Define database location ==================================================
SET full_db_specification=%~1
ECHO full_db_specification   = %full_db_specification%
REM Devide DB spec to network and local parts
FOR /f "DELIMS=: TOKENS=1*" %%i IN ("%full_db_specification%") DO (
  SET network=%%i
  SET local=%%j
)
REM Test if spec. is alias with no network part
IF "%local%" == "" (
  SET network=
  SET local=%full_db_specification%
)
REM Test if spec. is full specified file with no network part
FOR /f "DELIMS=\ TOKENS=*" %%i IN ("%local%") DO IF "%local%" == "\%%i" (
  SET network=
  SET local=%full_db_specification%
)
ECHO network_specification   = %network%
IF "%network%" GTR "" (
  REM Extract port from network spec (if exists)
  FOR /f "DELIMS=/ TOKENS=1*" %%i IN ("%network%") DO (
    SET host=%%i
    SET port=%%j
    IF "%%j" GTR "" (
      ECHO network_host            = %%i
      ECHO network_port            = %%j
    )
  )
)
REM Test if DB is local or remote
SET service_mgr_host=localhost
SET is_local_db=1
IF "%network%" GTR "" IF "%host%" NEQ "127.0.0.1" IF /i "%host%" NEQ "localhost" SET is_local_db=0
IF %is_local_db% == 1 IF "%network%" GTR "" SET service_mgr_host=%network%
ECHO local_db_specification  = %local%
IF "%local%" == "" (
  SET fail=Param #1 {DB specification} missing!
  GOTO finish
)
REM Extract file (or alias) from local part of spec.
FOR /f %%i IN ("%local%") DO (
  SET local_path=%%~dpi
  SET local_file_or_alias=%%~nxi
)
IF "%local%" NEQ "%local_file_or_alias%" (
  ECHO local_path              = %local_path%
  REM Check DB file exists for local, not aliased specification
  IF %is_local_db% == 1 IF NOT EXIST "%local%" (
    SET fail=Local DB file %local% not found!
    GOTO finish
  )
)
ECHO local_db_file_or_alias  = %local_file_or_alias%

definition of the resulting directory (where the backups are added)
Here we see:
  • Reading the resulting directory from the second parameter passed to the script;
  • If the resulting directory is missing, it is created;
  • Determining the location of the directory: local or remote (network) - will be needed below to select the optimal backup strategy;
REM ==== Define result directory ===================================================
SET result_dir=%~2
ECHO result_dir              = %result_dir%
IF "%result_dir%" == "" (
  SET fail=Param #2 {backup collecting directory} missing!
  GOTO finish
)
REM Cut the result dir if it is in path-style (ends with separator)
IF "%result_dir:~-1%" == "\" SET result_dir=%result_dir:~0,-1%
REM Try to create the result directory if it is not exists yet
IF NOT EXIST "%result_dir%" (
  MD "%result_dir%"
  IF NOT EXIST "%result_dir%" (
    SET fail=Cannot create backup collecting directory!
    GOTO finish
  )
)
REM Test if it is local or remote directory (elementary, may get wrong answer)
SET is_local_result_dir=1
IF "%result_dir:~0,2%" == "\\" SET is_local_result_dir=0
This block has an error handling code, upon occurrence of which the essence of the failure is written to the variable, after which control is transferred to the end of the script - to the feedback block with the administrator. Similar handlers are found in all subsequent blocks, and further I will not specifically mention them.

reading the rest of the passed parameters
Here we see the blocks:
  • reading of the next parameter;
  • separation of a key into a name and value;
  • key recognition, transition to its processing;
  • setting the number of stored copies;
  • setting the total volume of copies;
  • managing the garbage collection flag in the database;
  • test recovery flag management;
  • definition of compression ratio by archiver;
  • saving the SYSDBA password for the remote server;
  • reading the list of additional files for archiving;
REM ==== Use other command line options ============================================
:loop_options
SHIFT
SET next_param=%~2
IF "%next_param%" == "" GOTO print_options
SET prefix=%next_param:~0,1%
IF "%prefix%" == "/" SET next_param=%next_param:~1%
IF "%prefix%" == "-" SET next_param=%next_param:~1%
IF "%next_param%" GTR "" (
  FOR /f "DELIMS=: TOKENS=1*" %%i IN ("%next_param%") DO (
    SET value=%%j
    IF /i "%%i" == "count" IF "%%j" GTR "" GOTO count
    IF /i "%%i" == "space" IF "%%j" GTR "" GOTO space
    IF /i "%%i" == "gc" GOTO gc
    IF /i "%%i" == "restore" GOTO restore
    IF /i "%%i" == "compress" IF "%%j" GTR "" GOTO compress
    IF /i "%%i" == "password" IF "%%j" GTR "" GOTO password
    IF EXIST "%next_param%" GOTO add_file_to_compress
    IF "%prefix%" GTR "/" GOTO add_file_to_compress
    ECHO unknown param found: %next_param%
    GOTO loop_options
  )
) ELSE (
  ECHO empty param found!
  GOTO loop_options
)
REM ==== Define file count in the result dir =======================================
:count
SET /a backup_count=0+%value%
GOTO loop_options
REM ==== Define allowed backup space limit =========================================
:space
SET suffix=%value:~-1%
IF "%suffix%" GTR "9" (
  SET value=%value:~0,-1%
  IF /i "%suffix%" == "K" (
    SET file_size_shift=0
    SET /a value*=1000
  )
  IF /i "%suffix%" == "M" (
    SET file_size_shift=3
    SET /a value*=1000
  )
  IF /i "%suffix%" == "G" (
    SET file_size_shift=6
    SET /a value*=1000
  )
)
SET /a backup_space_limit=0+%value%
GOTO loop_options
REM ==== Define need of garbage collection =========================================
:gc
SET garbage_collection=
IF "%value%" == "0" SET garbage_collection=-g
IF /i "%value%" == "N" SET garbage_collection=-g
IF /i "%value%" == "NO" SET garbage_collection=-g
IF /i "%value%" == "OFF" SET garbage_collection=-g
GOTO loop_options
REM ==== Define need of test restore ===============================================
:restore
SET restore=1
IF "%value%" == "0" SET restore=0
IF /i "%value%" == "N" SET restore=0
IF /i "%value%" == "NO" SET restore=0
IF /i "%value%" == "OFF" SET restore=0
GOTO loop_options
REM ==== Define need of backup compression and compress ratio ======================
:compress
IF "%value%" GEQ "0" IF "%value%" LEQ "5" SET rar_compress_ratio=%value%
GOTO loop_options
REM ==== Define SYSDBA password (in addition or for replace source code given) =====
:password
if "%ISC_PASSWORD_LOCAL%" GTR "" if "%ISC_PASSWORD_REMOTE%" GTR "" (
  SET ISC_PASSWORD_LOCAL=%value%
  SET ISC_PASSWORD_REMOTE=%value%
)
if "%ISC_PASSWORD_LOCAL%" == "" SET ISC_PASSWORD_LOCAL=%value%
if "%ISC_PASSWORD_REMOTE%" == "" SET ISC_PASSWORD_REMOTE=%value%
GOTO loop_options
REM ==== Define file list to compress (in addition to backup and maybe logs) =======
:add_file_to_compress
IF "%backup_files%" == "" (
  SET backup_files="%next_param%"
) ELSE (
  SET backup_files=%backup_files% "%next_param%"
)
GOTO loop_options
Note:
  • Boolean flags for a restaurant or garbage collection take the following values: False = 0, N, NO, OFF; True = 1, Y, YES, ON.
  • The total amount of data stored is transferred in bytes. But since Since the numbers in the command processor are limited to a 32-bit integer, values ​​over 1 GB are best transmitted by specifying the suffix kilobyte (K), megabyte (M) or gigabyte (G). For example: / space: 1200K, / space: 280M, / space: 12G. In this block, you can also observe code that truncates the scale of storage units, allowing you to subsequently operate with volumes in excess of 32-bit arithmetic of the command processor.
  • The transmitted password is interpreted as follows: if the source defines the values ​​of the variable ISC_PASSWORD_LOCAL (local) and the variable ISC_PASSWORD_REMOTE (remote), then the transferred value overrides both of these variables. In other cases (if at least one variable is not defined), the transferred value is written only to the empty variable, and the filled one retains its predetermined value.
  • Additional files for compression (or file masks) are transferred in a natural way, without the key symbol [/].

print the final parameters for running the script
Here we see:
  • output of the number of stored copies;
  • output of the total space for storing copies;
  • output flag garbage collection;
  • flag output test recovery;
  • the conclusion of the sign of archiving and the compression ratio used;
REM ==== Print predefined or recognized in command line options ====================
:print_options
IF %backup_count% GTR 0 (
  ECHO backup_count            = %backup_count%
) ELSE (
  ECHO backup_count            = OFF
)
IF "%file_size_shift%" == "" SET file_size_shift=0
IF %backup_space_limit% GTR 0 (
  IF %file_size_shift% == 6 (
    ECHO backup_space_limit      = %backup_space_limit% Mb
  ) ELSE IF %file_size_shift% == 3 (
    ECHO backup_space_limit      = %backup_space_limit% Kb
  ) ELSE ECHO backup_space_limit      = %backup_space_limit% bytes
) ELSE (
  ECHO backup_space_limit      = OFF
)
IF "%garbage_collection%" == "-g" (
  ECHO garbage_collection_flag = OFF
) ELSE (
  ECHO garbage_collection_flag = ON
)
IF %restore% == 0 (
  ECHO test_restore_flag       = OFF
) ELSE (
  ECHO test_restore_flag       = ON
)
IF %rar_compress_ratio% == 0 (
  ECHO backup_compressing      = OFF
) ELSE (
  ECHO backup_compressing      = ON, RAR-ratio=%rar_compress_ratio%
)

work file definition
Here we see:
  • removing the timestamp to use in the backup file name;
  • determining whether we will backup directly to the destination directory, or first to temporary storage for subsequent recovery, compression, etc .;
  • definition of the full backup file specification;
  • definition of the full specification of backup log;
  • printing calculated parameters;
  • determination and printing of the specifications of the restaurant file and restaurant log, if the restaurant flag is raised;
  • definition and printing of the archive file, if compression is requested;
REM ==== Define backup file and backup log =========================================
SET datetime=%date:~-2%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%
SET finish_file=%result_dir%\%local_file_or_alias%.%datetime: =0%.%backup_ext%
SET direct_backup=0
IF %rar_compress_ratio% == 0 (
  IF %is_local_result_dir% == 1 SET direct_backup=1
  IF %restore% == 0 SET direct_backup=1
)
IF %direct_backup% == 1 (
  SET backup_file=%finish_file%
) ELSE (
  SET backup_file=%temp_backup_dir%\%local_file_or_alias%.%backup_ext%
  IF NOT EXIST "%temp_backup_dir%" (
    MD "%temp_backup_dir%"
    IF NOT EXIST "%temp_backup_dir%" (
      SET fail=Cannot create backup directory!
      GOTO finish
    )
  )
)
ECHO backup_file             = %backup_file%
SET backup_log=%result_dir%\%local_file_or_alias%.backup.log
ECHO backup_log              = %backup_log%
REM ==== Define restore file and restore log =======================================
SET restore_file=%temp_restore_dir%\%local_file_or_alias%.testrest
IF %restore% GTR 0 (
  IF NOT EXIST "%temp_restore_dir%" (
    MD "%temp_restore_dir%"
    IF NOT EXIST "%temp_restore_dir%" (
      ECHO temp_restore_dir             = %temp_restore_dir%
      SET fail=Cannot create restore directory!
      GOTO finish
    )
  )
  ECHO restore_file            = %restore_file%
  SET restore_log=%result_dir%\%local_file_or_alias%.restore.log
)
IF "%restore_log%" GTR "" (
  ECHO restore_log             = %restore_log%
) else (
  SET restore_log=just_a_stub
)
REM ==== Define compresed file =====================================================
SET compressed_file=%finish_file%.rar
IF %rar_compress_ratio% GTR 0 (
  ECHO compressed_file         = %compressed_file%
  SET finish_file=%compressed_file%
)

delete excess copies from the destination directory
REM ==== Delete not actual files (over defined count) ==============================
SET /a over=%backup_count%-1
IF %backup_count% == 1 (
  ECHO deleting_old_files      = %result_dir%\%local_file_or_alias%.*.%backup_ext%*
  DEL "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /q
) ELSE IF %backup_count% GTR 1 (
  FOR /f "SKIP=%over%" %%f IN ('DIR "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /a:-D /b /o:-N 2^>NUL') DO (
    IF EXIST "%result_dir%\%%f" (
      ECHO deleting_old_file       = %result_dir%\%%f
      DEL "%result_dir%\%%f" /q
    )
  )
)

backup execution
Here we see:
  • deleting the previous backup log (gbak.exe will return an error if it stumbles upon an old log);
  • depending on whether the database is recognized as local or remote, a password is selected;
  • depending on the location of the database and the location of the directory for backup, we determine the ability to use the fastest strategy - backup through services, or classic backup otherwise;
REM ==== Perform backup ============================================================
IF EXIST "%backup_log%" DEL "%backup_log%" /q 
ECHO backup_start            = %date% %time:~0,8%
SET is_local_backup=0
IF %is_local_db% == 1 (
  IF %is_local_result_dir% == 1 SET is_local_backup=1
  IF %direct_backup% == 0 SET is_local_backup=1
  SET ISC_PASSWORD=%ISC_PASSWORD_LOCAL%
) ELSE (
  SET ISC_PASSWORD=%ISC_PASSWORD_REMOTE%
)
IF "%rar_password%" == "" SET rar_password=%ISC_PASSWORD%
IF %is_local_backup% == 1 (
  ECHO %gbak% -b %garbage_collection% -se %service_mgr_host%:service_mgr %local% "%backup_file%" -v -y "%backup_log%"
  %gbak% -b %garbage_collection% -se %service_mgr_host%:service_mgr %local% "%backup_file%" -v >"%backup_log%" 2>&1
) ELSE (
  ECHO %gbak% -b %garbage_collection% "%full_db_specification%" "%backup_file%" -v -y "%backup_log%"
  %gbak% -b %garbage_collection% "%full_db_specification%" "%backup_file%" -v >"%backup_log%" 2>&1
)
IF %ERRORLEVEL% GTR 0 (
  IF EXIST "%backup_log%" (
    SET fail=Backup fail! See %backup_log% for details.
  ) ELSE (
    SET fail=Backup fail!
  )
  GOTO finish
)
IF NOT EXIST "%backup_log%" (
  SET fail=Backup fail!
  GOTO finish
)

performing test recovery
Here we see:
  • deleting the previous restaurant log (gbak.exe will return an error if it stumbles upon an old log);
  • performing recovery (always through services, because both the backup file and the test database are local in this branch of the script);
REM ==== Perform test restore ======================================================
IF %restore% GTR 0 (
  IF EXIST "%restore_log%" DEL "%restore_log%" /q
  IF "%ISC_PASSWORD_LOCAL%" GTR "" (
    SET ISC_PASSWORD=%ISC_PASSWORD_LOCAL%
  ) ELSE (
    SET ISC_PASSWORD=%ISC_PASSWORD_REMOTE%
  )
  ECHO restore_start           = %date% %time:~0,8%
  ECHO %gbak% -rep -se %service_mgr_host%:service_mgr "%backup_file%" "%restore_file%" -v -y "%restore_log%"
  %gbak% -rep -se %service_mgr_host%:service_mgr "%backup_file%" "%restore_file%" -v >"%restore_log%" 2>&1
  IF %ERRORLEVEL% GTR 0 (
    IF EXIST "%restore_log%" (
      SET fail=Test restore fail! See %restore_log% for details.
    ) ELSE (
      SET fail=Test restore fail!
    )
    GOTO finish
  )
  IF NOT EXIST "%restore_log%" (
    SET fail=Test restore fail!
    GOTO finish
  )
)
Note:
If you have InterBase, Yaffil, or Firebird younger than version 2.0, you need to replace the -rep recovery flag with -r

archive compression
Here we see:
  • collection of all rar parameters and keys, including archive password and compression ratio;
  • collection of all files for compression, including backup, logs (if specified by the policy) and other files (if transferred);
  • if compression is specified, perform compression;
  • if compression is disabled, then simply copy the backup file to the destination directory (only if the script did not create it initially there);
REM ==== Perform RAR-compression or copy backup into destination dir ===============
IF "%rar_password%" GTR "" SET rar_password=-p%rar_password%
SET rar_options=%rar_options% -m%rar_compress_ratio%
SET backup_files="%backup_file%" %backup_files%
IF %include_logs_to_archive% == 1 (
  IF %restore% GTR 0 (
    SET backup_files=%backup_files% "%backup_log%" "%restore_log%"
  ) ELSE (
    SET backup_files=%backup_files% "%backup_log%"
  )
)
IF %rar_compress_ratio% GTR 0 (
  ECHO compressing_start       = %date% %time:~0,8%
  ECHO %rar% %rar_options% "%compressed_file%" %backup_files%
  %rar% %rar_options% %rar_password% "%compressed_file%" %backup_files%
  IF %ERRORLEVEL% GTR 0 (
    SET fail=Compression fail!
    GOTO finish
  )
) ELSE IF %direct_backup% == 0 (
    ECHO copying_start           = %date% %time:~0,8%
    ECHO COPY "%backup_file%" "%finish_file%"
    COPY "%backup_file%" "%finish_file%"
  )
Note:
In a number of discussions, there are examples when, to speed up backup compression, gbak.exe and rar.exe are launched in conjunction when the output of the first is fed directly to the input of the second. Unfortunately, this method has a significant drawback that does not allow us to recommend it for use: if gbak.exe fails, then in the end a zero (successful) return code is returned anyway, because rar.exe honestly and unmistakably saved those crumbs that gbak.exe managed to transfer to him before it failed. Those. You will never know that your backup process will fail until you try to restore a broken backup, or accidentally do not pay attention to the penny size of the resulting archive.

delete files in excess of the specified disk quota
Here we see:
  • An attempt to activate the delayed expansion of variables in the command processor (in case of failure, inform the user and exit the block), which is necessary to calculate the total file size;
  • Searching for accumulated backups from the freshest to the most ancient, and counting their total volume. From the moment the limit is exceeded, delete all remaining files. This block uses a technique that allows you to overcome the limitation of 32-bit arithmetic when calculating tens and hundreds of gigabytes of disk space (trite we cut off the low-order bits) if you have to deal with such large databases.
REM ==== Delete not actual files (over defined space) ==============================
IF %backup_space_limit% GTR 0 (
  SETLOCAL EnableDelayedExpansion
  IF %ERRORLEVEL% GTR 0 (
    ECHO You must enable var delayed expansion by CMD.EXE /V:ON or at registry key 
    ECHO Software\Microsoft\Command Processor\DelayedExpansion: HKLM or HKCU
    GOTO finish
  ) 
  FOR /f %%f IN ('DIR "%result_dir%\%local_file_or_alias%.*.%backup_ext%*" /a:-D /b /o:-N') DO (
    FOR %%i in ("%result_dir%\%%f") DO (
      SET size=%%~zi
      IF %file_size_shift% == 3 SET size=!size:~0,-3!
      IF %file_size_shift% == 6 SET size=!size:~0,-6!
      IF "!size!" == "" SET size=0
      IF "!total_space!" == "" (
        SET /a total_space=!size!
      ) ELSE (
        IF !total_space! LEQ %backup_space_limit% SET /a total_space+=!size!
        IF !total_space! GTR %backup_space_limit% (
          ECHO deleting_overquota_file = %result_dir%\%%f
          DEL "%result_dir%\%%f" /q
        )
      )
    )
  )
)

final part
Here we see:
  • If an error is detected at any of the above steps, a short report is compiled: the essence of the error, the source database, the destination directory;
  • If the destination for the NET SEND transport is defined, then an error report is sent to him;
  • If the parameters of mailing are defined (mailer, server, sender, recipient), then the error report is sent by mail;
  • If a failure log file is defined, an error record is added to it;
  • The last step in the error script is to return control with a positive termination code, which will signal the caller (usually this is the task scheduler) that the script has failed;
REM ==== Report when fail or exit ==================================================
:finish
IF "%fail%" == "" (
  ECHO Finish                  = %date% %time:~0,8%
  GOTO exit
)
ECHO #=============================================================================#
ECHO # %fail%
ECHO #=============================================================================#
SET fail=%fail%,  DB: %full_db_specification%,  Dest: %result_dir%
IF "%net_send_receiver%" GTR "" (
  ECHO NET SEND %net_send_receiver% "%fail%"
  NET SEND %net_send_receiver% "%fail%"
)
IF "%blat%" GTR "" IF "%smtp_server%" GTR "" IF "%mail_sender%" GTR "" IF "%mail_login%" GTR "" IF "%mail_receiver%" GTR "" (
  ECHO %blat% -to "%mail_receiver%" -subject "%mail_subject%" -body "%fail%" -server %smtp_server% -f "%mail_sender%" -u "%mail_login%" -pw "*******"
  %blat% -to "%mail_receiver%" -subject "%mail_subject%" -body "%fail%" -server %smtp_server% -f "%mail_sender%" -u "%mail_login%" -pw "%mail_password%"
)
SET time_ex=%time: =0%
IF "%error_log%" GTR "" (
  ECHO %date% %time_ex:~0,8%   %fail% >> "%error_log%"
)
EXIT /b 1
:exit

Here are a couple of scripts that describe the use of the script in our daily practice. I will call them “night backup” and “day backup”.
At night, no one works with the base, or almost no one. Server resources are idle, and therefore it is appropriate to make a full b / r cycle, namely:
- backup with garbage collection;
- test recovery;
- compression of the backup and the current version of the application program, and compression in the best way;
Here is an example of launching "night backup":
fb_backup.bat localhost:Orma4 d:\Bak /count:99 /space:500G /compress:5 /restore /gc d:\Orma.exe >C:\Orma4.log 2>&1

On the contrary, the launch of the “day backup” should work out as quickly as possible, as little as possible straining the server, and therefore users connected to the database. Therefore, we exclude garbage collection and test recovery, compress it with the fastest method, and only the backup file:
fb_backup.bat localhost:Orma4 d:\Bak /count:99 /space:500G /compress:1 >C:\Orma4.log 2>&1

There is another option for recording backup to a remote server (for security reasons, according to the principle of "do not store all eggs in one basket"). It combines an intermediate set of keys: you need to reap most tightly, because the archiver usually manages to grind faster than the network can handle file transfer, and exclude other operations (garbage collection, test recovery, saving the application program, etc.), as far as possible. they have already been done "night backup" to the local server:
fb_backup.bat localhost:Orma4 \\ifs\E$\backup\FirebirdDB /count:99 /space:300G /compress:5 >C:\Orma4.log 2>&1

For those who are not in the know, the ending of the command means redirecting the script output (stdout) to a file, and sending the error output (stderr) to the same file.
 >C:\Orma4.log 2>&1


A bit about the limitations of the script.
  • All our databases are running server editions of Windows, so the script was not tested for the suitability of taking copies from databases running on Linux. They can probably occur at the stage of parsing the path to the database using a forward slash as a separator for UNIX systems. But if you use addressing via alias, then there seems to be no problem. I will be grateful if someone checks this aspect and unsubscribes in the comments.
  • I don’t particularly like that the SYSDBA password is in plain text in the script, or it is also transmitted in plain text to the script through the scheduler. Improving the script in the direction of the optional connection to the database through Windows Trusted Authentication suggests itself , which will allow b / r to be performed on behalf of a user account with domain admin rights without passing the password explicitly.
  • Some people prefer to save a backup copy, not a backup, because the first will need to be restored, and the second is already ready for use, which is important if emergency substitution is necessary. However, in the latter case, the requirements for backup storage volume increase significantly, and this option is also limitedly transferred between different versions of the server (suppose you have FB2.5.1 spinning in production, and FB2.5.2 or snapshot 2.5.3, between which there is incompatibility), because regular migration through b / r will not work here.
  • b / r comes from the name of the regular SYSDBA superuser (or another user that you write in the initializing part of the script). Support for an alternative database owner is not practiced here. In addition, the restaurant process in this script is of a control nature (checking backup recoverability), and not the target one.
  • At the recovery stage, the size of the database page is not declared, and thus the previous value is taken. IMHO, a page change is a rare one-time operation that should be done "sighted", and it does not belong in a script whose main purpose is to periodically call from the scheduler. Well, a reference to the specific role of the restaurant in this script will also be appropriate: there is nothing to be wiser with page size if we only want to check backup recoverability.
  • The script is quite plentifully supplied with comments, but all of them are English-speaking (indicate in the comments where it turned out quite clumsily with English). Cyrillic inserts had to be abandoned for reasons of practicality: the text is usually typed / edited in a notepad with code page 1251, and for Russian comments to be visible at the stage of execution of the command file, they must be written in code page 866. Since FAR or another advanced editor is not always at hand; it is easier to use letters of the Latin alphabet only. Here I greatly envy the Linux console with UTF8.
  • Although the script was developed taking into account the ability to work with paths containing spaces (which must be enclosed in quotation marks by standard), this aspect was not really tested with us, and there is an assumption that, for example, the specification of a remote database with the server name / address and local path, containing spaces may be parsed incorrectly. Work through an alias, or an abbreviated record of the path will allow you to get out of this difficulty.
Tags:

Also popular now: