數(shù)據(jù)庫(kù)質(zhì)疑修復(fù)總結(jié) For SQL Server 2000/2005/2008/2008R2 – 微軟亞太區(qū)數(shù)據(jù)庫(kù)技術(shù)支持組官方博客

瓜瓜2uuq7332fe 2018-02-13

展開全文

1 前言

本文的步驟基于以下條件：

1. SQL Server可以啟動(dòng)。

2. 數(shù)據(jù)庫(kù)沒(méi)有做有效的備份。

3. 當(dāng)前用戶有Sysadmin權(quán)限。

數(shù)據(jù)庫(kù)質(zhì)疑的原因會(huì)有多種多樣，不同的suspect采用的步驟也會(huì)有所不同，以下的步驟不能適用所有的情況，但包括了一些基本的步驟。數(shù)據(jù)庫(kù)suspect是指數(shù)據(jù)庫(kù)內(nèi)部處于不一致的狀態(tài)，很有可能會(huì)有數(shù)據(jù)丟失。我們推薦您從“好的數(shù)據(jù)庫(kù)備份”恢復(fù)。我們這里所指的“好的數(shù)據(jù)庫(kù)備份”是指：

1. 在做數(shù)據(jù)庫(kù)備份之前，您檢查過(guò)DBCC CHECKDB沒(méi)有錯(cuò)誤。

2. 備份之后的數(shù)據(jù)庫(kù)沒(méi)有更改，或者更改可以忽略。

在做任何修復(fù)操作之前，請(qǐng)務(wù)必備份.mdf/.ndf以及.ldf文件。

2 SQL Server 2000修復(fù)步驟

如果沒(méi)有“好的數(shù)據(jù)庫(kù)備份”，我們不能保證沒(méi)有數(shù)據(jù)丟失。以下是恢復(fù)suspect數(shù)據(jù)庫(kù) （SQL Server 2000）的一般方法，供您參考。同時(shí)在操作下列步驟之前，您可以備份質(zhì)疑數(shù)據(jù)庫(kù)的MDF、NDF、LDF文件，以便以下步驟恢復(fù)失敗時(shí)能夠回滾到原來(lái)的狀態(tài)。

1. 在查詢分析器中，運(yùn)行如下命令將數(shù)據(jù)庫(kù)置于緊急模式。

Sp_configure 'allow update', 1

Reconfigure with override

Begin Tran

Update master..sysdatabases set status = 32768 where name ='<DatabaseName>’

Commit Tran

--此處<DatabaseName>需要替代成您出問(wèn)題的數(shù)據(jù)庫(kù)名字。

--以下同

Select * from sysdatabases

--檢查數(shù)據(jù)庫(kù)狀態(tài)是否已經(jīng)變成 32768

2. 重啟SQL Server服務(wù)。

3. 如果第二步中重啟服務(wù)，數(shù)據(jù)庫(kù)再次進(jìn)入suspect模式，請(qǐng)?jiān)O(shè)置數(shù)據(jù)庫(kù)緊急模式，使用BCP方式導(dǎo)出數(shù)據(jù)。

否則在查詢分析器中，重新連接到SQL Server，運(yùn)行如下命令重建日志。

特別注意： dbcc rebuild_log是內(nèi)部命令，請(qǐng)您閱讀附件中的DBCC Rebuild Run Command.doc。只有當(dāng)您同意了附件中的內(nèi)容，才可以運(yùn)行此命令。

