HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Batch file to backup and update SQL Server DBs

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
codereviewsqlsql-serverbatchstackoverflowbeginnerwindows
dbsfileupdateserversqlbatchandbackup

Problem

This is my first windows batch file. The script basically goes through this basic flow of tasks, in order to update some specific SQL Server DBs to a new DB version:

  • Backup all SQL server DBs that will be upgraded.



  • Upgrade to the latest version and apply any last-minute patches to those DBs.



  • Error handling is done when the process fails at any point, rolling back all DBs to their initial version (before the update).



I would appreciate your feedback on:

  • What parts of the code could be improved in general (fix some bad practices, improve brittle code, etc).



  • How to better organize (comments, separation of sub-sections, etc) the code and make it clearer (for easy maintenance).



  • Any other aspect you think needs to be improved, specially regarding batch coding style and standarization.



Here is the full code:

```
:: MAIN SCRIPT OPTIONS
@echo off
setlocal EnableDelayedExpansion

:: ------------------------------------------------------------------------------------------------
:: PARAMETERS (ADJUST TO SPECIFIC ENVIRONMENT)

:: SQL Server: instance holding the DBs
set serverName=SERVER\SQLEXPRESS
:: SQL Server: user name and user password for performing the upgrade
set sqlUser=%1
set sqlPwd=%2
:: SQL Server: comma separated list of DBs in this server instance that should be upgraded
set sqlDBs=DB1,DB2

:: DB version: current version to be upgraded
set currentVersion=4.3.0
:: DB version: target version to upgrade to
set targetVersion=4.4.0
:: DB version: scripts (if multiple, quote and separate with commas) to run before applying the upgrade (leave blank if none is needed)
set prePatchScripts=
:: DB version: scripts (if multiple, quote and separate with commas) to run after applying the upgrade (leave blank if none is needed)
set postPatchScripts="Script1.sql,Script2.sql"

:: ------------------------------------------------------------------------------------------------
:: CALCULATED VARIABLES (DO NOT MODIFY)

:: Folders: scripts working directory

Solution

Well, in case anyone is interested, here is what I've come up with:

BASICS

I like using the :: type of comments over REM comments, because I think they are easier to read (less clutter).

Also, I use lower case text for everything inside the file (except for section headers, as you will see below). I understand that many people prefer to use upper case text for function names, labels, etc., but I think lower case text is easier to read, so that is what I am adopting for my files.

COMMENTS

Taking this into consideration, I use a header on each file like this:

::
:: SCRIPTNAME.BAT
:: 
:: Author: Author's Name (email@domain.com)
:: vx.y - Month dd, yyyy
::
:: Summary of what the script is supposed to do, special remarks,
:: acknowledgments, and other important general notes. 
::


Then I divide my file in sections, delimited by separators like these:

:: --------------------------------------------------------------
:: FILE PARAMETERS (FOR ADJUSTING SCRIPT TO A NEW ENVIRONMENT)
:: --------------------------------------------------------------


Some sections are optional - more details regarding the sections I use below in the organization part.

Blocks of code inside each section can be commented with something like this, in order to create a clear separation of the different parts of code, and also to provide useful comments for business logic, batch file techniques used to "make something work", etc:

:: 
:: Perform some specific sub-task (block title)
:: 
:: This is a comment to explain what will happen in the next code block.
:: You can document here important aspects of the lines that follow. 
::


Finally, functions are documented in a similar way than blocks of code, but I decided to include parameter and return value definitions (inspired to some degree in Javadoc comments). Here is an example (take notice of the tag at the top of the comment): 

::
:function_name
::
:: Function used to perform some task on some elements (function brief description). 
::
:: (Optional) comments can be added if you need to provide further details about the function.
:: These comments can span multiple lines. 
::
:: %1 this is a description of what the first parameter is.
:: %2 this is a description of what the second parameter is.
::
:: @Return errorlevel 0 if the action is valid, errorlevel 2 if error A
:: happens, and return errorlevel 1 otherwise. 
::


FILE ORGANIZATION

The basic organization of my files was thought in order to put everything that makes sense together, in the most 'findable' way, trying also to follow the basic flow of my scripts. In this regard, the main sections that my scripts have now are:

  • Main Script Options: here I set @echo off, setlocal EnableDelayedExpansion, etc. Every option that needs to be turned on for the whole script.



  • File Parameters: these are at the top of the file for easy modification, as they are the parameters that are 'hard coded' in the script, but can be changed to adjust the script to a new environment. Examples are set serverName=MYSERVER\MYINSTANCE or set workingDir=D:\Path\To\Files. These aren't supposed to change during each execution of the script.



  • Prompt Parameters: this section is for defining parameters that are set by prompting the user for specific input. For example, asking the user for a user name and/or password, or any other parameter that you don't want to store directly in the script (because it's sensible, or it changes every time the script is executed). Examples can be set /p user=Enter user name: or set /p sqlDBs=Comma separated list of DBs:.



  • Calculated Variables: this section holds the definition of variables that will be used in several places during the script execution, so it's better (imho) to have them centralized. These variables' values are derived from the values of the (file or prompt) parameters, usually using simple calculations/transformations. Examples would be: set scriptsDir=%workingDir%Scripts\ or set userFolder=%workingDir%\users\%user%. Although these examples only show folder "calculations", it can be extended to calculated patterns, dates, file names, etc. i.e. everything that's derived from the parameters, but not set directly. You get the idea.



  • Script Parameter Validation & Cleaning: all parameter validation happens here, before "doing any damage". If any validation fails, the whole script halts and a (hopefully helpful) message error is presented to the user. Any parameter cleaning is also done here (for instance, removing strange/unexpected characters).



  • Main Script Flow: these is where the main logic of the script is defined. Sub-sections of the script's flow can be commented using the convention shown above. You should be able to read the main logic of your script by reading this section.



  • Script Functions: this is where "reusable" code is located, properly documented with the style explained above. Code you need to use more than once, or code t

Code Snippets

::
:: SCRIPTNAME.BAT
:: 
:: Author: Author's Name (email@domain.com)
:: vx.y - Month dd, yyyy
::
:: Summary of what the script is supposed to do, special remarks,
:: acknowledgments, and other important general notes. 
::
:: --------------------------------------------------------------
:: FILE PARAMETERS (FOR ADJUSTING SCRIPT TO A NEW ENVIRONMENT)
:: --------------------------------------------------------------
:: 
:: Perform some specific sub-task (block title)
:: 
:: This is a comment to explain what will happen in the next code block.
:: You can document here important aspects of the lines that follow. 
::
::
:function_name
::
:: Function used to perform some task on some elements (function brief description). 
::
:: (Optional) comments can be added if you need to provide further details about the function.
:: These comments can span multiple lines. 
::
:: %1 this is a description of what the first parameter is.
:: %2 this is a description of what the second parameter is.
::
:: @Return errorlevel 0 if the action is valid, errorlevel 2 if error A
:: happens, and return errorlevel 1 otherwise. 
::
::
:: DBRESTORE.BAT
:: 
:: Author: Carlos Sierra
:: v1.0 - August 24, 2016
::
:: This batch file goes through all the backup files in the Backups 
:: subfolder, and restores them. 
::

:: --------------------------------------------------------------
:: MAIN SCRIPT OPTIONS
:: --------------------------------------------------------------

@echo off
setlocal EnableDelayedExpansion

:: --------------------------------------------------------------
:: FILE PARAMETERS (FOR USER TO ADJUST WHEN SETTING UP A NEW ENVIRONMENT)
:: --------------------------------------------------------------

:: SQL Server: instance holding the DBs
set serverName=MYSERVER\SQLEXPRESS

:: --------------------------------------------------------------
:: PROMPT PARAMETERS (FOR USER TO ADJUST DURING SCRIPT EXECUTION)
:: --------------------------------------------------------------

:: SQL Server: user name and user password for performing the update
set /p sqlUser=Enter the SQL server user for executing the update: 
set /p sqlPwd=Enter the SQL server password for user %sqlUser%:

:: --------------------------------------------------------------
:: CALCULATED VARIABLES (DO NOT MODIFY)
:: --------------------------------------------------------------

:: Folders: scripts working directory
set workingDir=%~dp0
set backupsDir=%workingDir%Backups\

:: --------------------------------------------------------------
:: SCRIPT PARAMETER VALIDATION & CLEANING
:: --------------------------------------------------------------

:: No validations required in this script. 

:: --------------------------------------------------------------
:: MAIN SCRIPT FLOW
:: --------------------------------------------------------------

:: 
:: Restore every db stored in the Backups folder
::
call :echo_header "Starting DB restore process"
for /f "delims=" %%a in ('dir %backupsDir% /b') do (
    set file=%%a
    set ext=!file:~-3!
    if !ext!==bak (
        set name=!file:~0,-4!
        echo Restoring database !name! from %backupsDir%!file!
        sqlcmd -b -S !serverName! -U !sqlUser! -P !sqlPwd! -Q "alter database [!name!] set single_user with rollback immediate"
        sqlcmd -b -S !serverName! -U !sqlUser! -P !sqlPwd! -Q "restore database [!name!] from Disk='%backupsDir%!file!' with replace"
        if errorlevel 1 goto :show_restore_error
        echo.   
    )
) 

::
:: End the process successfully
::
call :echo_header "SUCCESS: backups restored succesfully for DBs %sqlDBs%!"
goto :eof

:: --------------------------------------------------------------
:: SCRIPT FUNCTIONS
:: --------------------------------------------------------------

::
:show_restore_error
::
:: Function used to display a prominent error message to the user. 
::
:: @Return errorlevel 0 always. 
::
call :echo_header "ERROR: restoration of DBs failed. See console for details."
exit /b 0

::
:echo_header
::
:: Function used to display section titles inside the batch process. 
::
:: %1 the text to be displayed as a header
::
::

Context

StackExchange Code Review Q#139188, answer score: 5

Revisions (0)

No revisions yet.