SQL Flat File Daily Backup Job and Daily Checks script

Backup of MS SQL 2005/2008 Databases from SQL Maintenace job is way easier and quicker when it comes to restoring compared to BackupExec or similar products. 
Also SQL Agent maintains the log of scheduled maintenance task, successes and failures etc.

Following are the steps for creating automated SQL Backup Maintenance job and Batch script to check if your backup has been successful and drop you an email on daily basis
 

Create SQL Backup Maintenance Job
http://www.freetutorialssubmit.com/create-ms-sql-backup-maintenance-plan/1585

 
 Verify backup path and Backup file format


 

Create Batch script to monitor the Backup folders

Create a batch file sqlcheck.bat
  @echo off
FOR /F "TOKENS=1,2 DELIMS=/ " %%A IN ('DATE /T') DO SET mm=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('DATE /T') DO SET yyyy=%%B
FOR /F "TOKENS=1 DELIMS=/ " %%A IN ('DATE /T') DO SET dd=%%A

set efin=%mm%%dd%%yyyy%
if %dd% LSS 10 set dd=%dd:~-1%
set /A dd=%dd%-1
IF %dd% LSS 10 set dd=0%dd%


if %dd% equ 00 if %mm% equ 01 (
Set dd=31
Set mm=12

)

if %dd% equ 00 if %mm% equ 02 (
Set dd=31
Set mm=01

)

if %dd% equ 00 if %mm% equ 03 (
Set dd=28
Set mm=02

)

if %dd% equ 00 if %mm% equ 04 (
Set dd=31
Set mm=03

)
if %dd% equ 00 if %mm% equ 05 (
Set dd=30
Set mm=04

)
if %dd% equ 00 if %mm% equ 06 (
Set dd=31
Set mm=05

)
if %dd% equ 00 if %mm% equ 07 (
Set dd=30
Set mm=06


)
if %dd% equ 00 if %mm% equ 08 (
Set dd=31
Set mm=07

)

if %dd% equ 00 if %mm% equ 09 (
Set dd=31
Set mm=08

)

if %dd% equ 00 if %mm% equ 10 (
Set dd=30
Set mm=09

)

if %dd% equ 00 if %mm% equ 11 (
Set dd=31
Set mm=10

)

if %dd% equ 00 if %mm% equ 12 (
Set dd=30
Set mm=11

)


set ymd=%yyyy%%mm%%dd%
REM Backup file format Northwind20121217.bak

set y_m_d=%yyyy%_%mm%_%dd% 
REM Backup file format Northwind_2012_12_17.bak
 
:END

REM Examples
dir \\RepositoryServer\d$\backup\*.bak |find "/"
dir \\RepositoryServer\Backup\DatabaseName\*.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%ymd%*1400.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%ymd%*.bak | find "/"
dir \\RepositoryServer\Backup\DatabaseName\*%y_m_d%*1400.bak | find "/"

Schedule to run daily and email the report



download BMAIL.exe to email the report


Create second batch file sqlreport.bat to count number of lines to ensure all the databases are backed up and add it to Windows Scheduled Task:


sqlcheck.bat > sqlcheck.txt

REM following will count no of Databases backed up and add on the bottom of the txt file
for /f "tokens=* delims= " %%t in ('dir/b sqlcheck.txt') do (
for /f "tokens=* delims= " %%a in (%%t) do (
set /a N+=1
)
)
@echo ****************************** >> "SQLCheck.txt"
echo Total Backedup files count = !N! >>"SQLCheck.txt"
 
REM 120 are the number of databases my script was checking daily
Set /A p=120-%N%
echo Flat Files missed = %p% >>"SQLCheck.txt"


bmail -s smtpservername -t emailaddress@domain.com -f sendersaddress@domain.com-h -a "SQLCheck" -m SQLCheck.txt -c