________________________________

From: Mann Steve L. [mailto:mssqldba-ezmlmshield-x41580102.[Email address protected]
Sent: Mon 7/30/2007 2:25 PM
To: LazyDBA Discussion
Subject: RE: Getting the 2nd to max date for a table



Oops! In the last email, I included a database specific where clause
line. I've taken it out in this one: This should run in any msdb
database now.



SELECT bs1.database_name, max(bs1.backup_finish_date) as lastBU,
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM backupset bs3

WHERE
bs3.database_name=bs1.database_name)),
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM
backupset bs3
WHERE
bs3.database_name=bs1.database_name and

bs3.backup_finish_date <

(SELECT max(bs4.backup_finish_Date)
FROM backupset bs4
WHERE bs4.database_name=bs1.database_name)))
FROM backupset bs1
GROUP BY bs1.database_name



-----Original Message-----
From: Mann, Steve L.
Sent: Monday, July 30, 2007 10:53 AM
To: '[Email address protected]
Subject: Getting the 2nd to max date for a table

Hello all,

I wanted to get the last three backups and compare the intervals
between them. I was able to do this query on the MSDB database, but it
seems like there should be a MUCH simpler, easier way to do this.
Anyone have any ideas?

Here's my query (that works currently):

SELECT bs1.database_name, max(bs1.backup_finish_date) as lastBU,
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM backupset bs3

WHERE
bs3.database_name=bs1.database_name)),
(SELECT max(bs2.backup_finish_date)
FROM backupset bs2
WHERE bs2.database_name=bs1.database_name and
bs2.backup_finish_date < (SELECT
max(bs3.backup_finish_Date)
FROM
backupset bs3
WHERE
bs3.database_name=bs1.database_name and

bs3.backup_finish_date <

(SELECT max(bs4.backup_finish_Date)
FROM backupset bs4
WHERE bs4.database_name=bs1.database_name)))
FROM backupset bs1
WHERE bs1.database_name='Impact_Reporting'
GROUP BY bs1.database_name

Thanks!

Steve


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




MS Sql Server LazyDBA home page