SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Which Databases are Being Backed Up - Another Look


Which Databases are Being Backed Up - Another Look

Author
Message
santveer
santveer
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/co


1234
Peter Schott
Peter Schott
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2846 Visits: 1931
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
santveer
santveer
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 2

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
Michael Ziese-200679
Michael Ziese-200679
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 5

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


Peter Schott
Peter Schott
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2846 Visits: 1931
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.
santveer
santveer
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 2

All,

Regarding CMAX function, this is actualy a typoerror... please read it MAX function.




1234
santveer
santveer
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 2

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
RichB
RichB
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3068 Visits: 1067
Guess we can spot who doesn't run a case sensitive server...



kbirecki
kbirecki
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 6
Why does this (case sensitive server) matter?



Peter Schott
Peter Schott
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2846 Visits: 1931
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search