How to get a backup SQL database history

This article will review how to get information on your backup SQL database history, including the metadata in MSDB that can be queried, as well as value-added tools and features to group, sort, report, and export this critical information

It is essential for each organization to define the backup policy in its environment. Such policies help to recover the database with minimum loss of the data and minimum downtime. SQL Server maintains a backup history in the system database msdb. We might be taking different kinds of backups to the main minimum restoration time. In the case of any disaster, we must know exactly how to get the data from this internal table and prepare the restoration plan accordingly. In this article, we will understand the way to retrieve the database backup history to meet these critical business requirements.

Below are tables in the msdb database for the database backups.

Backupfile

This table contains the row for each data or log file of the database for which we executed the backup SQL database. In the following image, we can see essential columns of this table:

SELECT [filegroup_name]
      ,[backed_up_page_count]
      ,[file_type]
      ,[file_size]
      ,[logical_name]
      ,[physical_name]
  ,State
      ,[state_desc]
      ,[backup_size]
  ,[differential_base_lsn]
 FROM [msdb].[dbo].[backupfile] 

filegroup_name: It shows the filegroup name of the data or log file. We do not have any filegroup for the log file therefore; this column can contain NULL value.

backed_up_page_count: It shows the page count backed up.

file_type: We can get the file type using this field. It can have following values.

• D: SQL Server data file
• L: SQL Server log file
• F: Full-text catalog
• S: Memory optimised file

Logical_name: Logical name of the database file.

Physical_name: backup physical location.

State: It gives the state information of particular file. We can have following values for this.

• 0 = ONLINE
• 1 = RESTORING
• 2 = RECOVERING
• 3 = RECOVERY PENDING
• 4 = SUSPECT
• 6 = OFFLINE
• 7 = DEFUNCT
• 8 = DROPPED

backup_size: It gives the backup SQL database size in bytes.

differential_base_lsn: SQL Server takes a differential backup for the extents having LSN greater than differential_base_lsn.

backupmediafamily

In this table, we get the information about the logical and physical backup SQL database file name along with the device type in which backup was taken:

select logical_device_name ,physical_device_name,device_type from backupmediafamily

In the above screenshot, we can have the following device types:

• 2 = Disk
• 5 = Tape
• 7 = Virtual device
• 9 = Azure Storage
• 105 = A permanent backup SQL database device

Backupset

In this table, we can get information about the successful backup for each database:

select name,user_name,first_lsn,last_lsn,database_backup_lsn,backup_start_date,backup_finish_date,
  type,database_name,server_name,machine_name from backupset

• Name: backup SQL database set description
• User_name: We can get a user who executed this backup from this column
• Fist_lsn,last_lsn and database_backup_lsn: We get the backup LSN information from this column. It can help us to identify the backups after a particular LSN or preparing a restoration plan for a database
• Backup_start_date and backup_finish_date: It gives the backup start and ends time
• Server_name: It shows the instance name of the server
• Machine_name: we can get the name of the machine from which we took the backup
• Type: We can get the backup type using this column value. Here we have the following values

  D: Full database backup.

  L: log backup

  I: Differential database backup

We need to join these multiple tables to get the relevant backup information. For example, we can get the backup history from yesterday using the below query:

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
When 'I' THEN 'Differential database'
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 ) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc

We might want to get a list of the most recent full backup of all the databases:

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name

It is also equally important to identify if there are any databases for which we are not taking any backups. We can run the below query to get a list of such databases:

SELECT      
      S.NAME AS database_name,  
      'Nobackups' AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
       ON S.name  = B.database_name 
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY  
   B.database_name

In my test instance, you can see many databases having no database backups:

It does become difficult to get the backup information each time based on the requirement. We need to write the code and get the data accordingly. We might be having different kinds of environments having different database needs, and if we have a large database environment, it becomes difficult to get this data. We might report backup failures or backup reports to the management. In such a case, we need a central place from which we can get any backup information for any database in the instance.

In SQL Server, you can do it using the central management server with the combination of SSIS, SSRS tools however it requires a good level of knowledge of the development of the information using these code but again if requirement changes, we need to make changes at all places to reflect the change.