SQLSERVER 恢复命令restore总结

一、概述

SQLSERVER的备份与恢复命令：BACKUP和RESTORE是一对孪生兄弟，在前一篇文章中我们介绍了BACKUP命令及其选项的使用，

就像 BACKUP 命令一样，RESTORE 命令也有很多的选项，理解和掌握这些选项的含义是SQLSERVER成功恢复所必需的。

二、命令

2.1 RESTORE命令

2.1.1 RESTORE HEADERONLY

RESTORE HEADERONLY 选项允许您查看特定备份设备的所有备份的备份头信息。在大多数情况下，您创建的每个备份只有一个备份存储在物理文件中，因此您可能只会看到一个标题记录，但如果您在一个文件中有多个备份，您会看到每个备份的信息。

T-SQL

RESTORE HEADERONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

结果集如下所示，使用 HEADERONLY 时会返回很多重要信息。

image-20221101141451717

image-20221101141554284

SQL Server Management Studio

  • 右键单击数据库

  • 选择“恢复数据库...”

  • 选择“从设备：”，然后单击“...”

  • 单击“添加”并选择后备文件，在本例中为“C:\AdventureWorks.BAK”并单击“确定”

  • 再次点击“确定”可以看到备份文件的内容，下图可以看到这个文件有两个备份

恢复数据库

2.1.2 RESTORE LABELONLY

RESTORE LABELONLY允许您查看备份设备的备份介质信息。因此，如果备份设备（例如备份文件）有多个备份，您将只能获得一条记录，该记录会为您提供有关介质集的信息，例如用于创建备份的软件、介质的创建日期等。

T-SQL

RESTORE LABELONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

结果集如下所示，使用 LABELONLY 时会返回很多重要信息。

image-20221101142358573

 

2.1.3 RESTORE FILELISTONLY

RESTORE FILELISTONLY 选项允许您查看已备份文件的列表。因此，例如，如果您有完整备份，您将看到所有数据文件 (mdf) 和日志文件 (ldf)。此信息只能使用 T-SQL 返回，无法从 SQL Server Management Studio 获取此信息。RESTORE FILELISTONLY 选项可以简单列出备份中包含的文件，如果一个文件中有多个备份并且您没有指定“WITH FILE = X”，您将只能获得文件中第一个备份的信息。要获取文件编号，请使用 RESTORE HEADERONLY。

T-SQL

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 1
GO

结果集如下所示。这里有用的东西包括LogicalName 和PhysicalName。

image-20221101143209430

image-20221101143231162

2.1.4 RESTORE DATABASE

RESTORE DATABASE 选项允许您恢复完整、差异、文件或文件组备份。恢复数据库时需要对数据库进行独占访问，这意味着没有其他用户连接可以使用该数据库。可以使用 T-SQL 或使用 SQL Server Management Studio 完成 RESTORE DATABASE 选项。

T-SQL

恢复完整备份

这将使用指定文件恢复数据库。如果数据库已经存在，它将覆盖文件。如果数据库不存在，它将创建数据库并将文件恢复到备份中指定的相同位置。可以使用RESTORE FILELISTONLY检查原始位置。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
GO

恢复完整备份允许额外的恢复，例如差异或事务日志备份 (NORECOVERY)

NORECOVERY 选项在恢复完成后使数据库处于恢复状态。这允许您恢复其他文件以使数据库更新。默认情况下，此选项处于关闭状态。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO

恢复差异备份

要恢复差异备份，必须首先使用 NORECOVERY 选项进行完全恢复。然后才可以恢复差异。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

使用具有多个备份的备份文件进行还原

假设我们使用同一个备份文件 AdventureWorks.BAK 来存储完整备份和差异备份。我们可以使用RESTORE HEADERONLY来查看备份和备份文件中的位置。假设恢复标头只告诉我们在位置 1 我们有完整备份，在位置 2 我们有差异备份。恢复命令将是。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERY, FILE = 1
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH FILE = 2
GO

 

2.1.5 RESTORE LOG

RESTORE LOG 命令允许您恢复事务日志备份。这些选项包括恢复整个事务日志或恢复到某个时间点或某个事务标记。为了还原事务日志备份，数据库必须处于还原状态，这意味着首先使用 NORECOVERY 选项还原完整备份或完整备份和差异备份，以允许额外的备份，例如还原事务日志备份。

