How to Find Last Full Backup Time and Size for Database? – Interview Question of the Week #173

Question: How to Find Last Full Backup Time and Size for Database?

Answer: What makes me unhappy during any of my Comprehensive Database Performance Health Check is DBA and Developers often not taking backup of their database. It is really sad to see that individuals who care about their database performance would be so careless for their database backup (safety). Often DBAs get into action as soon as point them that they do not have a full backup. However, there are times when I have seen after pointing this out to DBAs just stay do not care about it.

How to Find Last Full Backup Time and Size for Database? - Interview Question of the Week #173 fullbackup-800x204

In any case, here is the script which when you run gives you two of the most important details about your database backup.

  • Backup Date
  • Backup Size
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
CAST(msdb.dbo.backupset.backup_size AS NUMERIC(35,2))/1048576.0 AS backup_size_MB
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.database_name

When you run the above query and do not see your database in this list, that means your database is not being backup at all. If you see your database in this list, I suggest you take look at the Backup Date and make sure that there is always a recent backup available for the database.

If you are using any such script, I would be happy to connect with you and know what script you run to check your database’s last full backup.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
How to Search Special Characters in Table Column? – Interview Question of the Week #172
Next Post
How to Generate Fibonacci Series for the First 1000 Values? – Interview Question of the Week #174

Related Posts

4 Comments. Leave new

  • If looking for the last backup would it be better to only get the last backup date using a CTE like this:

    With MostRecent
    As (Select
    msdb.dbo.backupset.database_name,
    Max(msdb.dbo.backupset.backup_finish_date) As max_backup_finish_date
    From
    msdb.dbo.backupset
    Group By
    msdb.dbo.backupset.database_name)
    Select
    Convert(char(100),Serverproperty(‘Servername’)) As Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_finish_date,
    Cast(msdb.dbo.backupset.backup_size As numeric(35,2)) / 1048576.0 As backup_size_MB
    From
    msdb.dbo.backupmediafamily
    Inner Join msdb.dbo.backupset
    On msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    Join MostRecent As mr
    On
    mr.database_name = backupset.database_name And
    mr.max_backup_finish_date = backupset.backup_finish_date
    Where msdb.dbo.backupset.type = ‘D’
    Order By
    msdb.dbo.backupset.database_name;

    Reply
  • Where we need to run this query?

    Reply
  • This query returns one row for each file in the backup set, for multiple backups of the same database. If what is wanted is one row for the most recent full backup of each database, that can be obtained using the following query adapted from https://dba.stackexchange.com/questions/150241/t-sql-query-for-date-of-last-full-backup-size-and-location :

    WITH LastBackUp AS
    (
    SELECT bs.database_name,
    bs.compressed_backup_size,
    bs.backup_size,
    bs.backup_start_date,
    bmf.physical_device_name,
    Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )
    FROM msdb.dbo.backupmediafamily bmf
    JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
    JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
    WHERE bs.[type] = ‘D’
    AND bs.is_copy_only = 0
    )
    SELECT
    database_name AS [Database],
    CAST(compressed_backup_size / 1073741824 AS DECIMAL(10, 4)) AS [CompressedBackupSizeGB],
    CAST(backup_size / 1073741824 AS DECIMAL(10, 4)) AS [BackupSizeGB],
    backup_start_date AS [Last Full DB Backup Date],
    physical_device_name AS [Backup File Location]
    FROM LastBackUp
    WHERE Position = 1
    ORDER BY [Database];

    Reply
  • how about if we want to know the last full back up only?

    Reply

Leave a Reply

Menu