（2.11）备份与还原--备份还原系统表解析

文中绝大部分文字与代码的引用参考自：https://www.cnblogs.com/xunziji/archive/2011/04/28/2031653.html

打个比喻简单理解成（媒体集为文件组，媒体簇为文件，备份集为数据表对象/即每一次一个库单独的一次备份即为一个备份集） 

1.介质集 (media set)
备份介质（磁带或磁盘文件）的有序集合，使用固定类型和数量的备份设备向其写入了一个或多个备份操作。

2.介质簇 (media family)
在介质集中的单个非镜像设备或一组镜像设备上创建的备份。

3.备份集 (backup set)
通过成功的备份操作添加到介质簇的备份内容。

 

 

注意：所有的表都在msdb下

1. backupfile

表中的每条记录都对应着数据库中的数据文件或日志文件的一次备份信息。表中的字段说明了备份时文件的属性。通俗的说，是备份文件的详细表。

--就是查询备份记录

主要字段：

backup_set_id：备份集文件的唯一标识号。一个备份集可能包含多个数据文件或日志文件。

filegroup_name ：数据库备份的文件组名称，文件组名可以在数据库配置中自定义

file_number ：所属备份集中的ID

backed_up_page_count：已备份的页数file_type：已备份文件的类型，D = 数据文件，L = 日志文件，F = 全文目录（全文索引），可为nullfile_size ：备份文件的长度，单位 字节

logical_name : 备份文件的逻辑名称

physical_drive：要备份文件的 物理驱动器或分区名称,如：d:\，表示 d 盘

physical_name：要备份文件的的物理位置state：备份时，文件所处的状态，0 = online ，1 = RESTORING，2 = RECOVERING，3 = RECOVERY PENDING，4 = SUSPECT...

file_guid ：文件的唯一标识符backup_size：备份大小

is_readonly：是否只读 ， 1= 文件为只读

is_present：1= 文件包含在备份集中

第一屏列：

第二屏列：

 

 

2. backupfilegroup

备份时，数据库中的每个文件组占一行。

主要字段：

backup_set_id：包含该文件组的备份集IDname ： 文件组名称

 

其实就是上面backupfile备份次数的文件组。

 

 

 

3. backupmediafamily

每个媒体簇在表中占一行。如果媒体簇驻留在镜像媒体集中，则对于媒体集中的每个镜像服务器，该媒体簇都具有一个单独的行。该表存储在 msdb 数据库中。媒体簇，即备份后的物理文件。

主要字段：

physical_device_name：备份文件的物理路径

 

4. backupmediaset

每个备份媒体集在表中占一行

结合本图与3中图可以看出，每个媒体集都只有一个媒体簇（可以理解为一个大整体），是因为每个媒体簇（一般可以理解为盘符）只有一个物理文件。（备份集：各个库的备份集合）

打个比喻简单理解成（媒体集为文件组，媒体簇为文件，备份集为数据表对象）

5. backupset

每个备份集在表中占一行。“备份集”包含来自单个成功备份操作的备份。RESTORE、RESTORE FILELISTONLY、RESTORE HEADERONLY 和 RESTORE VERIFYONLY 语句对指定的一个或多个备份设备上的媒体集中的单个备份集进行操作。
一个比较重要的表，能从该表中得到 备份集过期时间、备份集文件名、执行备份的用户名、主数据库版本号、备份开始时间、备份结束时间、备份大小、数据库名、备份的类型、数据库恢复模式

主要字段：

backup_set_id：标识备份集的唯一备份集标识号。标识，主键。

media_set_id：标识备份集所在媒体集的唯一媒体集标识号。

expiration_date：备份集过期的日期和时间。非常重要的一个属性，如果超过 expiration_date，并且磁盘控件不足，则新的备份文件将会覆盖过期的备份文件

name：备份集的名称

user_name：执行备份操作的用户的名称

software_major_version：Microsoft SQL Server 主版本号

backup_start_date：备份操作的开始日期和时间。

backup_finish_date：备份操作的结束日期和时间。

