Scripts for Backup SLA Report

########## 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:\dba\SQL
set DBAOUT=E:\DBA\out
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