Retrieving SQL Server database backup records

Problem

There is a lot of data that needs to be dug inside the Microsoft SQL Server System view. This data is used to present information to the end users of SQL Server Management Studio and to Third-party management tools available to SQL Server professionals. This data not only contributes significantly to database backup information, file data statistical analysis, index information, or one of the thousands of metrics included in this example, but can be used for direct query and absorption into your home-grown detection solution. This article is intended to illustrate the first measurement: database backup information. Where is it? What's the structure of it? What kind of data can be mined?

Expert answers

The msdb system database is the primary repository for storing SQL agents, backups, service agents, Database Mail, log shipping, recovery, and maintenance plan metadata. We'll focus on some of the system views associated with database backups in this tip.

L Dbo.backupset: Provides information about the most granular details of the backup process.

L dbo.backupmediafamily: Provides metadata for physical backup files because they are related to the backup collection.

L Dbo.backupfile: System view provides the most fine-grained information for a physical backup file.

Based on these tables, we can create different query statements to gain a more detailed understanding of the database backup state in any given instance of SQL Server.

Database backup of all databases last week

-----------------------------------------------------------
--database Backups for all databases for Previous Week
-----------------------------------------------------------
SELECT
CONVERT (CHAR), serverproperty (' Servername ') as Server,
Msdb.dbo.backupset.database_name,
Msdb.dbo.backupset.backup_start_date,
Msdb.dbo.backupset.backup_finish_date,
Msdb.dbo.backupset.expiration_date,
Case msdb.. Backupset.type
When ' D ' THEN ' Database '
When ' L ' THEN ' Log '
End as Backup_type,
Msdb.dbo.backupset.backup_size,
Msdb.dbo.backupmediafamily.logical_device_name,
Msdb.dbo.backupmediafamily.physical_device_name,
Msdb.dbo.backupset.name as Backupset_name,
Msdb.dbo.backupset.description
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 ()-7)
ORDER BY
Msdb.dbo.backupset.database_name,
Msdb.dbo.backupset.backup_finish_date

Note: The output is divided into two screenshots, taking into account readability.