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.
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.
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 following device types:
- 2 = Disk
- 5 = Tape
- 7 = Virtual device
- 9 = Azure Storage
- 105 = A permanent backup SQL database device
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 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.
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:
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:
We can hover the mouse on the timeline to get the start and end time of particular backup:
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’:
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:
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:
If you click on any backup in the timeline, it automatically expands the particular group and shows the backup information as shown below:
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:
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:
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:
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:
Similarly, if we want last 7 days successful backup, change the backup type filter to ‘Success’, and you can see the following data:
We can export this information from the ‘Export’ tab in the desired format types CSV, XML, HTML and PDF:
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:
Save the file, and you get the successful export message. You also get the choice to open the exported report:
Click ‘yes’ to open the file, and it opens the file for you as shown below:
Note: You can download ApexSQL Backup and get a free trial version of 14 days to be familiar with the tool.
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