December 7, 2010 at 5:46 am
Hi,
I nice fellow on here was able to help me inventory my numerous new 2005 database backups and compatibility level and so on using this.
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
b.recovery_model_desc as 'Recovery Model',
case
when state_desc <> 'ONLINE' then state_desc -- Alert that DB might be ReadOnly, Offline etc...
else ' '
end as 'DB Status', B.compatibility_level
FROM master.sys.databases B
LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name
GROUP BY B.name , a.type, b.recovery_model_desc, state_desc, B.compatibility_level
ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc
But when trying to run this against a SQL Server 8.0.760 it doesn't work. Could some one explain why there is this difference between 2000/2005 and have some suggestions on how to get the same result from SQL Server 2000?
December 7, 2010 at 5:51 am
The sys.databases view is new on SQL 2005 and hence it won';t work. There may have been changes to the MSDB tables as well.
Look up sysdatabases and DATABASEPROPERTYEX() for the SQL 2000 equivalent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2010 at 6:09 am
Thanks Gail! I found a wealth of info and already tweaking it now for my use. I really appreciate it!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply