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
MS Sql Server LazyDBA home page