SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Henrico Bekker / Database State and Backup Information / Latest Posts

RE: Database State and Backup Information

Henrico Bekker — Thu, 22 Apr 2010 09:35:04 GMT

Noted, and thank you for the feedback. [code="sql"]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], ROUND((((COALESCE(Convert(real(256), MAX(u.backup_size), 101),'NA'))/1024.000)/1024.000),3) 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.type), 101),'NA')) as [Backup_type], (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[/code]

RE: Database State and Backup Information

LondonNick — Thu, 22 Apr 2010 09:01:39 GMT

You may want to consider differentiating between types of backups. You may have log backups running fine but no full backups taking place.Also I would consider including databases which do not have records in msdb.dbo.backupset to highlight any database for which no backups have taken place.

RE: Database State and Backup Information

Abrar Ahmad_ — Thu, 22 Apr 2010 08:23:03 GMT

Good work!but you seems to use extra(not required) parenthesis. might be good, but affected readability a bit. (just general comments not to take it serious).2ndly, the code segment[code="other"]Convert(real(256), MAX(u.backup_size), 101),'NA'))/1024)/1024[/code]may result into truncation of data... !! not specific to this problem but i am trying to raise a general warning here that the code segment may have this in addition [code="other"]Convert(real(256), MAX(u.backup_size), 101),'NA'))/1024[b].0000[/b])/1024[b].0000[/b][/code]as you may know that Sql Server may return results in INT data type rather than in fractional (required format), and the CONVERT function will certainly would receive already truncated data (+ not rounded data) and consequently, u may get fractional truncation summation error in matter of summation of large number of records.I hope so, u may get what i intended for.Please don't forget to comment over it.& Thanks

Database State and Backup Information

Henrico Bekker — Thu, 22 Apr 2010 01:21:45 GMT

Comments posted to this topic are about the item [B]Database State and Backup Information[/B]