Backup information and database status


 

SELECT t.name as [DB Name],
  t.user_access_desc as [Access State], 
  t.state_desc as [Online/Offline], 
  ((SELECT (CASE t.is_in_standby WHEN 0 THEN ‘No’ WHEN 1 THEN ‘Yes’ ELSE ‘Other’ END))) as [In Standby],
  (COALESCE(Convert(datetime, MAX(u.backup_finish_date), 101),’Not Yet Taken’)) as [Last BackUp Taken],
  (((COALESCE(Convert(real(256), MAX(u.backup_size), 101),’NA’))/1024)/1024) as [Backup Size in MB],
  (COALESCE(Convert(varchar(10),MAX(datediff(d, getdate(), u.backup_finish_date))),101)) as [Days since Backup],
  (COALESCE(Convert(varchar(12), MAX(u.user_name), 101),’NA’)) as [User Name]
  
    FROM SYS.DATABASES t
    INNER JOIN msdb.dbo.BACKUPSET u
     ON t.name = u.database_name
   GROUP BY t.Name,t.is_in_standby, t.user_access_desc, t.state_desc
   ORDER BY t.Name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: