3

I use this query to list last backup dates of our databases:

SELECT SDB.NAME AS DATABASENAME,
COALESCE(CONVERT(VARCHAR(12), MAX(BUS.BACKUP_FINISH_DATE), 101),'-') AS LASTBACKUPTIME
FROM SYS.SYSDATABASES SDB
LEFT OUTER JOIN MSDB.DBO.BACKUPSET BUS ON BUS.DATABASE_NAME=SDB.NAME
GROUP BY SDB.NAME
    ORDER BY 2 ASC

But today, it shows me "09/15/2015" for all databases, but we had a problem in the cluster, so the backup job didn't run. Why is this query listing "09/15/2015' if there are no backups for today?

enter image description here

last time the job run was 09/14/2015.


Edit 1:

With James Anderson's query, I got this too ( filtering with bckset.[type] ='D)

Again, I don't have backups from 2015-09-15, only 2015-09-14.


EDIT 2:

With some tests, I see that these queries shows me last backup date, even if I only have log backups. Is there a way to list "last full backup" date??

enter image description here

1
  • Any chance the msdb was restored from other server ? Can you check that just to rule that out ?
    – Kin Shah
    Sep 16 '15 at 13:23
2

Your problem may be that the backup set was created but the backup did not complete. I use the query below to check my backups

USE MSDB;
GO

SELECT TOP  300
            bckset.database_name AS DatabaseName,
            bckmdiaset.physical_device_name AS BackupLocation,
            CASE 
                WHEN bckset.backup_size <= '10485760' THEN
                    CAST(CAST(bckset.backup_size/1024 AS INT) AS VARCHAR(14)) + ' ' + 'KB'
                ELSE
                    CASE 
                        WHEN bckset.backup_size <= '1048576000' THEN
                            CAST(CAST(bckset.backup_size/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'MB'
                        ELSE
                            CAST(CAST(bckset.backup_size/1024/1024/1024 AS INT) AS VARCHAR(14)) + ' ' + 'GB'
                    END
            END backupSize,
            CAST (bckset.backup_start_date AS smalldatetime) AS StartTime,
            CAST (bckset.backup_finish_date AS smalldatetime)FinishTime,
            CASE 
                WHEN CAST(DATEDIFF(second, bckset.backup_start_date, bckset.backup_finish_date )AS VARCHAR (4)) <= 60 THEN
                    CAST(DATEDIFF(second, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Seconds'
                ELSE
                    CAST(DATEDIFF(minute, bckset.backup_start_date,bckset.backup_finish_date) AS VARCHAR(4))+ ' ' + 'Minutes'
            END AS TimeTaken,
            CAST(bckset.first_lsn AS VARCHAR(25)) AS FirstLogSequenceNumber,
            CAST(bckset.last_lsn AS VARCHAR(25)) AS LastLogSequenceNumber,
            CASE bckset.[type]
                WHEN 'D' THEN 'Full'
                WHEN 'I' THEN 'Differential database'
                WHEN 'L' THEN 'Transaction Log'
                WHEN 'F' THEN 'File or filegroup'
                WHEN 'G' THEN 'Differential file'
                WHEN 'P' THEN 'Partial'
                WHEN 'Q' THEN 'Differential partial'
            END AS BackupType,
            bckset.server_name As ServerName,
            bckset.recovery_model As RecoveryModel,
            CASE bckset.is_snapshot
                WHEN '0' THEN 'FALSE'
                WHEN '1' THEN 'TRUE'
            END AS IsSnapshot,
            CASE [compatibility_level]
                WHEN 60 THEN 'SQL Server 6.0'
                WHEN 65 THEN 'SQL Server 6.5'
                WHEN 70 THEN 'SQL Server 7.0'
                WHEN 80 THEN 'SQL Server 2000'
                WHEN 90 THEN 'SQL Server 2005'
                WHEN 100 THEN 'SQL Server 2008'
                WHEN 110 THEN 'SQL Server 2012'
                WHEN 120 THEN 'SQL Server 2014'
            END AS CompatibilityLevel

FROM        msdb.dbo.backupset bckset
INNER JOIN  msdb.dbo.backupmediafamily bckmdiaset ON bckset.media_set_id = bckmdiaset.media_set_id

---WHERE bckset.database_name = ''

ORDER BY    backup_start_date DESC, backup_finish_date
10
  • How can I list only FULL backups? where bckset.[type] ='D' is returning me wrong values.
    – Racer SQL
    Sep 16 '15 at 12:56
  • That should work. How are the values wrong? Sep 16 '15 at 13:04
  • It says again, that I have backups from 2015-09-15 15:51:00 But I don't have. I will update the picture in my question ok?
    – Racer SQL
    Sep 16 '15 at 13:09
  • Are you sure someone hasn't manually ran some backups after the incident with the cluster? Can you check the location stated in the 'BackupLocation' column to make sure backps are not there? Sep 16 '15 at 13:12
  • Did you check the locations? The server wouldn't record this information if a backup didn't happen. The backups aren't on the other node in the cluster are they? Sep 16 '15 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.