SQLSERVER數據庫置疑、可疑、脫機、單用戶、緊急模式等的修復

嘗試數據庫被標記為RESTORING的處理方式

嘗試數據庫被標記為RESTORING的處理方式

/****

by select left('claro',2),in 西安 ,2009-03-27 00:29:23.450

Microsoft SQL Server 2005 - 9.00.1406.00

(Intel X86)

Mar 3 2007 18:40:02

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

****/

USE [master]

GO

-->1. 查詢數據庫狀態

select state_desc , name ,user_access_desc from sys.databases where name = 'db'

--state_desc name user_access_desc

--RESTORING Db MULTI_USER

-->2. 修改數據庫狀態

ALTER DATABASE [Db] SET ONLINE

-- 消息 5052 ，級別 16 ，狀態 1 ，第 1 行

-- 當數據庫處於 Restoring 狀態時，不允許 ALTER DATABASE 。

-- 消息 5069 ，級別 16 ，狀態 1 ，第 1 行

--ALTER DATABASE 語句失敗。

-->3. 設置數據庫狀態為緊急恢復模式

ALTER DATABASE [Db] SET EMERGENCY

-- 消息 5052 ，級別
16 ，狀態 1 ，第 1 行

-- 當數據庫處於 Restoring 狀態時，不允許 ALTER DATABASE 。

-- 消息 5069 ，級別 16 ，狀態 1 ，第 1 行

--ALTER DATABASE 語句失敗。

-->4. 設置數據庫狀態為單用戶模式

ALTER DATABASE [Db] SET SINGLE_USER

-- 消息 5052 ，級別 16 ，狀態 1 ，第 1 行

-- 當數據庫處於 Restoring 狀態時，不允許 ALTER DATABASE 。

-- 消息
5069 ，級別 16 ，狀態 1 ，第 1 行

--ALTER DATABASE 語句失敗。

-->5.CHECKDB 檢查

DBCC CHECKDB (db ,REPAIR_ALLOW_DATA_LOSS )

-- 消息 927 ，級別 14 ，狀態 2 ，第 1 行

-- 無法打開數據庫 'Db' 。該數據庫正在進行還原。

-->6. 嘗試用備份集繼續還原

RESTORE DATABASE [Db] FROM DISK = N 'c:/db.bak' WITH FILE = 1 , NOUNLOAD , REPLACE ,
STATS = 10

GO

-- 已處理百分之 13 。

-- 已處理百分之 22 。

-- 已處理百分之 31 。

-- 已處理百分之 40 。

-- 已處理百分之 54 。

-- 已處理百分之 63 。

-- 已處理百分之 72 。

-- 已處理百分之 81 。

-- 已處理百分之 90 。

-- 已處理百分之 100 。

-- 已為數據庫 'Db' ，文件 'Db' ( 位於文件 1 上 )
處理了 176 頁。

-- 已為數據庫 'Db' ，文件 'Db_log' ( 位於文件 1 上 ) 處理了 2 頁。

--RESTORE DATABASE 成功處理了 178 頁，花費 0.159 秒 (9.125 MB/ 秒 ) 。

-->7. 檢查數據庫狀態

select state_desc , name ,user_access_desc from sys.databases where name = 'db'

--state_desc name user_access_desc

--ONLINE Db MULTI_USER

-- 至此 db
數據庫正常。後續將嘗試數據庫處於置疑等狀態時，數據庫的修復方式。

數據庫一直顯示為正在還原（restoring)狀態--處理

restore database YourDB with recovery
--切換至正常狀態

完整的資料庫還原 (完整復原模式) Complete Database Restores (Full Recovery Model)

在完整資料庫還原中，目標是還原整個資料庫。 In a complete database restore, the goal is to restore the whole database. 在還原期間，整個資料庫為離線狀態。 The whole database is offline for the duration of the restore. 在讓資料庫的任何部分上線之前，所有的資料都必須復原到一致的位置；此時資料庫的所有部分都會回到相同的時間點，而且沒有未認可的交易存在。 Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.