dbcc rebuild_log('<DatabaseName>’,'<The file name and the path of the log file>')

--此處<The file name and the path of the log file>是新的日志文件的物理文件的絕對(duì)地址。

如果rebuild_log失敗，請(qǐng)運(yùn)行如下命令，

Use <DatabaseName>

如果數(shù)據(jù)庫(kù)在emergency 方式能夠進(jìn)入緊急模式，直接使用BCP方式導(dǎo)出數(shù)據(jù)，沒(méi)有其他方式來(lái)恢復(fù)數(shù)據(jù)庫(kù)。

如果不能進(jìn)入，則沒(méi)有其他方式恢復(fù)數(shù)據(jù)庫(kù)

如果數(shù)據(jù)庫(kù)在Rebuild_log之后恢復(fù)正常，進(jìn)入第四步。

4. 關(guān)掉查詢分析器，再次打開查詢分析器，運(yùn)行如下語(yǔ)句，查詢出問(wèn)題的數(shù)據(jù)庫(kù)的DBID

Select * from master.dbo.sysdatabases

5. 再運(yùn)行如下命令，檢查是否有人在使用當(dāng)前出問(wèn)題的數(shù)據(jù)庫(kù)，如果有的話，請(qǐng)將他們退出。

Select * from master.dbo.sysprocesses

6. 然后運(yùn)行如下命令，將數(shù)據(jù)庫(kù)置于單用戶使用模式。如果設(shè)置不生效，可以嘗試使用企業(yè)管理器->數(shù)據(jù)庫(kù)屬性-> 選項(xiàng)來(lái)直接設(shè)置．

exec sp_dboption N'<DatabaseName>', N'single', N'true'

7. 在查詢分析器中，運(yùn)行如下命令，檢查數(shù)據(jù)庫(kù)是否有損壞。

dbcc checkdb(‘<DatabaseName>')

8. 如果您運(yùn)行上述命令發(fā)現(xiàn)數(shù)據(jù)庫(kù)有錯(cuò)誤。此時(shí)我們需要根據(jù)錯(cuò)誤來(lái)處理。接下去有兩個(gè)方向。

方向一：

使用Repair_Allow_Data_Loss選項(xiàng)修復(fù)數(shù)據(jù)庫(kù)。

優(yōu)點(diǎn)：可能可以恢復(fù)盡量多的數(shù)據(jù)

缺點(diǎn)：

a) 不一定能夠?qū)⑷垮e(cuò)誤修復(fù)，還有可能越修越多。同時(shí)，需要大量時(shí)間，需要經(jīng)過(guò)多次執(zhí)行修復(fù)命令．十幾次，甚至數(shù)十次．修復(fù)時(shí)間不能預(yù)估．

b) 就算我們將所有錯(cuò)誤修復(fù)，我們也不能保證數(shù)據(jù)在應(yīng)用程序邏輯這一層次上的數(shù)據(jù)正確性，您需要找您的應(yīng)用程序提供商來(lái)檢查數(shù)據(jù)在程序邏輯層次是否正確。

dbcc checkdb ('<DB_name>', REPAIR_ALLOW_DATA_LOSS) go

--此命令可能需要運(yùn)行多次，才能完全修復(fù)。

方向二：

通過(guò)BCP，DTS，select into等方式將好的表，或者表中好的數(shù)據(jù)導(dǎo)出來(lái)。建議使用BCP的方法，這樣可以最大限度的回復(fù)數(shù)據(jù)．BCP會(huì)停在出錯(cuò)的紀(jì)錄上，但是前面的數(shù)據(jù)就能成功導(dǎo)出．使用DTS或Select into的話, 我們很難判斷最大限度能導(dǎo)出的記錄數(shù)．

優(yōu)點(diǎn)：導(dǎo)出來(lái)的數(shù)據(jù)保證在應(yīng)用程序邏輯這一層次的正確性

缺點(diǎn)：不會(huì)修復(fù)數(shù)據(jù)庫(kù)中存在的錯(cuò)誤，丟失的數(shù)據(jù)量會(huì)比較大，取決于第7步的運(yùn)行結(jié)果。

9. 數(shù)據(jù)庫(kù)完全恢復(fù)正常之后，將數(shù)據(jù)庫(kù)置于正常狀態(tài)，并將單用戶模式改成多用戶模式。(16或者0)

begin tran

update sysdatabases set status = 8 where name = 'db_name'

commit tran

sp_configure 'allow', 0

reconfigure with override

exec sp_dboption N'<DatabaseName>', N'single', N'false'

另外，在上述步驟之前或者之中，可能會(huì)有數(shù)據(jù)庫(kù)在企業(yè)管理器中看不到，并且無(wú)法attach的狀況。通?？梢越ㄍ麛?shù)據(jù)庫(kù)（注意：物理文件名也要一致），停止SQL Server，覆蓋文件，重啟SQLServer的方式來(lái)解決。

