How To: Write A .BAT file To BackUp your SQL Express and/or SQL Standard Databases

For my first how to article I thought it appropriate to come out with a blog I have been searching for, for a very long time. So here it is: First off I want to come out and state one thing, I am not responsible for anything that might occur on your system and or your data being completely secure. Please use an enterprise solution for guaranteeing that your information is secure and backed up. This article is being written for any tech that needs a bit of help for DBA.

First off I will post code bits and then explain them after I post.

First code bit:

@ECHO OFF

@REM Created By: MyTekki

@REM Last Updated: 01-10-10

@REM v1.0.5.0 Final

for /f "tokens=1-3 delims=./-" %%f in ("%date%") do (

set _today=%%f-%%g-%%h

)

Now what this does is basically 2 things. it goes through the default "date" variable object and gathers pieces of information out of them such as the day, month, year. Then assigns (sets) a new variable known as the "_today" variable. So now that we have this bit of information we can start setting some of our other variables.

SET CMD_PATH=%SYSTEMROOT%\system32\cmd.exe

SET sqlServer=%1

SET sqlInstance=%2

SET saUsr=%3

SET saPass=%4

SET dbName=%5

SET tempBackLoc=%6

SET copyBackLoc=%7

SET logLoc=%8

SET deleteLocal=%9

Here we set a couple variables also we set to the cmd.exe (command prompt) file normally located on the system under C:\Windows\System32\cmd.exe we use %sytemroot% instead of %windir% both are built in variables but one is supported in older versions as well as newer. So now that is done we can look at the other variables that are set such as "sqlServer" as you can see they all have a %1, %2, %3, etc.. This is due to the fact that we are going to dynamically take these variables in making this object more useful for further use (multiple databses for example). So in this case if we were doing a scheduled task it would look something like the following:

{PathOfBATFile}\{BATFileName}.bat {sqlServer} {sqlInstance} {sqlUser} {sqlPass} {dbName} {tempBackUpLoc} {copyBackUpLoc} {logLoc} {deleteLocal--BooleanValue Y or N}

MySample:

C:\Temp\SQL_autoBackUp.bat MyTekkiSrv SQLExpress2005 sa test123 Data2010 C:\Temp\ R:\SQLBackUps\ C:\Temp\Log\ Y

!!!!! BIG NOTES !!!!!

Many of you are probably wondering why I don't just copy to the final locaiton. The reason why I do not do this is because of 2 reasons. If for some reason you can not connect to the final location? Secondly if security changes in the final location? Also SQL Server can not back up to a remote location it has to be a local physical drive not a network location.

Another BIG NOTE note:

I do not Check for spaces any where in this script maybe a later version I will add it in. But for now you can not use spaces in anything folder names instance names passwords

Now we do a couple checks:

@REM ---- VARIABLE CHECKS

IF NOT DEFINED dbName GOTO defineDatabase

IF NOT DEFINED tempBackLoc GOTO defineTempBackLoc

IF NOT DEFINED copyBackLoc GOTO defineCopyBackLoc

IF NOT DEFINED logLoc GOTO defineLogLoc

Here we check to see if that we have defined a database name, temp backup location, final location, and a log location

@REM --- END OF VAR CHECKS

@REM Checks to see if the Directory for the temp BackUp Exists

IF EXIST %tempBackLoc% GOTO CHKCOPYLOC

@REM Creates the directory for the temp BackUp

MKDIR %tempBackLoc%

:CHKCOPYLOC

@REM Checks to see if the Directory for the real BackUp Exists

IF EXIST %copyBackLoc% GOTO COPYFILE

@REM Creates the directory for the copied file.

MKDIR %copyBackLoc%

Now we are going to see if some of the variables exist. For example the back up location exists if not we will make the location for them.

:COPYFILE

@REM -- Makes the back up using the sqlcmd exe Found in the C:\Program Files\Microsoft SQL Server\90\Tools\Binn

%CMD_PATH% /c sqlcmd -S %sqlServer%\%sqlInstance% -U %saUsr% -P %saPass% -Q "BACKUP DATABASE [%dbName%] TO DISK = N'%tempBackLoc%%dbName%%_today%.bak' WITH NOFORMAT, NOINIT, NAME = N'%dbName%-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