在完整復原模式下，還原一個或多個資料備份之後，您必須先還原所有後續的交易記錄備份，然後再復原資料庫。
Under the full recovery model, after you restore your data backup or backups, you must restore all subsequent transaction log backups and then recover the database. 您可以將資料庫還原到其中一個記錄備份內的特定 「復原點」 (Recovery point)。 You can restore a database to a specific recovery point within one of these log backups. 復原點可以是特定日期和時間、標示的交易或記錄序號 (LSN)。 The recovery point can be a specific date and time, a marked transaction, or a log sequence number (LSN).

還原資料庫時 (特別是在完整復原模式或大量記錄復原模式下)，應該使用單一還原順序。 When restoring a database, particularly under the full recovery model or bulk-logged recovery model, you should use a single restore sequence. 「還原順序」 (Restore sequence) 包含一個或多個還原作業，會在一個或多個還原階段中移動資料。 A restore sequence
consists of one or more restore operations that move data through one or more of the phases of restore.

本主題內容： In this Topic:

• 
  將資料庫還原到失敗點 Restoring a Database to the Point of Failure

• 將資料庫還原到記錄備份內的時間點 Restoring a Database to a Point Within a Log Backup

• 相關工作 Related Tasks

將資料庫還原到失敗點 Restoring a Database to the Point of Failure

一般而言，將資料庫復原至失敗點的作業，包含下列基本步驟：
Typically, recovering a database to the point of failure involves the following basic steps:

1. 備份使用中的交易記錄檔 (即所謂的記錄檔結尾)。 Back up the active transaction log (known as the tail of the log). 這會建立結尾記錄備份。 This creates a tail-log backup. 如果沒有使用中的交易記錄，就會遺失該部分記錄中的所有交易。 If the active transaction log is unavailable, all transactions in that part of the log are lost.

   重要

   在大量記錄復原模式下，備份包含大量記錄作業的記錄時，需要存取資料庫中的所有資料檔案。 Under the bulk-logged recovery model, backing up any log that contains bulk-logged operations requires access to all data files in the database. 如果無法存取資料檔案，就無法備份交易記錄。 If the data files cannot be accessed, the transaction log cannot be backed up.
   在這種情況下，您必須手動重做最近一次備份記錄之後進行的所有變更。 In that case, you have to manually redo all changes that were made since the most recent log backup.

   如需詳細資訊，請參閱 結尾記錄備份 (SQL Server)。 For more information, see Tail-Log Backups (SQL Server).

2. 還原最新的完整資料庫備份，但不復原資料庫 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。 Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name
   FROM backup_device WITH NORECOVERY).

3. 如果有差異備份存在，請還原最新的備份，但是不要復原資料庫 (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY)。 If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).

   還原最近一次的差異備份，可減少必須還原的記錄備份數目。 Restoring the most recent differential backup reduces the number of log backups that must be restored.

4. 從剛才還原的備份之後所建立的第一個交易記錄備份開始，依順序以 NORECOVERY 還原記錄。 Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.

5. 復原資料庫 (RESTORE DATABASE database_name
   WITH RECOVERY)。 Recover the database (RESTORE DATABASE database_name WITH RECOVERY). 或者，這個步驟也可以合併在還原最後的記錄備份。 Alternatively, this step can be combined with restoring the last log backup.

   下圖顯示此還原順序。 The following illustration shows this restore sequence. 在失敗發生之後 (1)，會建立結尾記錄備份 (2)。 After a failure occurs (1), a tail-log backup is created (2). 接下來，資料庫會還原至失敗點。 Next, the database is restored to the point of the failure. 這個程序牽涉到還原資料庫備份、後續的差異備份，以及在差異備份之後所進行的每一個記錄備份，包括結尾記錄備份在內。 This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.

   
   

基本 Transact-SQL RESTORE 語法 Basic Transact-SQL RESTORE Syntax

上圖中還原順序的基本 RESTORE Transact-SQL Transact-SQL 語法如下： The basic RESTORE
Transact-SQL Transact-SQL syntax for the restore sequence in the preceding illustration is as follows:

1. RESTORE DATABASE <database> FROM full database backup WITH NORECOVERY; RESTORE DATABASE database FROM full database backup WITH NORECOVERY;

2. RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY; RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;

