Deploy Multiple SQL scripts / Stored Procedures using single batch file

In this blog, we will see how to execute n-number of SQL script / Stored Procedure to SQL server:

1^st we have to take backup of all the Stored Procedures before deployment:

A)   Take the backup of all required stored procedures using Generate Scripts option in SSMS:

Go to Database à Right Click àTaksà Generate Scripts…

Select: Choose Objects à Expand Stored Procedures/Functions à Select required SPs/Functions from the case they want to deploy.

Set Scripting Options:

Output Type à save Scripts to a specific Location: will provide you script .sql file to the path that you mentioned.

Save to file:

Single file: will give you single .sql file for all marked stored procedures.

Single file per object: will give you separate single .sql file for each stored procedures.

File name: on server provide Z:\casenumber_SPbkp

Click on Next

This will give you information for your task as below:

Click on Fininsh:

Now can check .sql backup of all the SPs files get created on Z:\CaseNo_SPbkp\

B)      Deploy Stored Procedures using Batch file.

1) Copy SPs on local server with make new folder on drive (expl: C:\LV\Newfolder\).

2) Create batch file as following (create only on PROD server):

-- Open notepad and past below script:

@echo off

setlocalenabledelayedexpansion

set /p servername=Enter DB Servername :

set /p dbname=Enter Database Name :

set /p spath=Enter Script Path :

sethr=%time:~0,2%

if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%

set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log

setcmd='dir %spath%\*.sql /b/s'

FOR /f %%G IN (%cmd%) DO (

echo ******PROCESSING %%G FILE******

echo ******PROCESSING %%G FILE****** >> %logfilepath%

SQLCMD -S%servername% -E -d%dbname% -b -i%%G >> %logfilepath%

)

IF !ERRORLEVEL! NEQ 0 GOTO :OnError

GOTO :Success

:OnError

echo ERROR ERRORERROR

echo One\more script(s) failed to execute, terminating bath.

echo Check output.log file for more details

EXIT /b

:Success

echo ALL the scripts deployed successfully!!

EXIT /b

-          Save above script as batch file: "script.bat" (make double quote for file name) on local server. (folder name where we save this batch file, that name must be without space e.g.New Folder is wrong - NewFolder is correct)

3) Open Command Prompt on server then go to batch file path and then provide parameter as required:

(Shift+Right click on folder then select Open Command window here)

After execute above command please find the single .txt file named as “output_” created which stores logs for stored procedure deployed successfully and failure:

    ----------------------------------------------------------------------------------------------------------------------------------

Unsuccessful SPs where we can find the SPs with error messagewith SP name as below SP “AMC_HOSP_AdvDirectivesDetail_SelPr.sql”. Re-deploy failure stored procedure after did require changes.

-          After executed all the stored procedures we can check last modify date by below query:

USEDATABSE_NAME

GO

select*fromsys.proceduresorderbymodify_datedesc