Classification
金蝶智库数据库质疑修复总结 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）的一般方法，供您参考。 同时在操作下列步骤之前，您可以备份质疑数据库的MDF、NDF、LDF文件，以便以下步骤恢复失败时能够回滚到原来的状态。
1. 在查询分析器中，运行如下命令将数据库置于紧急模式。
Sp_configure 'allow ｕｐｄａｔｅ', 1
Go
Reconfigure with override
Go
Begin Tran
ｕｐｄａｔｅ master..sysdatabases set status = 32768 where name ='<DatabaseName>’
Commit Tran
--此处<DatabaseName>需要替代成您出问题的数据库名字。
--以下同
Go
ｓｅｌｅｃｔ * 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
ｓｅｌｅｃｔ * from master.dbo.sysdatabases
Go
5. 再运行如下命令，检查是否有人在使用当前出问题的数据库，如果有的话，请将他们退出。
ｓｅｌｅｃｔ * from master.dbo.sysprocesses
Go
6. 然后运行如下命令，将数据库置于单用户使用模式。如果设置不生效，可以尝试使用企业管理器->数据库属性-> 选项来直接设置．
ｅｘｅｃ 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
--此命令可能需要运行多次，才能完全修复。
方向二：
通过BCP，DTS，ｓｅｌｅｃｔ into等方式将好的表，或者表中好的数据导出来。建议使用BCP的方法，这样可以最大限度的回复数据．BCP会停在出错的纪录上，但是前面的数据就能成功导出．使用DTS或ｓｅｌｅｃｔ into的话, 我们很难判断最大限度能导出的记录数．
优点：导出来的数据保证在应用程序逻辑这一层次的正确性
缺点：不会修复数据库中存在的错误，丢失的数据量会比较大，取决于第7步的运行结果。
9. 数据库完全恢复正常之后，将数据库置于正常状态，并将单用户模式改成多用户模式。(16或者0)
begin tran
ｕｐｄａｔｅ sysdatabases set status = 8 where name = 'db_name'
commit tran
sp_configure 'allow', 0
go
reconfigure with override
ｅｘｅｃ 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
ｓｅｌｅｃｔ * 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 ｉｎｓｅｒｔ/DTS/SSIS方式将数据导出到新的数据库.
请注意，这种情况下索引，存储过程，视图等，都需要手工导出来。
4 导数据总结
1. 创建一个空的数据库。
2. 使用脚本禁用所有外键约束。
Use <NewDatabase>
GO
ｄｅｃｌａｒｅ @disable BIT
set @disable= 1
ｄｅｃｌａｒｅ
@sql VARCHAR(500),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128)
-- A list of all of the Foreign Keys and the table names
ｄｅｃｌａｒｅ foreignKeyCursor CURSOR
FOR
ｓｅｌｅｃｔ
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. 使用脚本禁用所有触发器。
ｄｅｃｌａｒｅ @disable BIT
set @disable= 1
ｄｅｃｌａｒｅ
@sql VARCHAR(500),
@tableName VARCHAR(128),
@tableSchema VARCHAR(128)
-- List of all tables
ｄｅｃｌａｒｅ triggerCursor CURSOR
FOR
ｓｅｌｅｃｔ
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。
ｓｅｌｅｃｔ 'SET IDENTITY_INSERT dbo.['+ name +'] on
go'
from dbo.sysobjects
where type='U'
5. 自动生成脚本导入数据到新的数据库中。（对于存在计算列或者Identity字段的表需要手动修改语句）
ｓｅｌｅｃｔ 'ｉｎｓｅｒｔ into NewDB.dbo.'+ name +' ｓｅｌｅｃｔ * from '+ name
from dbo.sysobjects
where type='U'
6. 使用脚本启用所有外键约束，触发器，禁用Identity_Insert。
启用外键：
ｄｅｃｌａｒｅ @disable BIT
set @disable= 0
ｄｅｃｌａｒｅ
@sql VARCHAR(500),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128)
-- A list of all of the Foreign Keys and the table names
ｄｅｃｌａｒｅ foreignKeyCursor CURSOR
FOR
ｓｅｌｅｃｔ
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
启用触发器：
ｄｅｃｌａｒｅ @disable BIT
set @disable= 0
ｄｅｃｌａｒｅ
@sql VARCHAR(500),
@tableName VARCHAR(128),
@tableSchema VARCHAR(128)
-- List of all tables
ｄｅｃｌａｒｅ triggerCursor CURSOR
FOR
ｓｅｌｅｃｔ
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：
ｓｅｌｅｃｔ '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文件就是MDF、NDF文件,日志就是事务日志, 通常是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
扫一扫关注微信号
