June 7, 2004 at 2:11 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/co
1234
June 22, 2004 at 5:37 pm
I thought this was a pretty good reminder of what we are and are not backing up. I did have to do some modifications in my case - we do transaction log backups and database level backups. Our database backups are done both to tape and to file (two separate backups). If I want to know when the actual last database backup was, this needs to be modified a little bit as follows:
Select a.name as DBName, cBackupType, cBackupLocation, dBackup from master.dbo.sysdatabases a
left join
(select database_name as DBName,
CASE WHEN type = 'L' Then 'Log'
WHEN type = 'D' THEN 'Database'
ELSE 'Other'
END as cBackupType,
CASE WHEN user_name = 'DOMAIN\TapeBackupAccount'
THEN 'Tape'
ELSE 'Disk'
END as cBackupLocation,
max(backup_finish_date) as dBackup
from msdb.dbo.backupset where backup_finish_date <= getdate()
group by database_name, type, user_name) B
on a.name = b.DBName
When I select from this, I'm able to filter out Disk/Tape and Log/Database backups. I didn't include differential backups because we don't use them at my company. This was very useful to find those databases which may have been backed up to disk for a transaction log backup, but no recent db backup. It was also useful to find those db's that have only been captured on tape recently.
Overall, this was a great reminder and gave me a little nudge to check on my own systems. Thanks for the article.
-Pete Schott
November 16, 2004 at 9:48 am
Pete Schott,
I agree with you. I got a number of emails to include Backup type in this script.
Below is the revised script to take care of this issue.
Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a
left join
( select database_name, Backup_Type = Case type when 'D' then 'Database'
When 'I' then 'Database Differential'
When 'L' then 'Log'
When 'F' then 'File or Filegroup'
Else
'Error'
End,
max(backup_finish_date) backup_date
from msdb.dbo.backupset where backup_finish_date <= getdate()
group by database_name,Type
)
B
on a.name=b.database_name
1234
January 14, 2005 at 8:21 am
I have problems with your script.
I cant find the the cmax and backup_date columns.
My SQL 2000 Database hasn't this objects.
I dont know what I shoud do.
I am not still a newbee but i'am new in this forum.
Kind Regards
Michael
January 14, 2005 at 9:25 am
Not sure about cMax - don't see that in any of the posts. If you look at the queries, the query posted contains a "derived table". This is a subquery that we can join against. (inside the parens). In that derived table, we use an alias to define backup_date (e.g. select getdate() backup_date or select getdate() as backup_date or select backup_date = getdate() )
That's probably where this is coming from. We use this because we don't want every date in the table, just the latest/max date.
January 15, 2005 at 6:15 pm
All,
Regarding CMAX function, this is actualy a typoerror... please read it MAX function.
1234
January 15, 2005 at 6:17 pm
please use this script
Select a.name,backup_type, Backup_Date from master.dbo.sysdatabases a
left join
( select database_name, Backup_Type = Case type when 'D' then 'Database'
When 'I' then 'Database Differential'
When 'L' then 'Log'
When 'F' then 'File or Filegroup'
Else
'Error'
End,
max(backup_finish_date) backup_date
from msdb.dbo.backupset where backup_finish_date <= getdate()
group by database_name,Type
)
B
on a.name=b.database_name
1234
June 9, 2005 at 3:52 am
Guess we can spot who doesn't run a case sensitive server...
June 9, 2005 at 5:55 am
Why does this (case sensitive server) matter?
June 9, 2005 at 8:56 am
Because if you are trying to access one of the system tables/columns in Proper Case, you will more than likely fail. Most (if not all) of them are in all lowercase. I think the one I saw right away was Backup_Type which should be backup_type in a case-sensitive server or it won't work.
Case sensitive collations require that the case you are using matches exactly to that on the device. In those cases Red red rEd.
-Pete
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply