Batch file to backup and update SQL Server DBs

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 protected])
:: 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 that you can use in several batch files is included here. This helps hiding the low-level logic of some operations from the 'Main Script Flow'.

With all that said, this is how one of my scripts look after all these conventions are applied (I used a simpler script than the one shown above to focus on the conventions and not the script itself):

::
:: 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
::
:: @Return errorlevel 0 always. 
::
echo.
echo //////////////////////////////////////////////////////////////////////////////////////////
echo %~1 
echo //////////////////////////////////////////////////////////////////////////////////////////
echo.
exit /b 0

I hope you find it useful.

All ideas and contributions are welcome!