Check Backups

, 2013-02-13 (first published: )

This procedure will check the last full backups and report databases that have not been backed in a certain amount of days.

The prodedure will also report on databases that have not been backed up at all.

Please feel free to make suggestions and comments on how this could be improved.

CREATE PROC CheckBackups
as
DECLARE @db nvarchar (100)
DECLARE @maxbackupfinishdate datetime
DECLARE @daysago nvarchar(50)
DECLARE @threshold nvarchar(10) -- Days since last full backup
DECLARE @msgtext nvarchar (500)           
set @threshold = 3
PRINT '========CHECKING THAT BACKUPS HAVE BEEN DONE IN THE LAST '+@threshold+' DAYS========='               
DECLARE Backups CURSOR 
FOR select database_name
,max(backup_finish_date)
,datediff(day,max(backup_finish_date),getdate())
from msdb.dbo.backupset
where type = 'D'
and database_name in (select name from sys.databases) 
group by database_name
order by database_name
OPEN Backups
FETCH NEXT FROM Backups into @db,@maxbackupfinishdate,@daysago
WHILE @@FETCH_STATUS = 0
BEGIN
set @msgtext = 'Database '''+@db+''' - WAS LAST BACKED UP '+@daysago+' DAYS AGO'
IF @daysago >  @threshold
begin 
print @msgtext
end
FETCH NEXT FROM Backups into @db,@maxbackupfinishdate,@daysago
END
CLOSE Backups
DEALLOCATE Backups
PRINT '
========CHECKING THAT BACKUPS HAVE BEEN DONE ON ALL DATABASES========='
DECLARE @db2 nvarchar (100)
DECLARE @msgtext2 nvarchar (500)  
DECLARE Backups2 CURSOR 
FOR select name from sys.databases where name <> 'tempdb'
except
select distinct database_name from msdb.dbo.backupset
OPEN Backups2
FETCH NEXT FROM Backups2 into @db2
WHILE @@FETCH_STATUS = 0
BEGIN
set @msgtext2 = 'Database '''+@db2+''' - HAS NOT BEEN BACKED UP'
PRINT @msgtext2
FETCH NEXT FROM Backups2 into @db2
END
CLOSE Backups2
DEALLOCATE Backups2

Rate

2.33 (3)

Share

Share

Rate

2.33 (3)

Related content

Backup to the Cloud: The Silver Lining

Offsite backups are vital to data integrity, allowing for preservation and backup of data in catastrophic circumstances. Cloud backups have been around for a while, but are now really coming into their own. Tony Davis looks at how to get set up with cloud backups for restores and disaster recovery.

2013-09-16

2,399 reads