3 SQL Server 2005/2008/2008R2修復(fù)步驟

方法一

alter database <DatabaseName>set emergency

alter database <DatabaseName>set single_user with rollback immediate

use <DatabaseName>

dbcc checkdb

use master

alter database <DatabaseName>Rebuild Log on

(name=<DatabaseName>_log,filename='c:\sql\logs\<DatabaseName>_log.LDF')

select * from sys.databases

dbcc checkdb('<DatabaseName>', repair_allow_data_loss)

sp_dboption '<DatabaseName>','single user','false'

方法二

alter database <DatabaseName> set emergency

alter database <DatabaseName> set single_user with rollback immediate

use master

alter database <DatabaseName> Rebuild Log on

(name=<DatabaseName>_log,filename='c:\sql\logs\<DatabaseName>_log.LDF')

use <DatabaseName>

dbcc checkdb

--此時(shí)checkdb有錯(cuò)誤才需要做步驟d)

d) 此時(shí)數(shù)據(jù)庫(kù)應(yīng)處于emergency狀態(tài)，創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)，通過(guò)bcp/bulk insert/DTS/SSIS方式將數(shù)據(jù)導(dǎo)出到新的數(shù)據(jù)庫(kù).

請(qǐng)注意，這種情況下索引，存儲(chǔ)過(guò)程，視圖等，都需要手工導(dǎo)出來(lái)。

4 導(dǎo)數(shù)據(jù)總結(jié)

1. 創(chuàng)建一個(gè)空的數(shù)據(jù)庫(kù)。

2. 使用腳本禁用所有外鍵約束。

Use <NewDatabase>

DECLARE @disable BIT

set @disable= 1

DECLARE

@sql VARCHAR(500),

@tableName VARCHAR(128),

@foreignKeyName VARCHAR(128)

-- A list of all of the Foreign Keys and the table names

DECLARE foreignKeyCursor CURSOR

FOR

SELECT

ref.constraint_name AS FK_Name,

fk.table_name AS FK_Table

FROM

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON ref.constraint_name = fk.constraint_name

ORDER BY

fk.table_name,

ref.constraint_name

OPEN foreignKeyCursor

FETCH NEXT FROM foreignKeyCursor INTO @foreignKeyName, @tableName

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

IF @disable = 1

SET @sql = 'ALTER TABLE [' + @tableName + '] NOCHECK CONSTRAINT [' + @foreignKeyName + ']'

ELSE

SET @sql = 'ALTER TABLE [' + @tableName + '] CHECK CONSTRAINT [' + @foreignKeyName + ']'

PRINT 'Executing Statement - ' + @sql

EXECUTE(@sql)

FETCH NEXT FROM foreignKeyCursor INTO @foreignKeyName, @tableName

END

CLOSE foreignKeyCursor

DEALLOCATE foreignKeyCursor

3. 使用腳本禁用所有觸發(fā)器。

DECLARE @disable BIT

set @disable= 1

DECLARE

@sql VARCHAR(500),

@tableName VARCHAR(128),

@tableSchema VARCHAR(128)

-- List of all tables

DECLARE triggerCursor CURSOR

FOR

SELECT

t.TABLE_NAME AS TableName,

t.TABLE_SCHEMA AS TableSchema

FROM

INFORMATION_SCHEMA.TABLES t

ORDER BY

t.TABLE_NAME,

t.TABLE_SCHEMA

OPEN triggerCursor

FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

IF @disable = 1

SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] DISABLE TRIGGER ALL'

ELSE

SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] ENABLE TRIGGER ALL'

PRINT 'Executing Statement - ' + @sql

EXECUTE ( @sql )

FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema

END

CLOSE triggerCursor

DEALLOCATE triggerCursor

4. 生成腳本并執(zhí)行啟用Identity_Insert。

select 'SET IDENTITY_INSERT dbo.['+ name +'] on

go'

from dbo.sysobjects

where type='U'

