@echo off
::
:: WinPharm Database Automated Backup, for Windows Task Scheduler
::
:: VER 3.0
::
:: 	The WPBACKUP.bat file will need to be edited providing your server, database, 
::	and backup file location parameters
:: 
:: 	WPSERVER=your SQL server name or IP and \instance if not the default SQL instance
:: 	WPDATABASE=the database to backup
:: 	OUTPUTDIR=the full path to store the backup and log files
::	(if OUTPUTDIR does not exist, it will be created)
:: 
:: example
:: 
:: SET WPSERVER=server\instance
:: SET WPDATABASE=WINPHARM
:: SET OUTPUTDIR=C:\WPDATA
:: 
:: After saving the changes - the bat file can be set as a scheduled task to be executed daily or at the
:: desired frequency.  If backup is desired more than once daily, then this batch file should not be used.
:: 	The task must be scheduled to be run by an administrator or a user with backup rights to the
:: 	specified database.
:: 
:: This bat file will create an SQL backup of the database (WPDATABASE) to a file which will be placed into
:: the (OUTPUTDIR) folder.  The backup file will be named (WPDATABASE)_day_BACKUP.bkf. 
::	Example: WinPharm_MON_BACKUP.bkf
::
:: If scheduled as a Daily task, there will be a 7 day backup history which overlays every week.
:: 
:: if the operating system is Windows 7 or later: 
::	Events will be logged into the Windows event logs for successful backups and any error that occurs.  
:: 	Using scheduled tasks you can trigger a notification on the WinPharm Event ID.
:: 
:: 	SOURCE: WinPharm
:: 
:: 	EVENT #: 990  - Start of Backup
:: 	EVENT #: 991  - Successfull Backup
:: 	EVENT #: 999  - Failed Backup
::
:: For operating systems of XP and older there will be an error displayed at the point of attempting to log
:: the events but the backup will process properly.  If you want the events logged, you may be able to
:: download the necessary Windows component to EVENTCREATE.  Contact IBS with questions if desired.
:: 

SET WPSERVER=(local)
SET WPDATABASE=WINPHARM
SET OUTPUTDIR=C:\WPDATA

IF NOT EXIST "%OUTPUTDIR%" (
  MD "%OUTPUTDIR%"
)

ECHO sText = Weekday(Now) >> "%OUTPUTDIR%\WEEKDAY.VBS"
ECHO sFilePath = "%OUTPUTDIR%\date.txt" >> "%OUTPUTDIR%\WEEKDAY.VBS"
ECHO Set objFSO = CreateObject("Scripting.FileSystemObject") >> "%OUTPUTDIR%\WEEKDAY.VBS"
ECHO Set objTextFile = objFSO.CreateTextFile(sFilePath, True) >> "%OUTPUTDIR%\WEEKDAY.VBS"
ECHO objTextFile.Write (sText) >> "%OUTPUTDIR%\WEEKDAY.VBS"
ECHO objTextFile.Close >> "%OUTPUTDIR%\WEEKDAY.VBS"

cscript /B /NOLOGO "%OUTPUTDIR%\WEEKDAY.VBS"

find/i "1" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=SUN
find/i "2" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=MON
find/i "3" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=TUE
find/i "4" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=WED
find/i "5" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=THU
find/i "6" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=FRI
find/i "7" "%OUTPUTDIR%\date.txt" > nul
if %ERRORLEVEL% EQU 0 SET Day=SAT

del "%OUTPUTDIR%\date.txt"
del "%OUTPUTDIR%\WEEKDAY.VBS"

IF EXIST %OUTPUTDIR%\backuputil.txt (
   del %OUTPUTDIR%\backuputil.txt
)

ECHO *** WinPharm Backup of DATABASE:%WPDATABASE% on SERVER:%WPSERVER% Starting at: %DATE% %TIME%, Log File: %OUTPUTDIR\WPBACKUP.LOG"
ECHO.
ECHO *** WinPharm Backup of DATABASE:%WPDATABASE% on SERVER:%WPSERVER% Starting at: %DATE% %TIME% >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"

EVENTCREATE /T INFORMATION /ID 990 /L APPLICATION /SO WinPharm /D "Backup of DATABASE: %WPDATABASE% on SERVER: %WPSERVER% Starting: %DATE% %TIME% Log File: %OUTPUTDIR%\WPBACKUP.LOG" > nul

