How do I find the backup information about a particular database ?

Some one asked me this question in the past and since it is being repeatedly asked in forums and to me personally , I decided to write this.

Having the backup information of a database is very important for a database administrator. In case of disaster, the DBA has to be aware when was the last time that database was backed up.  If a SQL Server is inherited from another Admin and the schedule of the backups and the backup types performed is not known it is easy to find by querying the backup related tables in MSDB.

One of those tables is msdb.dbo.backupset. It has the backup information related to all databases on that instance. information such as backup size, backup type and the start and end time of the backup to name a few.

The following script is one such script that gives the backup info of a specific database with the most recent one first.

The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.

Use the following Script.

DECLARE @DBNAME VARCHAR(50)

SET @DBNAME = 'LSWB'

SELECT CEILING(((backup_size / 1024)/1024)/1024)
Bkup_Size_In_GB,
CEILING(((backup_size / 1024)/1024)) Bkup_Size_In_MB,
[type] Bkup_Type,
backup_start_date StartTime,backup_finish_date
FROM msdb.dbo.backupset
WHERE DATABASE_NAME = @DBNAME
ORDER BY backup_start_date DESC

— Bru Medishetty