4/14/2015

Script for identifying last backup taken

--NOT SQL 2000
--Last Full, Diff, and Log backup taken with recovery model 
--Need to trim down the history?

--use msdb;
--go
--sp_delete_backuphistory @oldest_date =


--Get the most recent Full backup taken 
SELECT T1.NAME                                           AS DatabaseName,
       Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model,
       'Full'                                            AS BackupType,
       Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date),
                                   131)),
       '')                                               AS LastBackUpTaken
FROM   sys.sysdatabases T1
       LEFT OUTER JOIN msdb.dbo.backupset T2
                    ON T2.database_name = T1.NAME
WHERE  type = 'D'
GROUP  BY T1.NAME
--Get the most recent Diff backup taken 
UNION ALL
SELECT T1.NAME                                           AS DatabaseName,
       Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model,
       'Differential'                                    AS BackupType,
       Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date),
                                   131)),
       '')                                               AS LastBackUpTaken
FROM   sys.sysdatabases T1
       LEFT OUTER JOIN msdb.dbo.backupset T2
                    ON T2.database_name = T1.NAME
WHERE  type = 'I'
GROUP  BY T1.NAME
--Get the most recent Log backup taken 
UNION ALL
SELECT T1.NAME                                           AS DatabaseName,
       Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model,
       'Log'                                             AS BackupType,
       Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date),
                                   131)),
       '')                                               AS LastBackUpTaken
FROM   sys.sysdatabases T1
       LEFT OUTER JOIN msdb.dbo.backupset T2
                    ON T2.database_name = T1.NAME
WHERE  type = 'L'
GROUP  BY T1.NAME
--Get the databases with no backup yet taken 
UNION ALL
SELECT T1.NAME                                           AS DatabaseName,
       Isnull(Max(T2.recovery_model), 'No Backup Taken') AS recovery_model,
       'No Backup'                                       AS BackupType,
       Isnull(CONVERT(VARCHAR(23), CONVERT(DATETIME, Max(T2.backup_finish_date),
                                   131)),
       '')                                               AS LastBackUpTaken
FROM   sys.sysdatabases T1
       LEFT OUTER JOIN msdb.dbo.backupset T2
                    ON T2.database_name = T1.NAME
WHERE  type IS NULL
GROUP  BY T1.NAME
--Sort the combined results
ORDER  BY T1.NAME,
          backuptype