5. 自動(dòng)生成腳本導(dǎo)入數(shù)據(jù)到新的數(shù)據(jù)庫(kù)中。（對(duì)于存在計(jì)算列或者Identity字段的表需要手動(dòng)修改語(yǔ)句）

select 'insert into NewDB.dbo.'+ name +' select * from '+ name

from dbo.sysobjects

where type='U'

6. 使用腳本啟用所有外鍵約束，觸發(fā)器，禁用Identity_Insert。

啟用外鍵：

DECLARE @disable BIT

set @disable= 0

DECLARE

@sql VARCHAR(500),

@tableName VARCHAR(128),

@foreignKeyName VARCHAR(128)

-- A list of all of the Foreign Keys and the table names

DECLARE foreignKeyCursor CURSOR

FOR

SELECT

ref.constraint_name AS FK_Name,

fk.table_name AS FK_Table

FROM

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON ref.constraint_name = fk.constraint_name

ORDER BY

fk.table_name,

ref.constraint_name

OPEN foreignKeyCursor

FETCH NEXT FROM foreignKeyCursor INTO @foreignKeyName, @tableName

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

IF @disable = 1

SET @sql = 'ALTER TABLE [' + @tableName + '] NOCHECK CONSTRAINT [' + @foreignKeyName + ']'

ELSE

SET @sql = 'ALTER TABLE [' + @tableName + '] CHECK CONSTRAINT [' + @foreignKeyName + ']'

PRINT 'Executing Statement - ' + @sql

EXECUTE(@sql)

FETCH NEXT FROM foreignKeyCursor INTO @foreignKeyName, @tableName

END

CLOSE foreignKeyCursor

DEALLOCATE foreignKeyCursor

啟用觸發(fā)器：

DECLARE @disable BIT

set @disable= 0

DECLARE

@sql VARCHAR(500),

@tableName VARCHAR(128),

@tableSchema VARCHAR(128)

-- List of all tables

DECLARE triggerCursor CURSOR

FOR

SELECT

t.TABLE_NAME AS TableName,

t.TABLE_SCHEMA AS TableSchema

FROM

INFORMATION_SCHEMA.TABLES t

ORDER BY

t.TABLE_NAME,

t.TABLE_SCHEMA

OPEN triggerCursor

FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

IF @disable = 1

SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] DISABLE TRIGGER ALL'

ELSE

SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] ENABLE TRIGGER ALL'

PRINT 'Executing Statement - ' + @sql

EXECUTE ( @sql )

FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema

END

CLOSE triggerCursor

DEALLOCATE triggerCursor

禁用Identity_Insert：

select 'SET IDENTITY_INSERT dbo.['+ name +'] off

go'

from dbo.sysobjects

where type='U'

5 附錄

我們可以看到，數(shù)據(jù)庫(kù)恢復(fù)是一個(gè)非常復(fù)雜也是非常不可靠的方式，這就要求我們事先做好數(shù)據(jù)庫(kù)的備份。以下是我們對(duì)數(shù)據(jù)庫(kù)備份的一些基本策略，供您參考。

1．對(duì)所有的數(shù)據(jù)庫(kù)來(lái)說(shuō)，備份策略取決于數(shù)據(jù)庫(kù)被修改的頻繁程度，以及數(shù)據(jù)庫(kù)的重要程度。一般來(lái)說(shuō)，系統(tǒng)數(shù)據(jù)庫(kù)不像用戶數(shù)據(jù)庫(kù)那樣會(huì)經(jīng)常的修改。重要的系統(tǒng)數(shù)據(jù)庫(kù)有：

Master

Master 數(shù)據(jù)庫(kù)存儲(chǔ)了SQL Server 系統(tǒng)一級(jí)的信息，是最重要的數(shù)據(jù)庫(kù)。它還包括所有的登陸賬號(hào)和所有的系統(tǒng)配置信息。同時(shí)它還存儲(chǔ)了SQL Server還包含哪些數(shù)據(jù)庫(kù)（包括用戶數(shù)據(jù)庫(kù)），這些數(shù)據(jù)庫(kù)存在哪個(gè)地方等等信息。

MSDB

MSDB 數(shù)據(jù)庫(kù)包含了所有作業(yè)的的內(nèi)容和調(diào)度信息等等。

我們建議您每一個(gè)星期備份一次系統(tǒng)數(shù)據(jù)庫(kù)。同時(shí)當(dāng)您手動(dòng)更改過(guò)系統(tǒng)數(shù)據(jù)庫(kù)時(shí)，應(yīng)該立即手動(dòng)備份一次。比如添加了一個(gè)用戶，新建了一個(gè)數(shù)據(jù)庫(kù)，更改了一個(gè)作業(yè)等等。

2．對(duì)所有的用戶數(shù)據(jù)庫(kù)，如果它是一個(gè)重要的數(shù)據(jù)庫(kù)，除了用完全的恢復(fù)模式外，還需要調(diào)度備份您的數(shù)據(jù)庫(kù)：

A）完全備份每周一次

B）差異備份每天或者每半天一次

C）日志備份每小時(shí)或者半小時(shí)一次

注意：

A）所有的這些都可以用數(shù)據(jù)庫(kù)維護(hù)計(jì)劃來(lái)完成（Maintenance Plan），同時(shí)它也可以自動(dòng)刪除舊的文件，比如說(shuō)4周之前的，以避免磁盤空間不夠的問(wèn)題。

B）我們不建議備份到網(wǎng)絡(luò)磁盤上。以我們的經(jīng)驗(yàn)，直接備份到網(wǎng)絡(luò)磁盤會(huì)有些問(wèn)題。

C）上述備份時(shí)間周期僅供參考，您需要根據(jù)實(shí)際情況制定計(jì)劃。

數(shù)據(jù)庫(kù)被置疑主要是因?yàn)閿?shù)據(jù)庫(kù)data文件或者日志文件損壞. Data文件就是MDF、NDF文件,日志就是事務(wù)日志, 通常是LDF文件.

文件損壞的原因有:

(1) 一個(gè)最常見(jiàn)的原因是磁盤問(wèn)題. 如果數(shù)據(jù)庫(kù)文件因?yàn)榇疟P原因而binary受到損壞,那么SQL Server 往往不能從文件正常讀取數(shù)據(jù),這個(gè)時(shí)候很容易導(dǎo)致數(shù)據(jù)庫(kù)置疑. 對(duì)于這種情況, 你往往可以從機(jī)器的系統(tǒng)日志中發(fā)現(xiàn)磁盤相關(guān)的錯(cuò)誤. 在SQL Server 的錯(cuò)誤日志中也常?？梢钥吹?/span>823錯(cuò)誤:

錯(cuò)誤 823 嚴(yán)重級(jí)別 24 消息正文

在文件 ''%4!'' 的偏移量 %3! 處的 %2! 過(guò)程中，檢測(cè)到 I/O 錯(cuò)誤 %1!。

注意損壞可以是靜態(tài)的損壞, 比如磁盤某磁道的問(wèn)題而導(dǎo)致文件的某部分不能被正常讀取等. 也可以是動(dòng)態(tài)的.比方說(shuō)由于硬件問(wèn)題，文件在SQL Server 的讀寫過(guò)程中損壞.

(2) 如果SQL Server還在運(yùn)行而機(jī)器突然斷電或熱啟動(dòng),那么數(shù)據(jù)庫(kù)文件就有損壞的可能.

(3) 一些病毒也可以導(dǎo)致文件損壞

(4) 人為的因素. 比方說(shuō)日志文件被誤刪等.

6 引用

Disabling Constraints and Triggers (Quite useful)

http://msdn.microsoft.com/en-us/magazine/cc163442.aspx

聯(lián)機(jī)叢書的管理SQL Server -> 備份和還原數(shù)據(jù)庫(kù)這一章節(jié)

關(guān)于SQL Server 備份和恢復(fù)的文章

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

SQL Server 的高可用性

http://support.microsoft.com/?id=822400

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購(gòu)買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來(lái)自：瓜瓜2uuq7332fe > 《sqlserver》

舉報(bào)/認(rèn)領(lǐng)