Batching SQL Scripts to Automate Deployments (with .bat/cmd)

With this technique, you don’t have to buy anything or spend time installing fancy stuff. Just write some commands, copy/paste and save as .bat or cmd. [Not discrediting any other implementation, just sharing my experience with a simple method].

When you end up running the batch I recommend doing it on the target server, not your workstation but you can do the latter as well. Just stage the files together on a temp/deployments folder on the remote server for execution.

Also, if you need to install sqlcmd on your workstation or server here’s the free download link from MS to the Microsoft Command Line Utilities 11 for SQL Server.

For example, sometimes I have to execute thousands of T-SQL scripts in a given order and there’s no time or way in hell that I am going to open each one in SSMS and hit F5. lol. So, here’s an example of how I batch a set of SQL Scripts in a required order for a change request/deployment.

Note, I do pre-hand review each file to adhere policies. ie. Use Notepad++ (a free tool every DBA/SysAdmin should have) to scan files (Search…Find in Files…) and subdirectories for keywords such as CREATE, DROP, GRANT, DELETE, TRUNCATE, REVOKE, DENY, etc…

Don’t ever just blindly run things on your server. Oh and similarly can be done with Get-ChildItem Cmdlet, PowerShell scripts (.ps1) or even call a PowerShell script from the batch. More on that to follow. The nice thing about Notepad++ is that you can easily double click the line # to open a found in Search result or share the Search results with your requestor/dev to engage a conversation with any questions you may have.

Again, save the following as 0.Run.bat along with the files being called in the same directory. You can double-click it to run or call it from cmd. If you have any questions lmk.

REM File saved as 0.Run.bat
@ECHO Off
@ECHO ********** %DATE% %TIME% ********** >>RunLog.txt

SET Server=HFSQL16.lab.hiram.com,60305
SET Database=tempdb
cls

@ECHO OFF

@ECHO ON
@ECHO Deploying to %Server% >>RunLog.txt
@ECHO OFF

@ECHO Running 1.DBRestores.sql >>RunLog.txt
sqlcmd /S "%Server%" /d "tempdb" /i "1.DBRestores.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO END

@ECHO Running 2.CreateAndMapLogins.sql >>RunLog.txt
sqlcmd /S "%Server%" /d "master" /i "2.CreateAndMapLogins.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO END

@ECHO Running 3.RunTLogBackup.sql >>RunLog.txt
sqlcmd /S "%Server%" /d "msdb" /i "3.RunTLogBackup.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO END

@ECHO Running 4.GrantSelect.sql >>RunLog.txt
sqlcmd /S "%Server%" /d "master" /i "4.GrantSelect.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO END

@ECHO Running 12.sp_configure.sql >>RunLog.txt
sqlcmd /S "%Server%" /d "master" /i "12.sp_configure.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO END

@ECHO. >>RunLog.txt
@ECHO ************* %DATE% %TIME% ************* >>RunLog.txt
@ECHO OFF
GOTO EXIT

:END
@ECHO Date: %Date% %Time% PublishErrorlevel:%errorlevel% >>RunLog.txt
REM @ECHO Running 99.RollBack.sql >>RunLog.txt
REM sqlcmd /S "%Server%" /d "master" /i "99.RollBack.sql" >>RunLog.txt
IF ERRORLEVEL 1 GOTO EXIT

:EXIT
@ECHO Date: %Date% %Time% PublishErrorlevel:%errorlevel% >>RunLog.txt
@ECHO Completed Successfully!!! >>RunLog.txt
call RunLog.txt

PAUSE

Nice, right? And this shows you some error handling, variable usage and logging techniques in batch.

I hope it saves you time.

Note, the sqlcmd call to 99.RollBack.sql is commented out (REM) because in some cases it’s more important to actually solve an issue in the allotted time window, instead of rolling back the progress made. You can remove the REM if you wish.

Thanks,
Hiram