########## Bkp_sla_main.bat ######################
@echo OFF
@if not “%ECHO%”==”” echo %ECHO%
rem *******************************************
rem Script : Bkp_sla_main.bat
rem Author : Neeraj Nagpal
rem Date : 10/08/2004
rem *******************************************
rem Set Local scope and call main proc.
setlocal & pushd & Set RET=
set SCRIPTNAME=%~n0
set SCRIPTPATH=%~f0
set DBASQL=E:dbaSQL
set DBAOUT=E:DBAout
set DEBUG=1
if “%DEBUG%”==”1” (set TRACE=echo) else (set TRACE=rem)
if /i {%1}=={/Help} (call :HELP %2) & (goto :HELPEXIT)
if /i {%1}=={/?} (call :HELP %2) & (goto :HELPEXIT)
call :MAIN %*
:HELPEXIT
popd & endlocal & set RET=%RET%
goto :EOF
rem ********************************************
rem Help Information Follows
:HELP
if defined TRACE %TRACE% [proc %0 %*]
echo This script will check the availability of a server.
echo Usage : sqlchk srvname
goto :EOF
rem *******************************************
rem Main Procedure will follow
:MAIN
if defined TRACE %TRACE% [proc %0 %*]
REM ************** Step 0 *** Delete the data in the exception table for today’s date ***************************
isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -i%DBASQL%Before_bkp_sla.sql > %DBAOUT%before_bkp_exception.out
REM **************Step 1 *** Populate the exception report table ***************************
echo “*******Date=%date%::%time% || Start of the DTS step ************” >>%DBAOUT%Bkp_exception_report.log
dtsrun /S SQLSRVCNTREP /N Bkp_exception_report /E >>%DBAOUT%Bkp_exception_report.log
set err=%ERRORLEVEL%
echo Return code is %err%
echo “Return Code = %err%”>>%DBAOUT%Bkp_exception_report1.log
echo “*******End of the DTS step************” >>%DBAOUT%Bkp_exception_report.log
REM **************End of Step 1 *** Populate the exception report table ***************************
REM **************Step 2 *** Run the sql to get the backup exception report ***************************
echo “*******Date=%date%::%time% || Start of the Query execution step ************” >>%DBAOUT%Bkp_exception_report.log
isql -E -S SQLSRVCNTREP -d dbamaint -n -t 60 -i%DBASQL%bkp_exception.sql > %DBAOUT%bkp_exception_report.out
echo “*******Date=%date%::%time% || Sending exception report in email ************” >>%DBAOUT%Bkp_exception_report.log
blat %DBAOUT%bkp_exception_report.out -t neeraj.nagpal@neeraj.com -s “SQL Server: Backup Exception Report” -server timeline -f neeraj.nagpal@neeraj.com
rem — put the main procedure here
rem ******************************************
rem Additional info can be put here
rem *********************************************************************************
############### Create Script for tb_bkp_history ##############
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_bkp_history]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tb_bkp_history]
GO
CREATE TABLE [dbo].[tb_bkp_history] (
[server_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[database_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[report_date] [datetime] NOT NULL ,
[last_bkp_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
############### Create Script for tb_bkp_schedule ##############
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tb_bkp_schedule]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tb_bkp_schedule]
GO
CREATE TABLE [dbo].[tb_bkp_schedule] (
[server_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bkp_schedule] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bkp_sat] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bkp_sun] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
################## Script Before_bkp_sla.sql ###############
Use dbamaint
go
delete from tb_bkp_history where datediff(dd,report_date,getdate())=0
and datediff(mm,report_date,getdate())=0
and datediff(yy,report_date,getdate())=0
go
################## Script bkp_exception.sql ###############
SET NOCOUNT ON
go
use dbamaint
go
select getdate() as “Report Generation Date”
go
PRINT ‘ Backup History not Extracted For Following Server(s)’
PRINT ‘*************************************************************************’
go
if (select datepart(dw,getdate()))=2
Begin
select server_name from tb_bkp_schedule
where bkp_schedule = ‘Daily’
and bkp_sat=’Y’ and bkp_sun=’Y’
and server_name not in ( select distinct server_name from tb_bkp_history
where datediff(hh,report_date,getdate())< 24 )
End
go
if ((select datepart(dw,getdate()))>2 and (select datepart(dw,getdate()))<7)
Begin
select server_name from tb_bkp_schedule
where bkp_schedule = ‘Daily’
and server_name not in ( select distinct server_name from tb_bkp_history
where datediff(hh,report_date,getdate())< 24 )
End
go
PRINT ‘ Daily Backups which have not happened for more than 1 day’
go
PRINT ‘*************************************************************************’
go
Print ”
go
—- Next section is to run the query for Tuesday to Friday
—- datepart(dw,getdate()) = 1 for sunday
—- datepart(dw,getdate()) = 7 for saturday
if ((select datepart(dw,getdate()))>2 and (select datepart(dw,getdate()))<7)
Begin
select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,
max(last_bkp_date) as ‘Last_backup_date’ from dbamaint..tb_bkp_history
Where datediff(dd,report_date,getdate())=0
and datediff(dd,last_bkp_date,getdate())>1
and server_name in (select server_name from tb_bkp_schedule where bkp_schedule = ‘Daily’)
group by server_name,database_name
order by server_name,Last_backup_date desc
End
if (select datepart(dw,getdate()))=2
Begin
select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,
max(last_bkp_date) as ‘Last_backup_date’ from dbamaint..tb_bkp_history
Where datediff(dd,report_date,getdate())=0
and datediff(dd,last_bkp_date,getdate())>1
and server_name in (select server_name from tb_bkp_schedule where bkp_schedule = ‘Daily’ and bkp_sat=’Y’ and bkp_sun=’Y’)
group by server_name,database_name
order by server_name,Last_backup_date desc
End
go
Print ”
go
Print ”
go
Print ”
go
PRINT ‘ Weekly Backups which have not run as schedule’
go
PRINT ‘***************************************************************’
go
Print ”
go
Select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,
max(last_bkp_date) as ‘Last_backup_date’ from dbamaint..tb_bkp_history
Where datediff(dd,report_date,getdate())=0
and datediff(dd,last_bkp_date,getdate())>7
and server_name in (select server_name from tb_bkp_schedule where bkp_schedule = ‘Weekly’)
group by server_name,database_name
order by server_name,Last_backup_date desc
go
Print ”
go
Print ”
go
Print ”
go
PRINT ‘ Monthly Backups which have not run as schedule’
go
PRINT ‘***************************************************************’
go
Print ”
go
Select Convert(Varchar(25),server_name) as ‘Server Name’ ,convert(varchar(25),database_name) as ‘Database’ ,
max(last_bkp_date) as ‘Last_backup_date’ from dbamaint..tb_bkp_history
Where datediff(dd,report_date,getdate())=0
and datediff(dd,last_bkp_date,getdate())>31
and server_name in (select server_name from tb_bkp_schedule where bkp_schedule = ‘Monthly’)
group by server_name,database_name
order by server_name,Last_backup_date desc
go
Print ”
go
Print ”
go
Print ”
go
PRINT ‘ Backup Schedule for all servers’
go
PRINT ‘***************************************************************’
go
select convert(varchar(25),server_name) as “Server_name”,bkp_schedule,bkp_sat,bkp_sun from tb_bkp_schedule
go
Print ‘ Note : Pls send your suggestions to nnagpal@hotmail.com’
go