Checking for SQL backups

[kcuk]

Hi All

I have been asked come up with a way of automating check on sql 2000 backups.

I have the following so far...


cls
set DDate=%date:~0,2%
set MDate=%date:~3,2%
set YDate=%date:~6,4%
set backuppath=D:\SQL Server\Backup
set bkup=_backup_
set bkuptime=2230
set a=""

d:
cd\
cd D:\Notifications
:loop

echo 20 second intervals
echo Waiting for backup file last file check was at %time:~0,8%

for /f %%a in (livedbs.txt) do if exist "%backuppath%\%%a\%%a%bkup%%YDate%%MDate%%DDate%%bkuptime%.bak" goto cont
echo %%ERRORLEVEL%%
for /f %%a in (livedbs.txt) do echo %date% %time:~0,8% - %%a not backed up >> D:\Notifications\backuplog.txt

ping localhost -n 20 -w 10000 > nul

cls

goto loop

:cont
for /f %%a in (livedbs.txt) do echo %date% %time:~0,8% - %%a backedup email sent  >> D:\Notifications\backuplog.txt

And works.... well sort of, if only one of the databases listed in the livedb.txt is true it bombs out stating that all database have backed up.

in otherwords its not looping to check all are ok.

How can i make this script work or do i need another approach?

[ALAN_BR]

XCOPY /M will probably do all the work for you without a script.

It will copy all the files whose Archive bit is set, and also clear that bit so it will not copy it again.

There may be something special about SQL, but normally it is expected that if a file is updated with different information, the Archive bit will be set as the new information is written, and then any backup archiving system such as XCOPY will know that the old backup is out of date.

Use "XCOPY /?" to see the relevant help file with many other useful option switches.

Regards
Alan