December 12, 2006 at 9:43 am
I have a backups of sql servers on various machines. All the backups are scheduled daily. I want to monitor the backups using a web page. Any ideas
December 12, 2006 at 2:37 pm
Create a procedure on each server to get the backup info and execute them daily once and copy the output to a central location table and read the results from central table...
You can create html page using xp-makewebtaskor, see BOL for more details...
Modify the following proc.. as needed...
CREATE PROC sp_Backup_Report
as
set nocount on
-- drop table #t
if object_id('tempdb.dbo.##mbackup') is not null
drop table tempdb.dbo.##mbackup
select
database_name as 'Database_Name' ,
c.physical_name as 'Database_File_Location' ,
(b. physical_device_name) as 'Backup_Location' ,
Convert( numeric(20,2), ((backup_size)/1024)/1024 )as 'Backup_size_MB',
DATEDIFF ( mi , MIN(backup_start_date) , MAX(backup_finish_date)) as 'BackupTime_in_Minutes',
Convert( numeric(20,2), (((backup_size)/1024)/1024 )/ case when DATEDIFF ( ss , (backup_start_date) , (backup_finish_date))= 0 then 1 else
DATEDIFF ( ss , (backup_start_date) , (backup_finish_date))end ) as 'Thoughput_MB_Sec',
backup_start_date,
backup_finish_date,
DATEDIFF ( dd , (backup_start_date) , (getdate())) 'Backup_No_Days_Old',
'Comments' = Convert(Varchar(10),DATEDIFF ( dd , (backup_start_date) , (getdate())))+' days old backup....'
/*
'Comments' = CASE WHEN a.backup_start_date > DATEADD(dd,-1,getdate()) THEN 'Backup is current within a day'
WHEN a.backup_start_date > DATEADD(dd,-4,getdate()) THEN 'Backup is 3 days old'
ELSE '*****CHECK BACKUP!!!*****'
END
*/
into ##mbackup
FROM msdb.dbo.backupset a
join msdb.dbo.backupmediafamily b on a.media_set_id = b.media_set_id
join msdb.dbo.backupfile c on a.backup_set_id = c.backup_set_id
where backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset
WHERE database_name = a.database_name
AND type = 'D') -- full database backups only, not log backups
and type = 'd' and server_name = @@servername and c.file_type = 'd' and c.physical_name like '%.mdf'
group by database_name, backup_size, backup_finish_date, backup_start_date,b.physical_device_name,c.physical_name,
backup_start_date,
backup_finish_date
select
@@servername as 'SQLServer_instance_Name'
, a.name as 'Database Name'
, DATABASEPROPERTYEX (a.name, 'Recovery') as 'Recovery_Model'
--'Simple' as 'Recovery_Model',
, a.filename as Database_File_Location
, isnull (b.Backup_Location, '***** Backup Never Started*****') as Backup_Location
, isnull (b.Backup_size_MB,0) as Backup_size_MB
, isnull (b.BackupTime_in_Minutes,0) as BackupTime_in_Minutes
, isnull (b.Thoughput_MB_Sec,0) as Thoughput_MB_Sec
, isnull (b.Backup_start_date, '1/1/1900') as Backup_start_date
, isnull (b.Backup_finish_date,'1/1/1900') as Backup_finish_date
, isnull (b.Backup_No_Days_Old,'99999') as Backup_No_Days_Old
, isnull (b.Comments, '***** Backup Never Started *****') as Comments
FROM master.dbo.sysdatabases a (nolock)
left join ##mbackup b on b.database_name = a.name
where a.name not in ('model','pubs','tempdb', 'Northwind','Lumigent')
and DATABASEPROPERTYEX( a.name, 'Status') = 'ONLINE' and DATABASEPROPERTYEX( a.name, 'Updateability') = 'READ_WRITE'
MohammedU
Microsoft SQL Server MVP
December 12, 2006 at 3:21 pm
Reading the backup history is great, but it might not show you when backups failed. For that you need to look at the SQL Agent log. This is not a finished work of art, I get multiple messages a few milliseconds apart for some backup failures.
CREATE
TABLE #log (
LogDate datetime,
ProcessInfo varchar(50),
Text varchar(max))
go
-- Read enough log cycles to ensure you have all the rows you need for the intended date range
INSERT INTO #log
EXEC xp_readerrorlog
INSERT INTO #log
EXEC xp_readerrorlog 1
INSERT INTO #log
EXEC xp_readerrorlog 2
go
-- Merge the successful and unsuccessful messages and parse out the DB names
SELECT * FROM (
SELECT LogDate, substring(text, 55, charindex('.', Text) - 55) AS DBName, 'Failed' AS Result
FROM #log WHERE processinfo = 'Backup'
AND Text LIKE 'BACKUP failed to complete the command BACKUP DATABASE %'
AND LogDate >= dateadd(week, -4, getdate())
UNION ALL
SELECT LogDate, substring(text, 31, charindex(',', Text) - 31) AS DBName, 'Succeeded' AS Result
FROM #log WHERE processinfo = 'Backup'
AND Text LIKE 'Database backed up. Database: %'
AND LogDate >= dateadd(week, -4, getdate())
) x
ORDER BY LogDate
DROP TABLE #log
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply