德瑞克：SQL Server 學習筆記: 使用 ROLLBACK IMMEDIATE 砍掉指定資料庫上的全部連線，並搭配 NORECOVERY 選項執行「結尾記錄備份(Tail-log Backups)」

2012-03-05

使用 ROLLBACK IMMEDIATE 砍掉指定資料庫上的全部連線，並搭配 NORECOVERY 選項執行「結尾記錄備份(Tail-log Backups)」

適用環境：
SQL Server 2005 及更新的版本。

「結尾記錄備份(Tail-log Backups)」

在完整模式或大量記錄復原模式下，SQL Server 2005 及更新的版本多半會要求您備份記錄的結尾，以便擷取尚未備份的記錄檔記錄。

在還原作業之前對記錄結尾進行的記錄備份，就稱為「結尾記錄備份」。

範例程式碼如下：

範例資料庫：Northwind_Admin

-- BACKUP LOG，加入：WITH NORECOVERY
-- 加入：WITH NORECOVERY，資料庫狀態：Restoring , 正在還原...
BACKUP LOG Northwind_Admin
 TO DISK =N'C:\myAdmin\Device\myNWAdmin.BAK'
 WITH NORECOVERY;
GO

/*
若是有人在使用此資料庫時，將遭遇到以下的錯誤訊息

訊息 3101，層級 16，狀態 1，行 1
無法獲得獨佔存取權，因為資料庫正在使用中。
訊息 3013，層級 16，狀態 1，行 1
BACKUP LOG 正在異常結束。

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
*/

-- 砍掉資料庫 Northwind_Admin 上的全部連線後，並變更資料庫的屬性為：SET Single_USER
/*
ALTER DATABASE 使用的參數

(1) ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
指定在指定的秒數之後回復，或是立即回復。

(2)
當指定 SINGLE_USER 時，每次只有一個使用者可以連接到資料庫。所有其他的使用者連接都會中斷。
*/
ALTER DATABASE Northwind_Admin
 SET SINGLE_USER
 WITH ROLLBACK IMMEDIATE;
GO

/*
產生訊息：
正在回復非限定的交易。預估回復完成: 100%。
*/

-- BACKUP LOG，加入：WITH NORECOVERY
-- 加入：WITH NORECOVERY，資料庫狀態：Restoring , 正在還原...
BACKUP LOG Northwind_Admin
 TO DISK =N'C:\myAdmin\Device\myNWAdmin.BAK'
 WITH NORECOVERY
GO

-- 資料庫狀態：RESTORING，正在還原...；使用者存取設定：SINGLE_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
ORDER BY 2 DESC
GO

/*=========================================================*/

-- 回復為上線(ONLINE)狀態，WITH RECOVERY
RESTORE DATABASE Northwind_Admin
 WITH RECOVERY
GO
-- 當指定 MULTI_USER 時，可讓所有具有適當權限的使用者都連接到允許的資料庫。
ALTER DATABASE Northwind_Admin 
 SET  MULTI_USER 
GO

-- 資料庫狀態：ONLINE；使用者存取設定：MULTI_USER
SELECT name N'資料庫', state_desc N'資料庫狀態', user_access_desc N'使用者存取設定'
FROM sys.databases
ORDER BY 2 DESC
GO

-- 01_訊息3101_無法獲得獨佔存取權，因為資料庫正在使用中

-- 02_檢視資料庫的狀態，RESTORING_SINGLE_USER

-- 03_使用_WITH ROLLBACK IMMEDIATE

-- 04_檢視資料庫狀態_ONLINE_MULTI_USER

認識「結尾記錄備份(Tail-log Backups)」

在完整模式或大量記錄復原模式下，SQL Server 2005 及更新的版本多半會要求您備份記錄的結尾，以便擷取尚未備份的記錄檔記錄。

在還原作業之前對記錄結尾進行的記錄備份，就稱為「結尾記錄備份」。

SQL Server 2005 及更新的版本通常會要求您在開始還原資料庫之前進行結尾記錄備份。
結尾記錄備份可防止工作遺失，並保持記錄鏈結完整。

當您將資料庫復原到失敗點時，結尾記錄備份是復原計畫中重要的最後備份。
如果您無法備份記錄的結尾，則只能將資料庫復原到失敗前建立的最後一個備份的結尾。

並不是所有的還原實例都需要結尾記錄備份。

如果復原點已包含在較早的記錄備份中，或者您要移動或取代 (覆寫) 資料庫，就不需要有結尾記錄備份，而且不需要將它還原至最近備份之後的某個時間點。

此外，如果記錄檔損毀，而且無法建立結尾記錄備份，您也必須在不使用結尾記錄備份的情況下還原資料庫。
任何在最近一次記錄備份之後認可的交易都會遺失。

就像任何記錄備份一樣，結尾記錄備份是使用 BACKUP LOG 陳述式所進行的。

我們建議您在下列情況中進行結尾記錄備份：

情況 (1)
如果資料庫在線上，每當要對資料庫執行的下一個動作是還原作業時，請在開始還原順序前，先使用 WITH NORECOVERY 備份記錄結尾：

BACKUP LOG TO WITH NORECOVERY

附註：
為了避免錯誤，NORECOVERY 選項是必要的。

NORECOVERY 參數

每當您打算在資料庫上繼續還原作業時，請使用 NORECOVERY。
NORECOVERY 會讓資料庫進入還原狀態。這樣可以保證資料庫不會在結尾記錄備份之後變更。

它會備份記錄的結尾，並將資料庫保留在 RESTORING 狀態。
當進行容錯移轉，將工作交給次要資料庫時，或在 RESTORE 作業之前儲存記錄結尾時，NORECOVERY 非常有用。

若要執行略過記錄截斷的最大速率記錄備份，再使資料庫自動進入 RESTORING 狀態，請同時使用 NO_TRUNCATE 和 NORECOVERY 選項。

情況 (2)
如果資料庫離線而且未啟動。

嘗試進行結尾記錄備份。因為這段時間不會發生交易，使用 WITH NORECOVERY 是選擇性的。

如果資料庫受損，請使用 WITH CONTINUE_AFTER_ERROR 或 WITH NO_TRUNCATE。

BACKUP LOG TO [WITH { CONTINUE_AFTER_ERROR | NO_TRUNCATE }

重要事項：
我們建議您除非是在資料庫受損時，請避免使用 NO_TRUNCATE。

如果資料庫受損 (例如，資料庫未啟動)，則只有在記錄檔未損壞、資料庫處於支援結尾記錄備份的狀態，以及資料庫沒有包含任何大量記錄變更時，結尾記錄備份才會成功。

-- 05_彙總這些選項