恢复事务日志时，您将需要对数据库的独占访问权限，这意味着没有其他用户连接可以使用该数据库。如果数据库处于恢复状态，这不是问题，因为没有人可以使用该数据库。

可以使用 T-SQL 或使用 SQL Server Management Studio 完成 RESTORE LOG 选项。

T-SQL

恢复事务日志备份

要恢复事务日志备份，数据库需要处于恢复状态。这意味着您必须恢复完整备份，可能还需要恢复差异备份。

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

还原多个事务日志文件 (NORECOVERY)

NORECOVERY 选项在还原完成后使数据库处于还原状态。这允许您恢复其他文件以使数据库更新。默认情况下，此选项处于关闭状态。例如需要恢复两个事务日志备份，第一个使用 NORECOVERY 而第二个语句不使用，这意味着在恢复完成后可以访问数据库。

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO

从同一个备份文件恢复多个事务日志备份

假设我们使用同一个备份文件 AdventureWorks.TRN 来写入我们所有的事务日志备份。这不是最佳做法，因为如果文件已损坏，则可能会损坏此文件中的所有备份。我们可以使用RESTORE HEADERONLY来查看备份和备份文件中的位置。假设恢复标头只告诉我们这个文件中有 3 个事务日志备份，我们想要恢复全部。

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3
GO

 

2.1.6 RESTORE VERIFYONLY

RESTORE VERIFYONLY 命令检查备份以确保它是完整的并且整个备份是可读的。不执行实际还原，而是读取文件以确保 SQL Server 在需要使用此备份进行还原时可以读取它。RESTORE VERIFYONLY 选项是备份完成后检查每个备份的不错选择。不幸的是，这需要额外的处理时间才能完成，但这是一个很好的实践。以下是您可以使用 T-SQL 和 SSMS 执行此操作的方法。

T-SQL

检查磁盘上的备份文件

RESTORE VERIFYONLY 命令将检查备份文件并返回文件是否有效的消息。如果它无效，这意味着该文件不能用于恢复，并且应该进行新的备份。需要注意的一点是，如果一个文件中有多个备份，它只检查第一个文件。

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO

检查磁盘上的备份文件是否有特定备份

此命令将检查此备份文件中的第二个备份。要检查备份中的内容，您可以使用RESTORE HEADERONLY并使用 Position 列指定 FILE 编号。

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2
GO

SQL Server Management Studio

使用维护计划或通过 SSMS 创建备份时，您可以选择打开 RESTORE VERIFYONLY 选项，如下所示。这可以对所有备份类型进行。

e2

e3

 

2.2 Restore 选项

除了我们前面已经讨论过的命令之外，Restore还有许多其他选项可以与这些命令一起使用。

  • RECOVERY

  • NORECOVERY

  • STATS

  • REPLACE

  • MOVE

  • STOPAT

2.2.1 RECOVERY

RESTORE ... WITH RECOVERY 选项将数据库置于可用状态，因此用户可以访问已恢复的数据库。 当您发出 RESTORE DATABASE 或 RESTORE LOG 命令时，默认使用 WITH RECOVERY 选项。无需指定此选项即可执行此操作。

如果您恢复“完整”备份，则默认设置为 RESTORE WITH RECOVERY，因此在数据库恢复后，您的最终用户可以使用它。

如果您正在使用多个备份文件还原数据库，您需要使用 WITH NORECOVERY 选项进行除最后一次之外的每个还原。

如果您的数据库仍处于恢复状态，并且您想在不恢复其他备份的情况下恢复它，您可以发出 RESTORE DATABASE .. WITH RECOVERY 使数据库联机供用户使用。

T-SQL

恢复处于 RECOVERY状态 如上所述，此选项是默认选项，但您可以按如下方式指定。此选项是默认选项，但您可以按如下方式指定。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY
GO

对上次备份使用 WITH RECOVERY 恢复多个备份 第一次恢复使用 NORECOVERY 选项，因此可以进行额外的恢复。第二个命令恢复事务日志，然后使数据库联机以供最终用户使用。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

SQL Server Management Studio

使用 SSMS 恢复时，默认使用 WITH RECOVERY 选项，因此无需设置任何内容，但可以在恢复时在选项页面上设置或更改。

e2

 

