Verifying Backups and status

Question

Post reply

Verifying Backups and status

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
November 22, 2010 at 11:37 am

#229451

I was trying to run this... and discovered my version (8.0.7) didn't like it
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'
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
ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc
Can anybody help me determine the recovery model, backup type, last backup date on these databases?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First things first. What's the exact version?

SELECT @@Version

8.0.7 doesn't match with any version of SQL 2000.

As for the backups, you're going to have to build up a script yourself. It's not a search-replace on the 2005 views. Start by investigating sysdatabases and msdb.dbo.backupset, see what's there, see what's missing.

Also read up on the DATABASEPROPERTYEX function, you're probably going to need it for at least one of the things you want.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 2

Ok using

Select @@Versopm

I get...

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 3

Is there some kind of SQL injection protection going on? Whenever I try to post code the server just hangs. However if I just post text like this there is not a problem. Is there something I need to do to paste code?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Vertigo44 (11/22/2010)
Is there some kind of SQL injection protection going on? Whenever I try to post code the server just hangs. However if I just post text like this there is not a problem. Is there something I need to do to paste code?

Is your company running Websense? It screws me up all the time.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 5

Sorry guys, I found out we are running websense and was having trouble posting. Anyway you lost me at Linked Servers however from what i have read about it, it sounds like the way to go since I found out I have over 150+ servers running the sqlserver.exe service around the office. So if I were able to connect to a whole bunch at one time and run a distributed query againt all of the ones I am connected to it would save me loads of time. I am currently connecting to each server then running a query to determine the backup method,type,time since last,etc. I am going to try and figure out how to set up a Linked server config. Now I am running 2005 management studio on my pc if that make a difference?