osql /? > "%OUTPUTDIR%\backuputil.txt"
find/i "Microsoft" "%OUTPUTDIR%\backuputil.txt" > nul
if %ERRORLEVEL% EQU 0 (
   ECHO osql -S "%WPSERVER%" -E -Q "BACKUP DATABASE [%WPDATABASE%] TO DISK = N'%OUTPUTDIR%\%WPDATABASE%_%Day%_BACKUP.bkf' WITH INIT"
   osql -S "%WPSERVER%" -E -Q "BACKUP DATABASE [%WPDATABASE%] TO DISK = N'%OUTPUTDIR%\%WPDATABASE%_%Day%_BACKUP.bkf' WITH INIT" >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
   GOTO SHOWLOG
)

del "%OUTPUTDIR%\backuputil.txt"
sqlcmd /? > "%OUTPUTDIR%\backuputil.txt"
find/i "Microsoft" "%OUTPUTDIR%\backuputil.txt" > nul
if %ERRORLEVEL% EQU 0 (
   ECHO sqlcmd -S "%WPSERVER%" -E -Q "BACKUP DATABASE [%WPDATABASE%] TO DISK = N'%OUTPUTDIR%\%WPDATABASE%_%Day%_BACKUP.bkf' WITH INIT"
   sqlcmd -S "%WPSERVER%" -E -Q "BACKUP DATABASE [%WPDATABASE%] TO DISK = N'%OUTPUTDIR%\%WPDATABASE%_%Day%_BACKUP.bkf' WITH INIT" >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
   GOTO SHOWLOG
)

ECHO *** ERROR *** - Unable to Locate Command Line SQL Utility >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO *** ERROR *** - Unable to Locate Command Line SQL Utility >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO *** ERROR *** - Unable to Locate Command Line SQL Utility >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"

EVENTCREATE /T ERROR /ID 999 /L APPLICATION /SO WinPharm /D "Backup Command Line Utility Not Found" > nul

:SHOWLOG

ECHO *** WinPharm Backup Ending: %DATE% %TIME% >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO. >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO. >> "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO.
ECHO LogFile:
TYPE "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
ECHO.

find/i "successfull" "%OUTPUTDIR%\WPBACKUP_TMP.LOG" > nul
if %ERRORLEVEL% EQU 0 (
   SetLocal EnableDelayedExpansion
   set content=
   set LF=^


   rem Two empty lines are required here
   for /F "delims=" %%i in ("%OUTPUTDIR%\WPBACKUP_TMP.LOG") do set content=!content!!LF!%%i
   EVENTCREATE /T INFORMATION /ID 991 /L APPLICATION /SO WinPharm /D "Backup of DATABASE: %WPDATABASE% on SERVER: %WPSERVER% Completed Successfully: %DATE% %TIME%!LF!!LF!Log File: %OUTPUTDIR%\WPBACKUP.LOG!LF!!LF!!CONTENT!" > nul
   EndLocal
) else (
   SetLocal EnableDelayedExpansion
   set content=
   set LF=^


   rem Two empty lines are required here
   for /F "delims=" %%i in ("%OUTPUTDIR%\WPBACKUP_TMP.LOG") do set content=!content!!LF!%%i
   EVENTCREATE /T ERROR /ID 999 /L APPLICATION /SO WinPharm /D "Backup Failed!LF!!LF!!content!" > nul
   EndLocal
)

IF EXIST "%OUTPUTDIR%\WPBACKUP.LOG" (
   COPY /Y "%OUTPUTDIR%\WPBACKUP.LOG"+"%OUTPUTDIR%\WPBACKUP_TMP.LOG" "%OUTPUTDIR%\WPBACKUP_TMP2.LOG" > nul
   COPY /Y "%OUTPUTDIR%\WPBACKUP_TMP2.LOG" "%OUTPUTDIR%\WPBACKUP.LOG" > nul
   DEL /Q "%OUTPUTDIR%\WPBACKUP_TMP2.LOG"
) else (
   COPY /Y "%OUTPUTDIR%\WPBACKUP_TMP.LOG" "%OUTPUTDIR%\WPBACKUP.LOG"
)
DEL /Q "%OUTPUTDIR%\WPBACKUP_TMP.LOG"
DEL /Q "%OUTPUTDIR%\backuputil.txt"