一、概述

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 時會返回很多重要信息。

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 時會返回很多重要信息。

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。

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 選項，如下所示。這可以對所有備份類型進行。

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 選項，因此無需設置任何內容，但可以在恢復時在選項頁面上設置或更改。

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 選項恢復數據庫備份，請轉到選項頁面並選擇下面突出顯示的項目。

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 中不能更改。

2.2.4 REPLACE

RESTORE ... WITH REPLACE 選項允許您在進行還原時覆蓋現有數據庫。在某些情況下，當您嘗試進行還原時，您可能會收到一條錯誤消息，提示 “數據庫的日誌尾部 .. 尚未備份”。

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 進行還原，請執行以下操作，在還原的選項頁面上選擇 “覆蓋現有數據庫”。

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

5qZL9A

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\。

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

但是如果有批量操作我們會得到這個錯誤。

恢復操作將完成，但它會恢復整個事務日誌備份並使數據庫處於 “正在恢復” 狀態。然後，您可以恢復其他事務日誌或使用 RESTORE .. WITH RECOVERY 選項使數據庫重新聯機。

2.2.7 獨佔數據庫訪問權限

還原數據庫時，您需要做的一件事是確保您擁有對數據庫的獨佔訪問權限。如果數據庫中有任何其他用戶，則還原將失敗。

獲得獨家訪問權

要獲得獨佔訪問權限，需要刪除所有其他連接或更改它們所在的數據庫，以便它們不使用您嘗試恢復的數據庫。您可以使用 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 的使用方法和常見選項，結合具體的例子，簡單明瞭，對於數據庫恢復原理和實戰有一個令人深刻的認識。

來源：

https://www.toutiao.com/article/7160978351744647716/?log_from=7dbe332ea610b_1667351999138

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/f8BAP5f6iBZDCE9sgLkrWw