sql还原数据库备份数据库_如何获取SQL数据库还原历史记录

sql还原数据库备份数据库

This article will review how to get information on your SQL database restore history, including the metadata in MSDB that can be queried, as well as value added tools and features to the group, sort, report and export this critical information.

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

DBA’s are responsible for maintaining the database backup and restoring activities. Suppose you have automated a process to restore the database from production instance to test instance. We might need to get the database history to get answers to the following questions.

DBA负责维护数据库备份和还原活动。 假设您已经自动化了一个从生产实例到测试实例还原数据库的过程。 我们可能需要获取数据库历史记录才能获得以下问题的答案。

• What is the last database restoration date?
  上次数据库还原的日期是什么？
• What are the details of the database restoration (Source backup file, backup file creation date)?
  数据库还原的详细信息（源备份文件，备份文件创建日期）是什么？
• Who performed the database restoration?
  谁执行了数据库还原？

You can get the database restoration information using the system tables in the MSDB database. Below are tables in the MSDB database for the database restoration history.

您可以使用MSDB数据库中的系统表来获取数据库还原信息。 下面是MSDB数据库中有关数据库还原历史记录的表。

恢复历史记录 (Restore history )

This table contains the row for each database restoration performed. In the following image, we can see the essential columns of this table:

该表包含每个执行的数据库还原的行。 在下图中，我们可以看到此表的基本列：

SELECT  
  [restore_date]
      ,[destination_database_name]
      ,[user_name]
      ,[backup_set_id]
      ,[restore_type]
      ,[replace]
      ,[recovery]
      ,[restart]
  FROM [msdb].[dbo].[restorehistory]

We get the following database restoration history in my environment.

在我的环境中，我们获得以下数据库还原历史记录。

restore_date: It shows the database restoration date.

restore_date ：显示数据库还原日期。

destination_database_name: We can get the destination database name using this column.

destination_database_name ：我们可以使用此列获取目标数据库名称。

user_name: it gives user name that performed the restoration for that particular database.

user_name ：它提供执行该特定数据库还原的用户名。

backup_set_id: We can join this column with backupset table to get information about the backup file.

backup_set_id ：我们可以将此列与backupset表连接起来以获取有关备份文件的信息。

restore_type: We can use this column to know the kind of database restoration performed on particular database.

restore_type ：我们可以使用此列来了解对特定数据库执行的数据库还原的类型。

• D – Database
  D –数据库
• I -Differential
  I-微分
• L – Log
  L –日志
• V – Verifyonly
  V –仅验证

replace: once we execute a database restore command, we set this option to replace the existing destination database.

replace：执行数据库还原命令后，将设置此选项以替换现有的目标数据库。

• 1 – Specified
  1 –指定
• 0 – Not specified
  0 –未指定

recovery: In the database restore query, we also specify the Recovery and Norecovery option to bring the database open for users or not.

recovery ：在数据库还原查询中，我们还指定了Recovery and Norecovery选项以使数据库对用户开放还是不对用户开放。

• 1 – RECOVERY
  1 –恢复
• 0 – NoRecovery
  0 –无恢复

Restart: It shows whether the restore operation specified the RESTART option or not.

重新启动：它显示 恢复操作是否指定了RESTART选项。

• 1-Specified
  1指定
• 0-Not specified
  0-未指定

恢复文件 (restorefile )

We get the row for each restored file. We can join this table with restorehistory table on the restore_history_id column as well.

我们为每个还原的文件获得一行。 我们也可以将此表与restore_history_id列上的restorehistory表联接 。

Destination_phys_name: It gives the name of the physical file with the complete path. You will get the detail of each physical file that was restored by the backup file.

Destination_phys_name：它提供具有完整路径的物理文件的名称。 您将获得备份文件还原的每个物理文件的详细信息。

restorefilegroup (restorefilegroup )

We can do filegroup restore as well in SQL Server. A FILEGROUP backup and restore allows restoring the objects related to specific filegroup only. It is useful mainly for huge databases in TB’s. Each database has Primary filegroup that contains primary data file MDF.

我们也可以在SQL Server中进行文件组还原。 FILEGROUP备份和还原仅允许还原与特定文件组相关的对象。 它主要用于TB中的大型数据库。 每个数据库都有包含主要数据文件MDF的“主要”文件组。

SELECT [restore_history_id]
      ,[filegroup_name]
  FROM [msdb].[dbo].[restorefilegroup]

[restore_history_id]: We can join this column with other MSDB tables to get more information.

[restore_history_id]：我们可以将此列与其他MSDB表结合起来以获取更多信息。

Filegroup_name: It is the name of the FILEGROUP on which restoration was performed.

Filegroup_name ：这是在其上执行还原的FILEGROUP的名称。

Let us fetch information from the MSDB using internal tables with the following query. In this query, we join the restrehistory and restorefile tables with the backup history information tables to get complete information.

让我们使用内部表通过以下查询从MSDB中获取信息。 在此查询中，我们将restrehistory和restorefile表与备份历史信息表结合在一起以获取完整的信息。

SELECT
 rh.destination_database_name AS [Database],
  CASE WHEN rh.restore_type = 'D' THEN 'Database'
  WHEN rh.restore_type = 'F' THEN 'File'
   WHEN rh.restore_type = 'I' THEN 'Differential'
  WHEN rh.restore_type = 'L' THEN 'Log'
    ELSE rh.restore_type 
 END AS [Restore Type],
 rh.restore_date AS [Restore Date],
 bmf.physical_device_name AS [Source], 
 rf.destination_phys_name AS [Restore File],
  rh.user_name AS [Restored By]
FROM msdb.dbo.restorehistory rh
 INNER JOIN msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
ORDER BY rh.restore_history_id DESC
GO

We get the information about the database restoration as per the following screenshot.

我们根据以下屏幕快照获取有关数据库还原的信息。

结论 (Conclusion)

In this article, we explored getting database restoration history. It is essential for the DBA to be familiar with the way of getting this information in a handy way.

在本文中，我们探讨了如何获取数据库还原历史记录。 对于DBA来说，熟悉便捷获取此信息的方式至关重要。

翻译自: https://www.sqlshack.com/how-to-get-a-sql-database-restore-history/

sql还原数据库备份数据库