@echo off
Rem Change DBNAME and backup location to appropriate
Rem The backup location MUST EXIST
Rem THIS MUST BE RUN ON THE SQL SERVER!
Rem
rem [Setup]
rem When entering the tempdrive, tempdir, and dest values,
rem ******DO NOT******
rem use any slashes!
rem
set tempdrive=
set tempdir=
set dest=
set SQLSrvr=
set dbname=
set SQLUser=
set SQLpassword=
set zippassword=
set DaysToKeep=

rem The following will remove any : or spaces from the date, to allow
rem date-stamping the backup files
Set filedate=%date:/=%
Set filedate=%filedate: =%
Set filetime=%time::=%
set filetime=%filetime:.=%

rem Clean up database backups older than 1 week from destination media
rem To change the number of days of backup files to keep, change the DaysToKeep
rem above with a new value (higher numbers will save more backups)
%dest%
Forfiles /D -%DaysToKeep% /M *.BAK /C "cmd /c del @file"
Forfiles /D -%DaysToKeep% /M *.ZIP /C "cmd /c del @file"

%tempdrive%
cd %tempdir%
Forfiles /D -%DaysToKeep% /M *.BAK /C "cmd /c del @file"
Forfiles /D -%DaysToKeep% /M *.ZIP /C "cmd /c del @file"

rem ****************************************************************************
rem This will attempt to use Windows Authentication to run, using the User
rem Account configured in the scheduled task, or the currently logged in User
rem if double-clicking the batch file.
rem If this does not work,
rem un-rem the line: "rem sqlcmd -U %SQLuser% -P %SQLpassword% -q..."
rem and rem the line: "sqlcmd -q "USE %dbname%" -Q "BACKUP..."
rem ****************************************************************************

rem sqlcmd -U %SQLuser% -P %SQLpassword% -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK'"

sqlcmd -S %sqlSrvr% -Q "BACKUP DATABASE %dbname% TO DISK = '%tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK'"
if not exist %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK goto bad

pkzipc -add -fast -move -dir=current -pass=%zippassword% %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.zip %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.BAK
xcopy %tempdrive%\%tempdir%\%dbname%_%filedate%_%filetime%.zip %dest%\
if not exist %dest%\%dbname%_%filedate%_%filetime%.zip goto badcopy

:done
echo. >> %dest%\BackupLog.log
echo %date% - %time% >> %dest%\BackupLog.log
echo ------------------------ >> %dest%\BackupLog.log
echo Database backup completed OK >> %dest%\BackupLog.log
echo. >> %dest%\BackupLog.log
echo. >> %dest%\BackupLog.log
echo ************************************* >> %dest%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log
echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log
echo Database backup completed OK >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log

goto end

:bad
echo. >> %dest%\BackupLog.log
echo %date% - %time% >> %dest%\BackupLog.log
echo ------------------------ >> %dest%\BackupLog.log
echo Database backup encountered a problem >> %dest%\BackupLog.log
echo Check SQL Server Logs for more information >> %dest%\BackupLog.log
echo. >> %dest%\BackupLog.log
echo. >> %dest%\BackupLog.log
echo ************************************* >> %dest%\BackupLog.log

echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log
echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log
echo Database backup encountered a problem >> %tempdrive%\%tempdir%\BackupLog.log
echo Check SQL Server Logs for more information >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log
goto end

:badcopy
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo %date% - %time% >> %tempdrive%\%tempdir%\BackupLog.log
echo ------------------------ >> %tempdrive%\%tempdir%\BackupLog.log
echo Database backup encountered a problem >> %tempdrive%\%tempdir%\BackupLog.log
echo when copying backup file to %dest% >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo. >> %tempdrive%\%tempdir%\BackupLog.log
echo ************************************* >> %tempdrive%\%tempdir%\BackupLog.log
goto end

:end