如何获取备份 SQL 数据库历史记录-526互联

本文将回顾如何获取有关备份 SQL 数据库历史记录的信息，包括 MSDB 中可查询的元数据，以及用于对这些关键信息进行分组、排序、报告和导出的增值工具和功能

每个组织都必须在其环境中定义备份策略。此类策略有助于以最小的数据丢失和最短的停机时间恢复数据库。SQL Server 在系统数据库 msdb 中维护备份历史记录。我们可能会采取不同类型的备份来达到最短恢复时间。一旦发生灾难，我们必须确切地知道如何从该内表中获取数据并制定相应的恢复计划。在本文中，我们将了解如何检索数据库备份历史记录以满足这些关键业务需求。

下面是 msdb 数据库中用于数据库备份的表。

备份文件

该表包含我们为其执行备份 SQL 数据库的数据库的每个数据或日志文件的行。在下图中，我们可以看到该表的基本列：

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：显示数据或日志文件的文件组名称。因此，我们没有任何日志文件的文件组；该列可以包含 NULL 值。

backed_up_page_count：显示备份的页数。

file_type：我们可以使用该字段获取文件类型。它可以具有以下值。

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

Logical_name：数据库文件的逻辑名称。

Physical_name：备份物理位置。

状态：给出特定文件的状态信息。我们可以为此设置以下值。

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

backup_size：它给出备份 SQL 数据库的大小（以字节为单位）。

Differential_base_lsn： SQL Server 对 LSN 大于 Differential_base_lsn 的盘区进行差异备份。

备份媒体家族

在此表中，我们获取有关逻辑和物理备份 SQL 数据库文件名以及进行备份的设备类型的信息：

select logical_device_name ,physical_device_name,device_type from backupmediafamily

在上面的截图中，我们可以有以下设备类型：

2 = 磁盘
5 = 胶带
7 = 虚拟设备
9 = Azure 存储
105 = 永久备份 SQL 数据库设备

备份集

在此表中，我们可以获取有关每个数据库的成功备份的信息：

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：备份SQL数据库集描述
user_name：我们可以从该列获取执行本次备份的用户
fist_lsn、last_lsn 和database_backup_lsn：我们从该列中获取备份LSN 信息。它可以帮助我们识别特定LSN之后的备份或为数据库准备恢复计划
backup_start_date 和 backup_finish_date：给出备份开始和结束时间
Server_name：显示服务器的实例名称
Machine_name：我们可以获取从中进行备份的机器的名称
type：我们可以使用此列值获取备份类型。这里我们有以下值
D：完整数据库备份。

L：日志备份

一：数据库差异备份

我们需要连接这多个表来获取相关的备份信息。例如，我们可以使用以下查询获取昨天的备份历史记录：

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

我们可能想要获取所有数据库的最新完整备份的列表：

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

同样重要的是确定是否存在我们没有进行任何备份的数据库。我们可以运行以下查询来获取此类数据库的列表：

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

在我的测试实例中，您可以看到许多数据库没有数据库备份：

备份 SQL 数据库的查询结果显示没有备份的数据库

每次根据需求获取备份信息确实变得困难。我们需要编写代码并相应地获取数据。我们可能有不同类型的环境，有不同的数据库需求，如果我们有一个大型数据库环境，那么获取这些数据就会变得困难。我们可能会向管理层报告备份失败或备份报告。在这种情况下，我们需要一个中心位置，从中我们可以获得实例中任何数据库的任何备份信息。

在 SQL Server 中，您可以使用中央管理服务器结合 SSIS、SSRS 工具来完成此操作，但是它需要对使用这些代码进行信息开发有很好的了解，但是如果需求发生变化，我们需要在以下位置进行更改：所有地方都体现出变化。