List all Databases and Last Full Backups

,

This script will create a procedure to list all databases on the server (except tempdb), the last time the database was backed up (database backup, not log backups), and a comment which could be customized.  For my purposes, I flag any database which hasn't been backed up in the last week.  This provides a quick list to check and make sure that backups are all being performed as necessary.

CREATE PROCEDURE check_backups
AS	

SET NOCOUNT ON
select 	'Databases and backups on server ' + @@servername
select 	SUBSTRING(s.name,1,40)			AS	'Database',
	CAST(b.backup_start_date AS char(11)) 	AS 	'Backup Date  ',
	CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
		THEN 'Backup is current within a day'
	     WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
		THEN 'Backup is current within a week'
	     ELSE '*****CHECK BACKUP!!!*****'
		END
						AS 'Comment'

from 	master..sysdatabases	s
LEFT OUTER JOIN	msdb..backupset b
	ON s.name = b.database_name
	AND b.backup_start_date = (SELECT MAX(backup_start_date)
					FROM msdb..backupset
					WHERE database_name = b.database_name
						AND type = 'D')		-- full database backups only, not log backups
WHERE	s.name <> 'tempdb'

ORDER BY 	s.name

Rate

5 (1)

Share

Share

Rate

5 (1)