Checking when a database was last backed up

Category : Blogs Published : October 20, 2012 User Rating : 5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




It can be useful to periodically check when each database on a server was last backed up. The easiest way to do this on a single database is to right click on the database in SQL Server Management Studio (SSMS) and looking at the top of the Database Properties page (see the screenshot below).

However when there are several databases to check this can be quite labourious. SSMS actually uses the system table backupset to populate this part of the Properties page (you can verify this by running SQL Profiler just before opening the page).

Database Properties Window

I use a SQL script that uses this table along with backupmediafamily system table (to identify the file name of the backup) to query the latest backup of each type. The script query returns the most recent backup of each type, whether it's a full, transaction log, differential, filegroup or partial backup. Here is the script :
;WITH CTE_Backup AS
(
SELECT  database_name,backup_start_date,type,physical_device_name
       ,Row_Number() OVER(PARTITION BY database_name,BS.type
        ORDER BY backup_start_date DESC) AS RowNum

FROM    msdb..backupset BS
JOIN    msdb.dbo.backupmediafamily BMF
ON      BS.media_set_id=BMF.media_set_id
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name
AND         RowNum = 1
ORDER BY    D.name,type
As an aside the 'Last Database Backup' shown in SSMS does not seem to include filegroup or partial backups, only full, differential or log backups. I'm not sure why this should be.

Identifying Databases Which Haven’t Been Backed Up Recently

The above query can return a lot of data if your server has many databases, so I've modified it to produce a list of databases that have had no backups in the last 7 days (you might want to change this to a shorter period especially for production databases) :
;WITH CTE_Backup AS
(
SELECT   database_name,backup_start_date,type,is_readonly,physical_device_name
        ,Row_Number() OVER(PARTITION BY database_name
         ORDER BY backup_start_date DESC) AS RowNum

FROM     msdb..backupset BS
JOIN     msdb.dbo.backupmediafamily BMF
ON       BS.media_set_id=BMF.media_set_id
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name
AND         RowNum = 1
WHERE       ( backup_start_date IS NULL OR backup_start_date < DATEADD(dd,-7,GetDate()) )
ORDER BY    D.name,type
This will produce a list of databases for investigation, though of course there may be a good reason for a database not being backed up, for instance it's not possible to backup database snapshots. Also if the secondary database of a log shipping configuration there's not always a need to back it up.

Missing Transaction Log Backups

Finally I've modified the above query so that it reports all databases that have the full or bulk-logged recovery model and where there hasn't been a transaction log backup in the last day :
;WITH CTE_Backup AS
(
SELECT   database_name,backup_start_date,type,is_readonly,physical_device_name
        ,Row_Number() OVER(PARTITION BY database_name,BS.type
         ORDER BY backup_start_date DESC) AS RowNum

FROM     msdb..backupset BS
JOIN     msdb.dbo.backupmediafamily BMF
ON       BS.media_set_id=BMF.media_set_id
WHERE    type='L'
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No log backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name
AND         RowNum = 1
WHERE       ( backup_start_date IS NULL OR backup_start_date < DATEADD(dd,-1,GetDate()) )
AND         recovery_model_desc != 'SIMPLE'
ORDER BY    D.name,type
I hope the above queries are of use in identifying databases where there is no recent backup in place. You really don't want to be in a position where this is only discovered when the backup is actually needed, i.e. after a disk failure or data corruption. Of course you should also be checking that your backups are valid, ideally by periodically restoring from a backup, or at the very least by checking the backups using the VERIFYONLY option (though there is really no substitute for doing an actual restore).
Link back to this article : https://www.sqlmatters.com/Articles/Checking when a database was last backed up.aspx

Keywords

SQL,scripts,backup


Comments
Post by Kane on Wed 04 Jan 2017 10:08. Report Inappropriate Post

Thank you for a useful and concise set of scripts to check/verify backups
Post by jimi on Fri 01 Sep 2017 01:38. Report Inappropriate Post

how would you add to this script so it emails you the resutls when finished? thanks
Post by jimi on Fri 01 Sep 2017 04:17. Report Inappropriate Post

answered it myself, just added this at the top:

EXEC msdb.dbo.sp_send_dbmail
@recipients='xxxx@xxxx.com',
@subject='Databases with no backups for the last 2 days',
@profile_name = 'SysAdmin',
@attach_query_result_as_file = 0,
@query= '

with a closing ' at the end

might be handy for someone who isn't a DBA (such as myself)
Post by sdsdd on Tue 14 Jan 2020 23:29. Report Inappropriate Post

02436/k;lluo0080kkh 09999

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered