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 queries, as well as value added tools and features to group, sort, report and export this critical information

It is essential for each organisation to define the backup policy in their 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 kind of backups to 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] 

Query results from backup SQL database backupfile MSDB table

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

Query results from backup SQL database backupmediafamily MSDB table

In the above screenshot, we can have 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

Query results from backup SQL database backupset MSDB table

  • 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

Query results from and aggregation of MSDB tables with backup SQL database METADATA

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

Query results from backup SQL database MSDB tables to get most recent full backups

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:

Query results from backup SQL database showing databases with no 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 kind of environment 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.

Alternative solution

ApexSQL Backup is a tool to manage SQL Server database backups and restoration across all instances. We can quickly get the backup information for all the registered instances at a central place. We do not need to do any coding to get the required data.

We can get a detailed backup history for any database in a visual timeline format:

Viewing results from backup SQL database information of backup history

You get a different color for full, differential and the log backups. We can get to know the information using these color codes.

In the timeline, you can scroll the slider, and it shows the detail of the backup happened during that particular time. Let us say we want to get backup history for the SQLShackDemo database on 12th February between 10 AM to 10.30 AM.

Select the particular database and move the timeline slider for the date and time range. We can click on the particular timeline backup, and it points out to particular backup from the details page:

Viewing individual results from backup SQL database information of backup history with a time slider

We can hover the mouse on the timeline to get the start and end time of particular backup:

Viewing individual results from backup SQL database information of backup history with a start and end time

Let us consider a scenario where we want to group the database backups for a particular database based on the backup type. It is straightforward to do the grouping using the GUI mode. We can drag the particular column to the area ‘Drag a column header here group to that column’:

Viewing individual results from backup SQL database information grouped for a particular database

We want to group result based on the type column; therefore, drag the column to the group by column area. You get the following result in ApexSQL Backup. In the output, you can see we have result group by full, differential and transaction log backup:

Viewing individual results from backup SQL database information of backup history grouped by backup database type

We can expand the particular group to get information of particular backup type only. For example, in below screenshot, we expanded the transaction log backup group, and it shows the only backup related with the transaction log backup:

Viewing individual results from backup SQL database information of backup history by transaction log backups only

If you click on any backup in the timeline, it automatically expands the particular group and shows the backup information as shown below:

Selecting the backup SQL database node in the timeline to see full details

We can create multiple groups for the result set. For example, we want to sort the result on the type of the backup as well as the backup duration. This way we can get to know if any backup has taken a long time to complete for a particular type.

Drag the ‘Duration column as well in the group field area as shown below. You get the result based on the type and duration of the backup:

Viewing individual results from backup SQL database information of backup duration

If we are taking the regular backups using ApexSQL backup manually or with regular job schedule, we can see the details in the ‘Activities’ tab for all databases:

Viewing individual results from backup SQL database information of activities details

We can easily set the filters to get the required data. Below are the filters available in the activities tab.

  • Time Range filter
  • Backup status filter

Suppose we want to get the list of all failed backups in the last 7 days, therefore we can set the filter as shown below:

Viewing individual results from backup SQL database information of failed backups

In the backup status filter, remove the checkbox from all another status except ‘Failure’, and we can see failed backups in the last 7 days:

Seeing failed SQL Server database backups in last 7 days

Similarly, if we want last 7 days successful backup, change the backup type filter to ‘Success’, and you can see the following data:

Seeing successful SQL Server database backups in last 7 days

We can export this information from the ‘Export’ tab in the desired format types CSV, XML, HTML and PDF:

Exporting SQL Server database backup information

Choose the required format and provide the name of the exported file. In this example, I selected export format as ‘HTML’, and it is saved in the documents of particular logged in user in ApexSQL folder:

Exporting SQL Server database backup information to HTML

Save the file, and you get the successful export message. You also get the choice to open the exported report:

Exporting SQL Server database backup information to HTML

Click ‘yes’ to open the file, and it opens the file for you as shown below:

SQL Server database backup information to HTML report

Note: You can download ApexSQL Backup and get a free trial version of 14 days to be familiar with the tool.

Conclusion:

ApexSQL Backup is a useful tool to check the database backup history for the SQL Server databases. We can easily group, filter the result set and export in our desired format. Explore this tool to manage the database backups effectively.

 

February 26, 2019