数据库质疑修复总结.docx
《数据库质疑修复总结.docx》由会员分享,可在线阅读,更多相关《数据库质疑修复总结.docx(17页珍藏版)》请在冰豆网上搜索。
数据库质疑修复总结
数据库质疑修复总结ForSQLServer2000/2005/2008/2008R2
目录
1 前言2
2 SQLServer2000修复步骤2
1. 在查询分析器中,运行如下命令将数据库置于紧急模式。
2
2. 重启SQLServer服务。
3
3. 如果第二步中重启服务,数据库再次进入suspect模式,请设置数据库紧急模式,使用BCP方式导出数据。
3
4. 关掉查询分析器,再次打开查询分析器,运行如下语句,查询出问题的数据库的DBID4
5. 再运行如下命令,检查是否有人在使用当前出问题的数据库,如果有的话,请将他们退出。
4
6. 然后运行如下命令,将数据库置于单用户使用模式。
如果设置不生效,可以尝试使用企业管理器->数据库属性-> 选项来直接设置.4
7. 在查询分析器中,运行如下命令,检查数据库是否有损坏。
4
8. 如果您运行上述命令发现数据库有错误。
此时我们需要根据错误来处理。
接下去有两个方向。
5
9. 数据库完全恢复正常之后,将数据库置于正常状态,并将单用户模式改成多用户模式。
5
3 SQLServer2005/2008/2008R2修复步骤6
4 导数据总结7
1. 创建一个空的数据库。
7
2. 使用脚本禁用所有外键约束。
7
3. 使用脚本禁用所有触发器。
9
4. 生成脚本并执行启用Identity_Insert。
10
5. 自动生成脚本导入数据到新的数据库中。
(10
6. 使用脚本启用所有外键约束,触发器10
5 附录12
1 前言
本文的步骤基于以下条件:
1. SQLServer可以启动。
2. 数据库没有做有效的备份。
3. 当前用户有Sysadmin权限。
数据库质疑的原因会有多种多样,不同的suspect采用的步骤也会有所不同,以下的步骤不能适用所有的情况,但包括了一些基本的步骤。
数据库suspect是指数据库内部处于不一致的状态,很有可能会有数据丢失。
我们推荐您从“好的数据库备份”恢复。
我们这里所指的“好的数据库备份”是指:
1. 在做数据库备份之前,您检查过DBCCCHECKDB没有错误。
2. 备份之后的数据库没有更改,或者更改可以忽略。
在做任何修复操作之前,请务必备份.mdf/.ndf以及.ldf文件。
2 SQLServer2000修复步骤
如果没有“好的数据库备份”,我们不能保证没有数据丢失。
以下是恢复suspect数据库(SQLServer2000)的一般方法,供您参考。
同时在操作下列步骤之前,您可以备份质疑数据库的MDF、NDF、LDF文件,以便以下步骤恢复失败时能够回滚到原来的状态。
1. 在查询分析器中,运行如下命令将数据库置于紧急模式。
Sp_configure‘allowupdate’,1
Go
Reconfigurewithoverride
Go
BeginTran
Updatemaster..sysdatabasessetstatus=32768wherename='’
CommitTran
—此处需要替代成您出问题的数据库名字。
—以下同
Go
Select*fromsysdatabases
—检查数据库状态是否已经变成 32768
Go
2. 重启SQLServer服务。
3. 如果第二步中重启服务,数据库再次进入suspect模式,请设置数据库紧急模式,使用BCP方式导出数据。
否则在查询分析器中,重新连接到SQLServer,运行如下命令重建日志。
特别注意:
dbccrebuild_log是内部命令,请您阅读附件中的DBCCRebuildRunCommand.doc。
只有当您同意了附件中的内容,才可以运行此命令。
dbccrebuild_log(‘’,'’)
—此处是新的日志文件的物理文件的绝对地址。
go
如果rebuild_log失败,请运行如下命令,
Use
Go
如果数据库在emergency 方式能够进入紧急模式,直接使用BCP方式导出数据,没有其他方式来恢复数据库。
如果不能进入,则没有其他方式恢复数据库
如果数据库在Rebuild_log之后恢复正常,进入第四步。
4. 关掉查询分析器,再次打开查询分析器,运行如下语句,查询出问题的数据库的DBID
Select*frommaster.dbo.sysdatabases
Go
5. 再运行如下命令,检查是否有人在使用当前出问题的数据库,如果有的话,请将他们退出。
Select*frommaster.dbo.sysprocesses
Go
6. 然后运行如下命令,将数据库置于单用户使用模式。
如果设置不生效,可以尝试使用企业管理器->数据库属性-> 选项来直接设置.
execsp_dboptionN'’,N’single’,N’true’
7. 在查询分析器中,运行如下命令,检查数据库是否有损坏。
dbcccheckdb(‘’)
go
8. 如果您运行上述命令发现数据库有错误。
此时我们需要根据错误来处理。
接下去有两个方向。
方向一:
使用Repair_Allow_Data_Loss选项修复数据库。
优点:
可能可以恢复尽量多的数据
缺点:
a) 不一定能够将全部错误修复,还有可能越修越多。
同时,需要大量时间,需要经过多次执行修复命令.十几次,甚至数十次.修复时间不能预估.
b) 就算我们将所有错误修复,我们也不能保证数据在应用程序逻辑这一层次上的数据正确性,您需要找您的应用程序提供商来检查数据在程序逻辑层次是否正确。
dbcccheckdb(‘’,REPAIR_ALLOW_DATA_LOSS)go
—此命令可能需要运行多次,才能完全修复。
方向二:
通过BCP,DTS,selectinto等方式将好的表,或者表中好的数据导出来。
建议使用BCP的方法,这样可以最大限度的回复数据.BCP会停在出错的纪录上,但是前面的数据就能成功导出.使用DTS或Selectinto的话, 我们很难判断最大限度能导出的记录数.
优点:
导出来的数据保证在应用程序逻辑这一层次的正确性
缺点:
不会修复数据库中存在的错误,丢失的数据量会比较大,取决于第7步的运行结果。
9. 数据库完全恢复正常之后,将数据库置于正常状态,并将单用户模式改成多用户模式。
(16或者0)
begintran
updatesysdatabasessetstatus=8wherename=‘db_name’
committran
sp_configure‘allow’,0
go
reconfigurewithoverride
execsp_dboptionN'’,N’single’,N’false’
go
另外,在上述步骤之前或者之中,可能会有数据库在企业管理器中看不到,并且无法attach的状况。
通常可以建同名数据库(注意:
物理文件名也要一致),停止SQLServer,覆盖文件,重启SQLServer的方式来解决。
3 SQLServer2005/2008/2008R2修复步骤
方法一
alterdatabase setemergency
go
alterdatabase setsingle_userwithrollbackimmediate
go
use
go
dbcccheckdb
go
usemaster
go
alterdatabase RebuildLogon
(name=_log,filename=’c:
\sql\logs\_log.LDF’)
go
select*fromsys.databases
go
dbcccheckdb(‘‘,repair_allow_data_loss)
go
sp_dboption‘’,’singleuser’,’false’
方法二
alterdatabasesetemergency
go
alterdatabasesetsingle_userwithrollbackimmediate
go
usemaster
go
alterdatabaseRebuildLogon
(name=_log,filename=’c:
\sql\logs\_log.LDF’)
go
use
go
dbcccheckdb
go
—此时checkdb有错误才需要做步骤d)
d) 此时数据库应处于emergency状态,创建一个新数据库,通过bcp/bulkinsert/DTS/SSIS方式将数据导出到新的数据库.
请注意,这种情况下索引,存储过程,视图等,都需要手工导出来。
4 导数据总结
1. 创建一个空的数据库。
2. 使用脚本禁用所有外键约束。
Use
GO
DECLARE @disable BIT
set @disable= 1
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128)
—AlistofalloftheForeignKeysandthetablenames
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 = ‘ALTERTABLE[‘ + @tableName + ‘]NOCHECKCONSTRAINT[‘ + @foreignKeyName + ‘]’
ELSE
SET @sql = ‘ALTERTABLE[‘ + @tableName + ‘]CHECKCONSTRAINT[‘ + @foreignKeyName + ‘]’
PRINT ‘ExecutingStatement–‘ + @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)
—Listofalltables
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 = ‘ALTERTABLE‘ + @tableSchema + ‘.[‘ + @tableName + ‘]DISABLETRIGGERALL’
ELSE
SET @sql = ‘ALTERTABLE‘ + @tableSchema + ‘.[‘ + @tableName + ‘]ENABLETRIGGERALL’
PRINT ‘ExecutingStatement–‘ + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
4. 生成脚本并执行启用Identity_Insert。
select ‘SETIDENTITY_INSERTdbo.[‘+ name +‘]on
go’
from dbo.sysobjects
where type=‘U’
5. 自动生成脚本导入数据到新的数据库中。
(对于存在计算列或者Identity字段的表需要手动修改语句)
select ‘insertintoNewDB.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)
—AlistofalloftheForeignKeysandthetablenames
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 = ‘ALTERTABLE[‘ + @tableName + ‘]NOCHECKCONSTRAINT[‘ + @foreignKeyName + ‘]’
ELSE
SET @sql = ‘ALTERTABLE[‘ + @tableName + ‘]CHECKCONSTRAINT[‘ + @foreignKeyName + ‘]’
PRINT ‘ExecutingStatement–‘ + @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)
—Listofalltables
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 = ‘ALTERTABLE‘ + @tableSchema + ‘.[‘ + @tableName + ‘]DISABLETRIGGERALL’
ELSE
SET @sql = ‘ALTERTABLE‘ + @tableSchema + ‘.[‘ + @tableName + ‘]ENABLETRIGGERALL’
PRINT ‘ExecutingStatement–‘ + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
禁用Identity_Insert:
select ‘SETIDENTITY_INSERTdbo.[‘+ name +‘]off
go’
from dbo.sysobjects
where type=‘U’
5 附录
我们可以看到,数据库恢复是一个非常复杂也是非常不可靠的方式,这就要求我们事先做好数据库的备份。
以下是我们对数据库备份的一些基本策略,供您参考。
1.对所有的数据库来说,备份策略取决于数据库被修改的频繁程度,以及数据库的重要程度。
一般来说,系统数据库不像用户数据库那样会经常的修改。
重要的系统数据库有:
Master
Master 数据库存储了SQLServer 系统一级的信息,是最重要的数据库。
它还包括所有的登陆帐号和所有的系统配置信息。
同时它还存储了SQLServer还包含哪些数据库(包括用户数据库),这些数据库存在哪个地方等等信息。
MSDB
MSDB 数据库包含了所有作业的的内容和调度信息等等。
我们建议您每一个星期备份一次系统数据库。
同时当您手动更改过系统数据库时,应该立即手动备份一次。
比如添加了一个用户,新建了一个数据库,更改了一个作业等等。
2.对所有的用户数据库,如果它是一个重要的数据库,除了用完全的恢复模式外,还需要调度备份您的数据库:
A)完全备份每周一次
B)差异备份每天或者每半天一次
C)日志备份每小时或者半小时一次
注意:
A)所有的这些都可以用数据库维护计划来完成(MaintenancePlan),同时它也可以自动删除旧的文件,比如说4周之前的,以避免磁盘空间不够的问题。
B)我们不建议备份到网络磁盘上。
以我们的经验,直接备份到网络磁盘会有些问题。
C)上述备份时间周期仅供参考,您需要根据实际情况制定计划。
数据库被置疑主要是因为数据库data文件或者日志文件损坏.Data文件就是MD