2.2.2 NORECOVERY

RESTORE ... WITH NORECOVERY 选项将数据库置于“正在恢复”状态，因此可以恢复其他备份。当数据库处于“正在恢复”状态时，没有用户可以访问数据库或数据库的内容。

当您发出 RESTORE DATABASE 或 RESTORE LOG 命令时，WITH NORECOVERY 选项允许您在恢复数据库之前恢复其他备份文件。因此，这允许您在让最终用户访问数据之前尽可能获取最新的数据库。

此选项默认不启用，因此如果您需要通过恢复多个备份文件来恢复数据库而忘记使用此选项，则必须重新开始备份过程。

最常见的示例是恢复“完整”备份和一个或多个“事务日志”备份。

T-SQL

恢复完整备份和一个事务日志备份 第一个命令执行恢复并使数据库处于恢复状态，第二个命令恢复事务日志备份，然后使数据库可用。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

还原完整备份和两个事务日志备份

首先使用 NORECOVERY 还原前两个备份，然后使用 RECOVERY 进行最后一次还原。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks2.TRN'
WITH RECOVERY
GO

还原完整备份、最新差异备份和两个事务日志备份

使用 NORECOVERY 还原前三个备份，然后使用 RECOVERY 进行最后还原。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks2.TRN'
WITH RECOVERY
GO

SQL Server Management Studio

要使用 WITH NORECOVERY 选项恢复数据库备份，请转到选项页面并选择下面突出显示的项目。

e3

 

2.2.3 STATS

RESTORE WITH STATS 选项允许您查看还原过程的进度，这个选项也可用于 RESTORE DATABASE、RESTORE LOG、RESTORE VERIFYONLY。RESTORE WITH STATS 选项将让您了解当前还原过程的进度。此信息以完成百分比表示。默认是每 10% 后显示，或者可以指定百分比值。

T-SQL

使用默认统计设置恢复完整数据库 以下将显示每个 10% 段后的完成百分比。

RESTORE DATABASE 'AdventureWorks' FROM DISK = 'C:\AdventureWorks.BAK'
GO

恢复完整的数据库，每完成 1% 就会显示统计信息 这将在每完成 1% 后显示进度。

RESTORE DATABASE 'AdventureWorks' FROM DISK = 'C:\AdventureWorks.BAK' WITH STATS = 1
GO

SQL Server Management Studio

使用 SSMS 还原数据库时，此信息将显示在下面突出显示的部分中。默认值为 10%， GUI 中不能更改。

e4

2.2.4 REPLACE

RESTORE ... WITH REPLACE 选项允许您在进行还原时覆盖现有数据库。在某些情况下，当您尝试进行还原时，您可能会收到一条错误消息，提示“数据库的日志尾部 .. 尚未备份”。

image-20221101170245234

RESTORE ... WITH REPLACE 允许您在执行还原时覆盖现有数据库，而无需先备份事务日志的尾部。WITH REPLACE 基本上告诉 SQL Server 丢弃事务日志中的所有活动内容并继续进行还原。

T-SQL

使用 WITH REPLACE 恢复完整备份 下面的命令将恢复数据库并忽略当前事务日志中的任何活动数据。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH REPLACE
GO

SQL Server Management Studio

要使用 SSMS 进行还原，请执行以下操作，在还原的选项页面上选择“覆盖现有数据库”。

e5

 

 

2.2.5 MOVE

RESTORE ... WITH MOVE 选项允许您恢复数据库，但也可以指定数据库文件（mdf 和 ldf）的新位置。如果您要从该数据库的备份还原现有数据库，则不需要这样做，但如果您要从具有不同文件位置的不同实例还原数据库，则可能需要使用此选项。

RESTORE ... WITH MOVE 选项将让您确定数据库文件的名称以及创建这些文件的位置。在使用此选项之前，您需要知道这些文件的逻辑名称以及 SQL Server 的位置。

如果已经存在另一个使用您尝试还原的相同文件名的数据库并且该数据库处于联机状态，则还原将失败。但是如果数据库由于某种原因不在线并且文件没有打开，如果你不使用 WITH MOVE 选项，恢复将覆盖这些文件，所以要小心你不要意外覆盖好的数据库文件。

此外，当使用 WITH MOVE 选项时，您需要确保用于 SQL Server 引擎的帐户有权在您指定的文件夹中创建这些文件。