COPY "%tempBackLoc%%dbName%%_today%.bak" "%copyBackLoc%%dbName%_%_today%.bak"

Now because the SQL Directory is within the the enviroment variables of the system (by default on install of sql) For those of you who do not know where this is I have posted how to check

--Windows XP and Server 2003. --

Right click My Computer > Properties > Advanced Tab > Evironment Variables > System Variables > Path (the one that includes C:\windows\system32) In here if you hit edit you will see at the end there is a location posted. c:\Program Files\Microsoft SQL Server\90\Tools\binn\

--Windows Vista and Server 2008 --

Right click Computer > Properties > Advanced System Settings (left Side) > Evironment Variables > System Variables > Path (the one that includes C:\windows\system32) In here if you hit edit you will see at the end there is a location posted. c:\Program Files\Microsoft SQL Server\90\Tools\binn\

Note: If you are using SQL Expres 2008 or SQL 2008 you will probable see the following:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn

Now because this path is in the System Eviroment Variables I can just call sqlcmd and it will automatically know what file I am calling. So this we pass all the variables as paramaters that the sqlcmd requires to run the query of BACKUP. We name the file with the tempBackLoc then the dbName then the _today.

@REM -- checks to see if the file successfully got copied. If not it goes to the copy bad

IF NOT EXIST "%copyBackLoc%%dbName%_%_today%.bak" GOTO COPYBAD

We now check to see if the database actually backed up. If it did not copy correctly we exit and log error to the log location.

@REM if the copied Existed. Then we write a log letting the person know (this will also append to a previous log)

ECHO #################### %_today% -- executed SQL_autoBackUp.bat #################### >> %logLoc%%dbName%_log.txt

ECHO -- >> %logLoc%%dbName%_log.txt

ECHO Copied file %copyBackLoc%%dbName%_%_today%.bak successfully >> %logLoc%%dbName%_log.txt

ECHO -- >> %logLoc%%dbName%_log.txt

ECHO ####################################################################################### >> %logLoc%%dbName%_log.txt

ECHO ----- >> %logLoc%%dbName%_log.txt

GOTO DELETEORIG

Here we write that we executed the batch file and that it created the following files successfully

:COPYBAD

ECHO #################### %_today% -- executed SQL_autoBackUp.bat #################### >> %logLoc%%dbName%_log.txt

ECHO -- >> %logLoc%%dbName%_log.txt

ECHO Copy failed >> %logLoc%%dbName%_log.txt

ECHO -- >> %logLoc%%dbName%_log.txt

ECHO ####################################################################################### >> %logLoc%%dbName%_log.txt

ECHO ----- >> %logLoc%%dbName%_log.txt

EXIT

Else we write to the log file telling them that it did not back up successfully

:DELETEORIG

IF %deleteLocal% == Y DEL "%tempBackLoc%%dbName%%_today%.bak"

EXIT

Finally we check to see if they want us to delete the local copy of the file. (tempBackLoc) If so then we delete it

@REM --- Messages to users who do not define params

:defineDatabase

ECHO Please Define a Database Name within your params

PAUSE

EXIT

:defineTempBackLoc

ECHO Please define a temporary Back Up Location within your params

PAUSE

EXIT

:defineCopyBackLoc

ECHO Please define a final (copy) Back Up Location within your params

PAUSE

EXIT

:defineLogLoc

ECHO Please define a Log Location within your params

PAUSE

EXIT

and as the comment says all these are, are messages that if the user does not define certain things I can tell them.

Please note that the tempBackLoc (temp back up location) must have SQL Security rights. This is a local security group that gets created on the server. Normally looks like the following:

SQLServer2005MSSQLUser$COMPNAME$INSTANCENAME

In my case I have:

SQLServer2005MSSQLUser$MyTekkiSrv$SQLEXPRESS2005

If you wish for the entire source of this file. Please use the following link

http://mytekki.com/public/scripts/bat/SQLExpressBackUp.bat