drop table dba..
Transaction_Log_latest_backup_details
-- Query to find latest "Full" as well as "T-Log" backup of all specified Databases
-- Query to find latest "Full" as well as "T-Log" backup of all specified Databases
USE DBA
GO
SELECT msdb.dbo.backupset.server_name
,msdb.dbo.backupset.database_name
,CASE msdb.dbo.backupset.[type] -- Let's decode the main types of backup here
WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
WHEN 'F'
THEN 'File or filegroup'
WHEN 'G'
THEN 'Differential file'
WHEN 'P'
THEN 'Partial'
WHEN 'Q'
THEN 'Differential partial'
ELSE msdb.dbo.backupset.[type]
END AS BackupType
, convert(varchar, msdb.dbo.backupset.backup_finish_date, 100) as
Latest_backup_Date
,CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date) AS INT)) / 3600 AS VARCHAR) + ' hours, ' +
CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date) AS INT)) / 60 AS VARCHAR) + ' minutes, ' +
CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date) AS INT)) % 60 AS VARCHAR) + ' seconds' AS
[Total_Time_Taken_For_Backup]
,msdb.dbo.backupmediafamily.physical_device_name AS Backup_File_Location
into dba..Transaction_Log_latest_backup_details
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
WHERE (
backup_set_ID IN (
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.[type] = 'D'
GROUP BY database_name
)
OR backup_set_ID IN (
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.[type] = 'L'
GROUP BY database_name
) OR
backup_set_ID IN (
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.[type] = 'I'
GROUP BY database_name
)
)and msdb.dbo.backupset.server_name ='fwgongcdb'
ORDER BY msdb.dbo.backupset.database_name
,BackupType
GO
ALTER TABLE dba..Transaction_Log_latest_backup_details
ADD backup_status VARCHAR(20)
go
declare cur1 cursor for SELECT name FROM sys.databases where recovery_model_desc
not like 'SIMPLE'
declare @db15 varchar(90)
open cur1
fetch next from cur1 into @db15
while @@fetch_status=0
begin
declare cur cursor for select Latest_Backup_date,database_name from
dba..Transaction_Log_latest_backup_details where BackupType like '%log%' and
database_name =@db15
declare @db13 varchar(90)
declare @db14 DATETIME
declare @db12 varchar(90)
open cur
fetch next from cur into @db13,@db12
while @@fetch_status=0
begin
--declare @db14 VARCHAR(90)
set @db14=(SELECT convert(varchar, dateadd(hour, -2, GETDATE()), 100))
--set @db14=(SELECT convert(varchar, dateadd(hour, 0, GETDATE()), 100))
print @db14
update dba..Transaction_Log_latest_backup_details set backup_status='YES' where
database_name = @db12 and Latest_Backup_date >=@db14
update dba..Transaction_Log_latest_backup_details set backup_status='NO' where
database_name = @db12 and Latest_Backup_date < @db14
fetch next from cur into @db13,@db12
end
close cur
deallocate cur
fetch next from cur1 into @db15
end
--update dba..Transaction_Log_latest_backup_details set backup_status='NA' where
backup_status='NULL'
close cur1
deallocate cur1
select * from dba..Transaction_Log_latest_backup_details where BackupType like
'%LOG%' and backup_status ='yes' order by backup_status
declare @var int
set @var=( select count(*) from dba..Transaction_Log_latest_backup_details where
BackupType like '%LOG%' and backup_status ='no' )
if @var >=1
begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT server_name AS 'td','',database_name AS 'td','',
BackupType AS 'td','', Latest_Backup_date AS 'td','', Total_Time_Taken_For_Backup
AS 'td','', Backup_File_Location AS 'td','', Backup_status AS 'td'
FROM dba..Transaction_Log_latest_backup_details where BackupType like '%LOG%' and
backup_status ='no'
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Transaction Log Backups report of fwgongcdb server</H3>
<table border = 1>
<tr>
<th> server_name </th> <th> database_name </th> <th> BackupType </th> <th>
Latest_Backup_date </th><th> Total_Time_Taken_For_Backup </th><th>
Backup_File_Location </th><th> Backup_Status </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA',
-- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'hari_prasad@vfc.com;KSFT-VFC-DBA@kochasoft.com', -- replace with
your email address
@subject = 'Transaction Log Backups report of fwgongcdb server' ;
end
else if @var < 1
begin
SET @body ='<html><body><H3>All Transaction Log backups in place.</H3>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA',
-- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'hari_prasad@vfc.com;KSFT-VFC-DBA@kochasoft.com', -- replace with
your email address
@subject = 'All Transaction Log Backups report of fwgongcdb server' ;
end
--select * from dba..Transaction_Log_latest_backup_details