T-SQL

1、确定备份的内容

您需要做的第一件事是确定文件的逻辑名称和物理位置。这可以通过使用RESTORE FILELISTONLY命令来完成。这将为您提供逻辑名称和物理名称。

例如：

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO
ColumnName Value - Row 1Value - Row2
LogicalName AdventureWorks_Data AdventureWorks_Log
PhysicalName C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf
Type D L

2、使用 MOVE 恢复完整备份

假设我们要恢复这个数据库，但我们希望将数据文件放在“G:\SQLData”文件夹中，将事务日志文件放在“H:\SQLLog”文件夹中。该命令如下所示：

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf'
GO

还原完整和事务日志备份 WITH MOVE 只需为第一次还原指定 WITH MOVE，因为此后数据库将处于“还原”状态。第二次还原只会将内容写入正在使用的这个新位置。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLLog\AdventureWorks_Log.ldf',
NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

SQL Server Management Studio

要使用 SSMS 进行还原，请执行以下操作，在还原的选项页面上，更改每个文件的“还原为：”值，如下所示。下面将还原到根文件夹，但您可以根据需要将它们更改为 G:\SQLData\ 和 H:\SQLLog\。

e6

 

2.2.6 STOPAT

RESTORE ... WITH STOPAT 选项允许您将数据库恢复到某个时间点。为了使此选项起作用，数据库需要处于 FULL 或 Bulk-Logged 恢复模式，并且您需要进行事务日志备份。

当数据写入数据库时，它首先写入事务日志，然后在事务完成后写入数据文件。还原事务日志时，SQL Server 将重播事务日志中的所有事务，并在将数据库置于可用状态之前前滚或回滚它需要的事务。

这些事务每一个都有一个 LSN（逻辑序列号）和一个时间戳，因此在还原事务日志时，您可以告诉 SQL Server 在哪里停止读取需要还原的事务。

需要注意的一点是，如果您的数据库使用 Bulk-Logged 恢复模型，并且事务日志中存在最小记录操作（例如批量插入），则您无法使用该事务日志进行时间点恢复。

T-SQL

使用 STOPAT 还原数据库 这会将 AdventureWorks 数据库还原到等于“2022 年 10 月 23 日下午 5:31”的时间点。

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH NORECOVERY
GO 
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' 
WITH RECOVERY, 
STOPAT = 'Oct 23, 2022 05:31:00 PM' 
GO

使用 STOPAT 恢复数据库，其中恢复模式为 Bulk-Logged 并且存在最低限度记录的操作

在此示例中，我们有一个完整备份并且事务日志具有最低限度记录的操作。我们可以尝试使用以下命令进行时间点恢复：

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
WITH NORECOVERY
GO 
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' 
WITH RECOVERY, 
STOPAT = 'Oct 23, 2022 05:31:00 PM' 
GO

但是如果有批量操作我们会得到这个错误。

image-20221101172350574

恢复操作将完成，但它会恢复整个事务日志备份并使数据库处于“正在恢复”状态。然后，您可以恢复其他事务日志或使用RESTORE .. WITH RECOVERY选项使数据库重新联机。

 

2.2.7 独占数据库访问权限

还原数据库时，您需要做的一件事是确保您拥有对数据库的独占访问权限。如果数据库中有任何其他用户，则还原将失败。

image-20221101172610142

获得独家访问权

要获得独占访问权限，需要删除所有其他连接或更改它们所在的数据库，以便它们不使用您尝试恢复的数据库。您可以使用sp_who2或SSMS查看哪些连接正在使用您尝试恢复的数据库。

使用 KILL 获得独占访问权的一个选项是使用 KILL 命令来终止正在使用数据库的每个连接。但要注意您要终止的连接以及可能需要发生的回滚问题。

使用 ALTER DATABASE 另一种选择是将数据库置于单用户模式，然后进行恢复。这也会根据您使用的选项进行回滚，但会一次完成所有连接。 

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' 
GO

三、小结

本文详解了SQLSERVER数据库恢复命令restore的使用方法和常见选项，结合具体的例子，简单明了，对于数据库恢复原理和实战有一个令人深刻的认识。

posted @ 2022-11-01 17:32  雪竹子  阅读(4684)  评论(1)  编辑  收藏  举报