works only on 2005 and up.
SELECTt.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
The second article in a 3 part series that presents another VBScript class file that creates a class that can be used to display the backup information from a SQL server's maintanence plans.
2003-08-28
6,819 reads
This article by new contributing member Bob Musser shows you how to reduce the amount of scans that SQL Server Agent does against databases. Not for the faint of heart.
2003-02-13
4,950 reads
Are you tired of manually restoring each database on a new server when the original server has a melt down? Does the manual process seem slow, and prone to keystoke and mouse click errors? Would you like to have those restore scripts automatically built, so you only have to fire them off? Well this article will show you one possible method for speeding up and reducing errors will trying to perform a restore of all databases on a server.
2002-11-05
9,005 reads
Steve found a gaping hole in his backup process. One that he plugged with this script to move log files around the network in the event of a server failure. If you can't afford to lose a day of work, read about his solution.
2002-09-30
6,267 reads
A VBscript class is created that can be used to query the maintenance plans on an SQL server to determine when a backup has occurred. This class can be used with others to create a scripted method for monitoring backups.
2002-08-22
6,321 reads
