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
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_nameIn 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.
Source:
https://solutioncenter.apexsql.com/how-to-get-a-backup-sql-database-history/
