Click here to Skip to main content
15,868,164 members
Articles / Database Development
Tip/Trick

Simple Daily SQL Server Database Backup Batch File

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
6 Jul 2015CPOL 13K   1   1
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

BAT
@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.:

BAT
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
UnlimiDev, Inc.
United States United States
This member doesn't quite have enough reputation to be able to display their biography and homepage.
This is a Organisation (No members)


Comments and Discussions

 
QuestionThoughts Pin
Wendelius6-Jul-15 20:58
mentorWendelius6-Jul-15 20:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.