type：备份类型。D = 数据库、I = 差异数据库、L = 日志、F = 文件或文件组、G = 差异文件、P = 部分、Q = 差异部分

compatibility_level：数据库的兼容级别设置。

backup_size：备份集的大小（以字节为单位）　　

database_name：备份操作中涉及的数据库的名称

server_name：运行 SQL Server 备份操作的服务器的名称

is_password_protected：是否启用密码保护

recovery_model：恢复模式。FULL、BULK-LOGGED、SIMPLE

is_damaged：1 = 创建此备份时，检测到数据库损坏。已要求备份操作忽略错误，继续执行备份

compressed_backup_size ：磁盘上存储的备份的总字节数。

实战，查询出 DB 备份集的详细信息：

SELECT TOP 1000 [backup_set_id],a.[media_set_id],[expiration_date],[name],[user_name],[software_major_version],[backup_start_date],[backup_finish_date],[type],[compatibility_level], [backup_size],[database_name] ,[server_name], [is_password_protected],[recovery_model],[is_damaged] ,[begins_log_chain],[compressed_backup_size], b.physical_device_name

FROM [msdb].[dbo].[backupset] a,[msdb].[dbo].[backupmediafamily] b

where a.media_set_id=b.media_set_id order by backup_set_id desc

--结果如下图

 

（6）restorefile
每个已还原文件在表中占一行。 这包括按文件组名称间接还原的文件。
（7）restorefilegroup
每个已还原的文件组在表中占一行。
（8）restorehistory
每个还原操作在表中占一行。

 

6. 数据库备份还原历史记录

备份还原的记录都在msdb里。

【1】. 备份记录

SELECT 
     bs.backup_set_id,
     bs.database_name,
     bs.backup_start_date,
     bs.backup_finish_date,
     CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
     CAST(DATEDIFF(second, bs.backup_start_date,
     bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
     CASE bs.[type]
         WHEN 'D' THEN 'Full Backup'
         WHEN 'I' THEN 'Differential Backup'
         WHEN 'L' THEN 'TLog Backup'
         WHEN 'F' THEN 'File or filegroup'
         WHEN 'G' THEN 'Differential file'
         WHEN 'P' THEN 'Partial'
         WHEN 'Q' THEN 'Differential Partial'
     END AS BackupType,
     bmf.physical_device_name,
     CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
     CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
     bs.server_name,
     bs.recovery_model
 FROM msdb.dbo.backupset bs
 INNER JOIN msdb.dbo.backupmediafamily bmf 
 ON bs.media_set_id = bmf.media_set_id
 ORDER BY bs.server_name,bs.database_name,bs.backup_start_date;
GO

如果server_name是本机，那么备份是在本机生成的；

如果server_name是别的主机名，那么备份是被拿到本机做过数据库还原；

 

【2】. 还原纪录

SELECT 
     rs.[restore_history_id],
     rs.[restore_date],
     rs.[destination_database_name],
     bmf.physical_device_name,
     rs.[user_name],
     rs.[backup_set_id],
     CASE rs.[restore_type]
         WHEN 'D' THEN 'Database'
         WHEN 'I' THEN 'Differential'
         WHEN 'L' THEN 'Log'
         WHEN 'F' THEN 'File'
         WHEN 'G' THEN 'Filegroup'
         WHEN 'V' THEN 'Verifyonly'
     END AS RestoreType,
     rs.[replace],
     rs.[recovery],
     rs.[restart],
     rs.[stop_at],
     rs.[device_count],
     rs.[stop_at_mark_name],
     rs.[stop_before]
FROM [msdb].[dbo].[restorehistory] rs
INNER JOIN [msdb].[dbo].[backupset] bs
--on rs.backup_set_id = bs.media_set_id
ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
ON bs.media_set_id = bmf.media_set_id
GO

还原数据库的时候是会写backupset和backupmediafamily系统表的，用来记录还原所用到的备份文件信息。

转载于:https://www.cnblogs.com/gered/p/9183860.html