Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which Databases are Being Backed Up - Another Look Expand / Collapse
Author
Message
Posted Monday, June 7, 2004 2:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 28, 2011 11:35 AM
Points: 75, Visits: 2
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/co


1234
Post #119679
Posted Tuesday, June 22, 2004 5:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:47 PM
Points: 837, Visits: 1,259
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



Post #122457
Posted Tuesday, November 16, 2004 9:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 28, 2011 11:35 AM
Points: 75, 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
Post #146513
Posted Friday, January 14, 2005 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 6, 2008 6:36 AM
Points: 39, 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

Post #155643
Posted Friday, January 14, 2005 9:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:47 PM
Points: 837, Visits: 1,259
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.



Post #155663
Posted Saturday, January 15, 2005 6:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 28, 2011 11:35 AM
Points: 75, Visits: 2

All,

 

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

 




1234
Post #155776
Posted Saturday, January 15, 2005 6:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 28, 2011 11:35 AM
Points: 75, 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
Post #155777
Posted Thursday, June 9, 2005 3:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:24 AM
Points: 1,060, Visits: 874
Guess we can spot who doesn't run a case sensitive server...


Post #188950
Posted Thursday, June 9, 2005 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 6, 2010 3:45 AM
Points: 3, Visits: 6
Why does this (case sensitive server) matter?


Post #188977
Posted Thursday, June 9, 2005 8:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 3:47 PM
Points: 837, Visits: 1,259
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



Post #189089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse