数据库质疑修复总结 For SQL Server 2000/2005/2008/2008R2


 1    前言

本文的步骤基于以下条件:

1.     SQL Server可以启动。

2.     数据库没有做有效的备份。

3.     当前用户有Sysadmin权限。

 

数据库质疑的原因会有多种多样,不同的suspect采用的步骤也会有所不同,以下的步骤不能适用所有的情况,但包括了一些基本的步骤。 数据库suspect是指数据库内部处于不一致的状态,很有可能会有数据丢失。 我们推荐您从“好的数据库备份”恢复。 我们这里所指的“好的数据库备份”是指:

1.  在做数据库备份之前,您检查过DBCC CHECKDB没有错误。

2.  备份之后的数据库没有更改,或者更改可以忽略。

 

在做任何修复操作之前,请务必备份.mdf/.ndf以及.ldf文件。

  

2   SQL Server 2000修复步骤

 

如果没有“好的数据库备份”,我们不能保证没有数据丢失。 以下是恢复suspect数据库 (SQL Server 2000)的一般方法,供您参考。 同时在操作下列步骤之前,您可以备份质疑数据库的MDFNDFLDF文件,以便以下步骤恢复失败时能够回滚到原来的状态。

1.  在查询分析器中,运行如下命令将数据库置于紧急模式。

Sp_configure 'allow update', 1

Go

Reconfigure with override

Go

Begin Tran

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

Commit Tran

--此处<DatabaseName>需要替代成您出问题的数据库名字。

--以下同

Go

Select * from sysdatabases

--检查数据库状态是否已经变成 32768

Go

 

2. 重启SQL Server服务。

 

3.  如果第二步中重启服务,数据库再次进入suspect模式,请设置数据库紧急模式,使用BCP方式导出数据。

否则在查询分析器中,重新连接到SQL Server,运行如下命令重建日志。

特别注意: dbcc rebuild_log是内部命令,请您阅读附件中的DBCC Rebuild Run Command.doc。 只有当您同意了附件中的内容,才可以运行此命令。

 

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

--此处<The file name and the path of the log file>是新的日志文件的物理文件的绝对地址。

go

 

如果rebuild_log失败,请运行如下命令,

 

Use <DatabaseName>

Go

 

如果数据库在emergency 方式能够进入紧急模式,直接使用BCP方式导出数据,没有其他方式来恢复数据库。

如果不能进入,则没有其他方式恢复数据库

 

如果数据库在Rebuild_log之后恢复正常,进入第四步。

 

4.  关掉查询分析器,再次打开查询分析器,运行如下语句,查询出问题的数据库的DBID

Select * from master.dbo.sysdatabases

Go

 

5.  再运行如下命令,检查是否有人在使用当前出问题的数据库,如果有的话,请将他们退出。

Select * from master.dbo.sysprocesses

Go

 

6.  然后运行如下命令,将数据库置于单用户使用模式。如果设置不生效,可以尝试使用企业管理器->数据库属性-> 选项来直接设置.

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

 

7.  在查询分析器中,运行如下命令,检查数据库是否有损坏。

dbcc checkdb(‘<DatabaseName>')

go

 

8.  如果您运行上述命令发现数据库有错误。 此时我们需要根据错误来处理。 接下去有两个方向。

方向一:

使用Repair_Allow_Data_Loss选项修复数据库。

优点: 可能可以恢复尽量多的数据

缺点:

a)  不一定能够将全部错误修复,还有可能越修越多。同时,需要大量时间,需要经过多次执行修复命令.十几次,甚至数十次.修复时间不能预估.

b)  就算我们将所有错误修复,我们也不能保证数据在应用程序逻辑这一层次上的数据正确性,您需要找您的应用程序提供商来检查数据在程序逻辑层次是否正确。

 

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

--此命令可能需要运行多次,才能完全修复。

 

方向二:

通过BCPDTSselect into等方式将好的表,或者表中好的数据导出来。建议使用BCP的方法,这样可以最大限度的回复数据.BCP会停在出错的纪录上,但是前面的数据就能成功导出.使用DTSSelect into的话, 我们很难判断最大限度能导出的记录数.

优点:导出来的数据保证在应用程序逻辑这一层次的正确性

缺点:不会修复数据库中存在的错误,丢失的数据量会比较大,取决于第7步的运行结果。

 

9.  数据库完全恢复正常之后,将数据库置于正常状态,并将单用户模式改成多用户模式。(16或者0)

begin tran

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

commit tran

 

sp_configure 'allow', 0

go

reconfigure with override

 

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

go

 

另外,在上述步骤之前或者之中,可能会有数据库在企业管理器中看不到,并且无法attach的状况。 通常可以建同名数据库(注意:物理文件名也要一致),停止SQL Server,覆盖文件,重启SQLServer的方式来解决。

 

3   SQL Server 2005/2008/2008R2修复步骤

方法一

alter database <DatabaseName>set emergency

go

alter database <DatabaseName>set single_user with rollback immediate

go

use <DatabaseName>

go

dbcc checkdb

go

use master

go

alter database <DatabaseName>Rebuild Log on

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

go

select * from sys.databases

go

dbcc checkdb('<DatabaseName>', repair_allow_data_loss)

go

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

 

方法二

alter database <DatabaseName> set emergency

go

alter database <DatabaseName> set single_user with rollback immediate

go

use master

go

alter database <DatabaseName> Rebuild Log on

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

go

use <DatabaseName>

go

dbcc checkdb

go

--此时checkdb有错误才需要做步骤d)

 

d) 此时数据库应处于emergency状态,创建一个新数据库,通过bcp/bulk insert/DTS/SSIS方式将数据导出到新的数据库.

请注意,这种情况下索引,存储过程,视图等,都需要手工导出来。

 

4             导数据总结

1. 创建一个空的数据库。

2. 使用脚本禁用所有外键约束。

       Use <NewDatabase>

GO

 

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

 

    Go

3. 使用脚本禁用所有触发器。

  

       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.     生成脚本并执行启用Identity_Insert

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

go'

from dbo.sysobjects

where type='U'

5.     自动生成脚本导入数据到新的数据库中。(对于存在计算列或者Identity字段的表需要手动修改语句)

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

from dbo.sysobjects

where type='U'

 

6.     使用脚本启用所有外键约束,触发器,禁用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

 

     Go

 

启用触发器:

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             附录

 

我们可以看到,数据库恢复是一个非常复杂也是非常不可靠的方式,这就要求我们事先做好数据库的备份。以下是我们对数据库备份的一些基本策略,供您参考。

 

1对所有的数据库来说,备份策略取决于数据库被修改的频繁程度,以及数据库的重要程度。一般来说,系统数据库不像用户数据库那样会经常的修改。重要的系统数据库有:

Master

Master 数据库存储了SQL Server 系统一级的信息,是最重要的数据库。它还包括所有的登陆帐号和所有的系统配置信息。同时它还存储了SQL Server还包含哪些数据库(包括用户数据库),这些数据库存在哪个地方等等信息。

 

MSDB

MSDB 数据库包含了所有作业的的内容和调度信息等等。

 

我们建议您每一个星期备份一次系统数据库。同时当您手动更改过系统数据库时,应该立即手动备份一次。比如添加了一个用户,新建了一个数据库,更改了一个作业等等。

 

2.对所有的用户数据库,如果它是一个重要的数据库,除了用完全的恢复模式外,还需要调度备份您的数据库:

 

A)完全备份每周一次

 

B)差异备份每天或者每半天一次

 

C)日志备份每小时或者半小时一次

 

注意:

 

A)所有的这些都可以用数据库维护计划来完成(Maintenance Plan),同时它也可以自动删除旧的文件,比如说4周之前的,以避免磁盘空间不够的问题。

B)我们不建议备份到网络磁盘上。以我们的经验,直接备份到网络磁盘会有些问题。

C)上述备份时间周期仅供参考,您需要根据实际情况制定计划。

 

 

 

数据库被置疑主要是因为数据库data文件或者日志文件损坏. Data文件就是MDFNDF文件,日志就是事务日志, 通常是LDF文件.

文件损坏的原因有:

(1) 一个最常见的原因是磁盘问题. 如果数据库文件因为磁盘原因而binary受到损坏,那么SQL Server 往往不能从文件正常读取数据,这个时候很容易导致数据库置疑. 对于这种情况, 你往往可以从机器的系统日志中发现磁盘相关的错误. SQL Server 的错误日志中也常常可以看到823错误:

错误 823 严重级别 24 消息正文

在文件 ''%4!'' 的偏移量 %3! 处的 %2! 过程中,检测到 I/O 错误 %1!

注意损坏可以是静态的损坏, 比如磁盘某磁道的问题而导致文件的某部分不能被正常读取等. 也可以是动态的.比方说由于硬件问题,文件在SQL Server 的读写过程中损坏.

(2) 如果SQL Server还在运行而机器突然断电或热启动,那么数据库文件就有损坏的可能.

(3) 一些病毒也可以导致文件损坏

(4) 人为的因素. 比方说日志文件被误删等.

 

6             引用

Disabling Constraints and Triggers (Quite useful)

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

 

联机丛书的管理SQL Server -> 备份和还原数据库这一章节

关于SQL Server 备份和恢复的文章

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

 


Comments (0)

Skip to main content