3. RESTORE LOG database FROM log_backup WITH NORECOVERY; RESTORE LOG database FROM log_backup WITH NORECOVERY;

   針對其他每個記錄備份重複此還原記錄步驟。
   Repeat this restore-log step for each additional log backup.

4. RESTORE DATABASE <database> WITH RECOVERY; RESTORE DATABASE database WITH RECOVERY;

範例：復原到失敗點 (Transact-SQL) Example: Recovering to the Point of Failure (Transact-SQL)

下列 Transact-SQL Transact-SQL 範例顯示將資料庫還原到失敗點之還原順序中的基本選項。 The following Transact-SQL Transact-SQL example shows the essential options in a restore sequence that restores the database to the point of failure. 此範例會建立資料庫的結尾記錄備份。 The example creates a tail-log backup of the database. 接下來，此範例會還原完整的資料庫備份和記錄備份，然後還原結尾記錄備份。
Next, the example restores a full database backup and log backup and then restores the tail-log backup. 此範例會在一個不同的最後步驟中復原資料庫。 The example recovers the database in a separate, final step.

USE master;
--Create tail-log backup.
BACKUP LOG AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH NORECOVERY;
GO
--Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=1,
NORECOVERY;
--Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=2,
NORECOVERY;
--Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=3,
NORECOVERY;
GO
--recover the database:
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
GO

將資料庫還原到記錄備份內的時間點 Restoring a Database to a Point Within a Log Backup

在完整復原模式下，完整資料庫還原通常可以復原到某個時間點、標示的交易或記錄備份內的 LSN。 Under the full recovery model, a complete database restore can usually be recovered to a point of time, a marked transaction, or an LSN within a log backup. 然而，在大量記錄復原模式下，如果記錄備份包含大量記錄的變更，則無法進行時間點復原。 However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible.

範例時間點還原案例 Sample Point-in-Time Restore Scenarios

下列範例假設有一個關鍵任務的資料庫系統，每天午夜時會建立一次完整資料庫備份，從星期一到星期六每小時整點時會建立一次差異資料庫備份，而全天每隔 10 分鐘會建立一次交易記錄備份。
The following example assumes a mission-critical database system for which a full database backup is created daily at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. 若要將資料庫還原至星期三 5:19 A.M. 時的狀態， To restore the database to the state is was in at 5:19 A.M. 請執行下列工作： Wednesday, do the following:

1. 還原星期二午夜建立的完整資料庫備份。 Restore the full database backup that was created Tuesday at midnight.

2. 還原星期三上午 5:00 建立的差異資料庫備份 Restore the differential database backup that was created at 5:00 A.M. 。 on Wednesday.

3. 
   套用星期三上午 5:10 建立的交易記錄備份， Apply the transaction log backup that was created at 5:10 A.M. 。 on Wednesday.

4. 套用星期三上午 5:20 建立的交易記錄備份， Apply the transaction log backup that was created 5:20 A.M. 並指定復原程序僅適用於上午 5:19 之前發生的交易。 on Wednesday, specifying that the recovery process applies only to transactions that occurred before 5:19 A.M.

   或者，如果需要將資料庫還原至星期四 3:04 A.M. 時的狀態， Alternatively, if the database needs to be restored to its state at 3:04 A.M. 但是無法使用星期四 3:00 A.M. 時建立的差異資料庫備份， Thursday, but the differential database backup that was created at 3:00 A.M. 則執行下列操作：
   Thursday is unavailable, do the following:

5. 還原星期三午夜建立的資料庫備份。 Restore the database backup that was created Wednesday at midnight.

6. 還原星期四上午 2:00 建立的差異資料庫備份 Restore the differential database backup that was created at 2:00 A.M. 建立的交易記錄備份。 on Thursday.

7. 套用星期四上午 2:10 到 Apply all the transaction log backups created from 2:10 A.M. 變成上午 3:00。 to 3:00 A.M. 建立的交易記錄備份。 on Thursday.

8. 套用星期四上午 3:10 建立的交易記錄備份， Apply the transaction log backup that was created at 3:10 A.M. 並在上午 3:04 停止復原程序。
   on Thursday, stopping the recovery process at 3:04 A.M.