Simple Daily SQL Server Database Backup Batch File

Introduction

This is a simple DOS batch file to perform your own custom, daily Microsoft SQL Server database backups.

Notes

Makes a backup of the specified SQL Server database appending the day on it, i.e., if you run it on Tuesday, will have '_Tuesday.BAK'.

See the help in the code below for more information/instructions.

Run the batch file without arguments for the help.

Create a Windows scheduled task in Task Scheduler to run your daily backups. Remember to set the scheduled task to 'run as' the appropriate user account.

The Code

@echo off
CLS

REM * SQL_BACKUP_DB.BAT
REM * SQL DAILY BACKUP ROUTINE BATCH FILE
REM * SEE SHOW_COMMAND_LINE_HELP AT BOTTOM FOR MORE INFO

IF "%2"=="" GOTO SHOW_COMMAND_LINE_HELP
IF "%3"=="" GOTO SHOW_COMMAND_LINE_HELP

REM DETERMINE DAY
echo. | date | find/i "sat" >nul
if not errorlevel 1 goto saturday >nul
echo. | date | find/i "sun" >nul
if not errorlevel 1 goto sunday >nul
echo. | date | find/i "mon" >nul
if not errorlevel 1 goto monday >nul
echo. | date | find/i "tue" >nul
if not errorlevel 1 goto tuesday >nul
echo. | date | find/i "wed" >nul
if not errorlevel 1 goto wednesday >nul
echo. | date | find/i "thu" >nul
if not errorlevel 1 goto thursday >nul

rem friday set day=Friday
goto next

:saturday
set day=Saturday
goto next

:sunday
set day=Sunday
goto next

:monday
set day=Monday
goto next

:tuesday
set day=Tuesday
goto next

:wednesday
set day=Wednesday
goto next

:thursday
set day=Thursday

:next

REM DELETE PREVIOUS BACKUP FILE FIRST
IF EXIST %3\%2_%day%.BAK DEL %3\%2_%day%.BAK >NUL

REM DO BACKUP!
IF '%1'=='""' GOTO SQLLOCAL
SET INST=.\%1
GOTO SQLNEXT
:SQLLOCAL
SET INST=.
:SQLNEXT
SET MYSELF=%~n0%~x0
ECHO.
ECHO BACKING UP DB '%2' ON SERVER/INSTANCE '%INST%' TO '%3\%2_%day%.BAK'...
SQLCMD -S %INST% -E -Q "BACKUP DATABASE %2 TO DISK='%3\%2_%day%.BAK'" -o %MYSELF%_LASTRUN.TXT
IF NOT %ERRORLEVEL%==0 GOTO BACKUPERROR

GOTO DONE

:BACKUPERROR
ECHO ERROR! BACKUP FAILED.  ERRORLEVEL=%ERRORLEVEL%
GOTO DONE

:SHOW_COMMAND_LINE_HELP
ECHO.
ECHO *******************************************************************************
ECHO * SQL DAILY BACKUP ROUTINE
ECHO *
ECHO * CREATES A SQL DB BACKUP TO THE SPECIFIED FOLDER FOR EACH DAY WITH
ECHO * THE DAY NAME ADDED TO THE SPECIFIED FILENAME
ECHO *
ECHO * BY MIKE TRANK OF UNLIMIDEV, INC. - 7-6-2015
ECHO *
ECHO * COMMAND-LINE ARGUMENTS...
ECHO * %1 - 1ST CMDLINE = SQL INSTANCE NAME (USE "" IF DEFAULT INSTANCE)
ECHO * %2 - 2ND CMDLINE = SQL DB NAME
ECHO * %3 - 3RD CMDLINE = BACKUP DESTINATION PATH *WITHOUT* AN ENDING BACKSLASH
ECHO *
ECHO * *NOTES*  -IF ANY ARGUMENTS CONTAIN SPACES, WRAP IT WITH QUOTES ("")
ECHO *          -IS DESIGNED TO RUN ON THE SPECIFIED SQL SERVER
ECHO *          -MAKES A TEXT OUTPUT FILE PREFIXED WITH THE SAME NAME AS THIS BATCH
ECHO *           FILE THAT CONTAINS THE SQL RESULTS OF THE LAST RUN.
ECHO *          -ASSUMES THAT THE USER RUNNING THIS BATCH FILE HAS SQL PERMISSIONS
ECHO *           TO BACK UP THE SPECIFIED DATABASE AND WRITE TO THE SPECIFIED FOLDER
ECHO *
ECHO * EXAMPLES...
ECHO *
ECHO * BACKUPDB.BAT "" HEALTHDATA E:\BACKUP\HEALTHDATA
ECHO *
ECHO *   IF RUN ON A TUESDAY, WILL MAKE A SQL DB BACKUP OF DATABASE 'HEALTHDATA' ON
ECHO *   THE LOCAL, DEFAULT SQL SERVER/INSTANCE TO A FILE NAMED
ECHO *   'HEALTHDATA_Monday.BAK' IN FOLDER 'E:\BACKUP\HEALTHDATA'
ECHO *
ECHO *
ECHO * BACKUPDB.BAT SHAREPOINT MIKEDB E:\BACKUP\MIKEBACKUPS
ECHO *
ECHO *   IF RUN ON A FRIDAY, WILL MAKE A SQL DB BACKUP OF DATABASE 'MIKEDB' ON THE
ECHO *   LOCAL SQL SERVER WITH INSTANCE NAME 'SHAREPOINT' TO A FILE NAMED
ECHO *   'MIKEDB_Friday.BAK' IN FOLDER 'E:\BACKUP\MIKEBACKUPS'
ECHO *
ECHO *
ECHO * CMD /C BACKUPDB.BAT "" MYDB "\\BACKUPSVR1\DB Backups\MYDB"
ECHO *
ECHO *   YOU CAN RUN UNDER A 'COMMAND' SHELL IF NEEDED.
ECHO *   IF RUN ON A WEDNESDAY, WILL MAKE A SQL DB BACKUP OF DATABASE 'MYDB' ON
ECHO *   THE LOCAL, DEFAULT SQL SERVER INSTANCE A TO FILE NAMED 'MYDB_Wednesday.BAK'
ECHO *   IN SHARE '\\BACKUPSVR1\Backups\Custom Database'
ECHO *******************************************************************************

:DONE
REM DONE

More Notes

For multiple backups, make a main batch file and then add a bunch of calls to this batch file, i.e.:

MyBackups.Bat

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

Call SQL_BACKUP_DB.BAT "" MYDB1 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "" MYDB2 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "" MYDB3 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "" MYDB4 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "" MYDB5 D:\MYBACKUPS

Call SQL_BACKUP_DB.BAT "INSTANCE1" MYDB61 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "INSTANCE1" MYDB7 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "INSTANCE2" MYDB8 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "INSTANCE2" MYDB9 D:\MYBACKUPS
Call SQL_BACKUP_DB.BAT "INSTANCE3" MYDB0 D:\MYBACKUPS

History

Made this do a bunch of custom backups using my own scripts/methods instead of the built-in SQL backup plans, etc. Originally was half the size, but then added the help for convenience.

v1.0 - 2015-07-06