Automate SQL Server operations by batch file

Many times we need to perform many repetitive actions on our sql server . Many of those are literaaly repetitve or many of them we really do not do every day but whenever it is , it a collection of steps .

Today we will see how easily we can automate our repetitive work by batch file so that we always donot have to open SQL Server Management Studio and conect to our DB etc….

 

You can also have a look how to create a log file from batch file in my previous post

Create a batch file :

1. Open your favourite text edidor and write below lines :

 @echo off
SET SERVER=YOURSERVERNAME
SET DB=YOURDATABSENAME

SET Logfile=runscripts.log
SET InvokeSQL=sqlcmd -S %SERVER% -d %DB%  -E -b -i
echo %DATE% %TIME% %USERNAME% > %Logfile%
echo calling sql-scripts with: %InvokeSQL% %%scriptfile%% >> %Logfile%
echo. >> %Logfile%
REM START trace32.exe %Logfile%
echo.
CALL :RUNProgram 001.sql || GOTO :FAILED
CALL :RUNProgram 002.sql || GOTO :FAILED
echo no errors found – logfile = %Logfile%
GOTO :EOF
:RUNProgram
echo running %1%
echo.>> %Logfile%
echo ===================   running %1%     %     %TIME%  =======================%  %>> %Logfile%
%InvokeSQL% %1% %>> %Logfile% 2>&1
GOTO :EOF
:FAILED
echo errors found – logfile = %Logfile%

2. Save file as batch file “myFirstSQLBatch.cmd”

3. Run it !!!!

Thats it !!!

Advertisements

2 thoughts on “Automate SQL Server operations by batch file

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s