SQL置疑REPAIR_REBUILD修复

1、寒山 sql 数据库修复中心 出错状态 :现象 1：数据库后面有“置 疑”字样，查看系统事 务日记出现以 下错误： 错误1 错误: 823，严重度 : 24，状态 : 2I/O error 23( 数 据 错 误 ( 循 环 冗 余 检 查 ) 。 ) detected during read at offset 0x00000000200000 in file 捷 作 2008datatest_Data.MDF'. 错 误 2 错误: 3313，严重度 : 21，状态 : 2恢复数据库 '' 的日志中记录的操作时出错。出错位置在日志记录ID (274:377:2)。错

2、误 3 错误: 3313，严重度 : 21，状态 : 2Error while redoing logged operation in database 'test'. Error at log record ID (274:377:2). 数据库可以分离，但分离后无法附加，附加时出现“823”号错误。 微 软 公 司 SQL 联机从书解释 :错误 823 严重级别 24消息正文 在文件 ''%4!'' 的偏移量 %3! 处的 %2! 过程中， 检测到 I/O 错误 %1!。 解 释Microsoft? SQL Server? 在对某设备进行读或

3、写请求时遇到 I/O 错误。该错误通常表明 磁盘问题。但是，错误日志中在错误 823 之前记录的其它核心消息应指出涉及了哪个设备。 对策检查该设备的可访问性和状态。如果可能，执行硬件诊断并纠正问题。 从最新的数据 库备份还原损坏的文件。从数据库备份中还原应始终是修复已损坏数据库的首选方法。如果没有备份或者检测到的错误是孤立的，则DBCC CHECKDB勺修复功能可能很有用。然而，比起从备份中还原损坏的文件，可能使用DBCC CHECKDB消耗的时间更多，且可能无法恢复全部数据。注意？?如果使用修复子句运行DBCC CHECKDB时，问题没有得到纠正，或者不知道该过程将如何影响数据， 请与主要的

4、支持提供者联系。 出错原因 :通常这个问题是由于硬盘空 间不够/硬盘读写错误/忽然断电(停电/死机),SQL系统异常。1. 日志文件被破坏 823 错误 日志文件被破坏的数据库文件， 通过如下方法附加上去后， 数据库里所有的表都不能访 问，提示错误 832，请问要如何解决？use master gosp_configure 'allow updates',1 goreconfigure with override go/* 注意输正确，如果输入后执行此语句，并且下面显示DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。已将配置选项'allow up

5、dates'从0改为1。请运行 RECONFIGURE语句以安装。说明执行正确，如果不显示以上信息，请检查是否有输错！此语句是的作用是：设置数据库允许直接操作系统表。*/update sysdatabases set status=-32768 where dbid=DB_ID('icyqshsf') /* 设置数据库为紧急 修复模式。 */ godbccrebuild_log('icyqshsf','e:ProgramFilesMicrosoftSQLServerMSSQLDataicyqshsf_log.ldf)/* 重新数据库日志(ldf)

6、文件。下面显示 :警告：数据库'test'的日志已重建。已失去事务的一致性。应运行DBCC CHECKDBA验 证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。*/ godbcc checkdb('icyqshsf')/* 现在检查有没有错误，再输入语法 下面显示CHECKDB发现了 0个分配错误和 0个一致性错误(在数据库 'tiger' 中)。那说明第6步就建立成功没问题了，下面就可以把SQL恢复模式了 */ gosp_dboption 'icyqshsf','dbo use only',&

7、#39;false' gosp_configure 'allow updates',0 go reconfigure with override go2. 附加数据库文件时 ,提示 823 错误 EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE /* 打开修改系统表的 开关 */update sysdatabases set status = 32768 where name = ' 数据库名 ' DBCC REBUILD_LOG (数' 据库名 '

8、, 'E： dzzdatabase dzz1204_Log.LDF' ) update sysdatabases set status = 0 where name = '数 据库名 ' restore database 数据库名 WITH RECOVERYEXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDE /* 关闭打开修改系统 表的开关 */3因为停电等原因造成 MSSQL数据库，提示823错误 USE MASTER GOsp_dboption 'database

9、Name', 'single user', 'true' GoDBCC CHECKDB('databaseName'， REPAIR_REBUILD) GoUSE databaseName goexec sp_msforeachtable 'DBCC CHECKTABLE('''?'''，REPAIR_REBUILD)' gosp_dboption 'databaseName'， 'single user'， 'false' G

10、o 如果还不行，可以采用允许丢失数据 的方式修复，如下： USE MASTER GOsp_dboption 'databaseName'， 'single user'， 'true' GoDBCC CHECKDB('databaseName'， REPAIR_ALLOW_DATA_LOSS) GoUSE databaseName goexec sp_msforeachtable 'DBCC CHECKTABLE('''?'''，REPAIR_REBUILD)' gos

11、p_dboption 'databaseName'， 'single user'， 'false' Go=修 复 方 法 恢复语句 ：-drop database wgy -create database wgy-RESTORE FILELISTOYNfLrom disk='F：data1218wgy.db' ALTER DATABASE wgy SET OFFLINE WITH ROLLBACKIMMEDIATE restore database wgy from disk='F：data1218wgy.db'

12、with replace，MOVE 'Syb_Data' TO 'D：DataNPDFDN.mdf'， MOVE 'Syb_Log' TO 'D：DataNPDFDN.ldf'ALTER DATABASE wgy SET ONLINE WITH ROLLBACK IMMEDIAT法 一：1. 新建一个同名的数据库2.再停掉 sql server3. 用 mdf 和 ldf 数据库的文件覆盖掉这个新建的同名数据库4.再重启 sql server5. 此时打开企业管理器时新建的同名数据库会出现置疑，先不管，执行下面的语句(注 意修改其

13、中的数据库名 )USE MASTER GOSP_CONFIGURE 'ALLOW UpdateS',1 RECONFIGURE WITH OVERRIDE GOUpdate SYSDATABASES SET STATUS =32768 Where NAME='aaa' Gosp_dboption 'aaa', 'single user', 'true' GoDBCC CHECKDB('aaa') Goupdate sysdatabases set status =28 where name='

14、;aaa' Go sp_configure 'allow updates', 0 reconfigure with override Go sp_dboption 'aaa', 'single user', 'true' Go6. 完成后一般就可以访问数据库中的数据了,这时 ,数据库本身一般还要问题 ,解决办法是 ,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了 . 方法二：1. 新建一个同名的数据库2.再停掉 sql server3. 用 mdf 和 ldf 数据库的文件覆盖掉这个新建的同名数据库4. 再重启

15、sql server5.此时打开企业管理器时新建的同名数据库会出现置疑，先不管，执行下面的语句(注 意修改其中的数据库名 ) USE MASTER GOSP_CONFIGURE 'ALLOW UpdateS',1 RECONFIGURE WITH OVERRIDE GO Update SYSDATABASES SET STATUS =32768 Where NAME='aaa' Go sp_dboption 'aaa', 'single user', 'true' GoDBCC REBUILD_LOG ('

16、aaa', 'C:Program FilesMicrosoft SQL ServerMSSQLDataaaa_Log2.LDF' )Goupdate sysdatabases set status = 0 where name = 'aaa' Go restore database aaa WITH RECOVERY-如果表有错的话用DBCC CHECKTABL来进行修复 goDBCC CHECKDB('aaa')Goupdate sysdatabases set status =28 where name='aaa' G

17、o sp_configure 'allow updates', 0 reconfigure with override Go sp_dboption 'aaa', 'single user', 'true' Go6.完成后一般就可以访问数据库中的数据了,这时 ,数据库本身一般还要问题 ,解决办法是 ,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了 . 方法三：.非置疑数据库修复技术 :USE MASTER GO sp_dboption 'aaa', 'single user', '

18、true' Go-无损修复DBCC CHECKDB('aaa', REPAIR_REBUILD)-有损修复- DBCCCHECKDB('ke_2008',repair_allow_data_loss) WITH TABLOCK-如果表有错的话用DBCC CHECKTABL来 进行修复 Gosp_dboption 'aaa', 'single user', 'false' Go 方法四： /*- 重置置疑状态 系统方法 : 如果 sql server 因为磁盘驱动器不再有可用空间，而不能完成数据库的恢复，那么

19、microsoft? sql server? 2000 会返回错误 1105并且将 sysdatabases 中的 status 列设为置疑。按下面的步骤解决这个问题：执行sp_resetstatus 。 语法为：sp_resetstatus 'aaa' sp_resetstatus ' 数据库名 '用 alter database 向数据库添加一个数据文件或日志文件。 停止并重新启动 sql server 。 用新的数据文件或日志文件所提供的额外空间， sql server 应该能完成数据库的恢复。释放磁盘空间并且重新运行恢复操作。sp_resetstatus

20、 关闭数据库的置疑标志，但是原封不动地保持数据库的其它选项。-*/方法五：-手工重置置疑状态use master gosp_configure 'allow updates',1 reconfigure with override godeclare dbname varchar(30) set dbname='lqgs' if trancount > 0print '正在进行事务处理 ,操作不能进行 ' else if suser_id()!=1print ' 你 不 是 系 统 管 理 员 (sa), 不 能 进 行 此 操 作

21、 ' else if not exists(select 1 from master.sysdatabases where name=dbname) print ' 你要操作的数据库不存在' else if notexists(select 1 from master.sysdatabases where name= dbname and status & 256 = 256)print '你的数据库没有被置疑 ' else beginbegi n tran update master.sysdatabases set status = stat

22、us A 256 where n ame = db name if error != 0 or rowcount != 1 rollback tran else begincommit tranprint '操作成功 ,请重新启动 SQL' end end gosp_configure 'allow updates', 1 reconfigure with override gosp_resetstatus lqgs 方法六：重置置疑状态如果 SQL Server 因为磁盘驱动器不再有可用空间，而不能完成数据库的恢复，那么Microsoft? SQL Serve

23、r? 2000 会返回错误 1105 并且将 sysdatabases 中的 status 列设为置疑。按下面的步骤解决这个问题： 1. 执行 sp_resetstatus。2. 用Alter DATABASE向数据库添加一个数据文件或日志文件。3.停止并重新启动SQL Serve。用新的数据文件或日志文件所提供的额外空间， SQL Server 应该能完成数据库的恢 复。4. 释放磁盘空间并且重新运行恢复操作。sp_resetstatus 关闭数据库的置疑标志，但是原封不动地保持数据库的其它选项。注意 只有在您的主要支持提供者指导下或有疑难解答建议的做法时，才可以使用sp_resetstat

24、us。否则，可能会损坏数据库。由于该过程修改了系统表，系统管理员必须在创建这个过程前，启用系统表更新。要启用更新，使用下面的过程： USE master GOsp_configure 'allow updates', 1 GORECONFIGURE WITH OVERRIDE GO过程创建后，立即禁用系统表更新： sp_configure 'allow updates', 0 GORECONFIGURE WITH OVERRIDE GO只有系统管理员才能执行sp_resetstatus。执行该过程后，立即关闭SQL Serve。语法为：sp_resetstatu

25、s aaa下面的例子将关闭 PRODUCTION 数据库的置疑标志。 sp_resetstatus PRODUCTION 下 面是结果集：Database 'PRODUCTION' status reset!WARNING: You must reboot SQL Server prior to accessing this database! sp_resetstatus 存 储过程代码下面是 sp_resetstatus 存储过程的代码：IF EXISTS( Select * from sysobjects where name = 'sp_resetstatus&

26、#39; )Drop PROCEDURE sp_resetstatus GOCreate PROC sp_resetstatus dbname varchar(30) AS DECLAREmsg varchar(80) IF trancount > 0 BEGINPRINT 'Can''t run sp_resetstatus from within a transaction.' RETURN (1) ENDIF suser_id() != 1 BEGINSelect msg = 'You must be the System Administr

27、ator (SA)' Select msg = msg + ' to execute this procedure.' RETURN (1) ENDIF (Select COUNT(*) FROM master.sysdatabases Where name = dbname) != 1 BEGIN Select msg = 'Database ' + dbname + ' does not exist!' PRINT msg RETURN (1) END IF (Select COUNT(*) FROM master.sysdataba

28、sesWhere name = dbname AND status & 256 = 256) != 1 BEGINPRINT 'sp_resetstatus can only be run on suspect databases.' RETURN (1) ENDBEGIN TRANUpdate master.sysdatabases SET status = status A 256 Where n ame = db nameIF error != 0 or rowcount != 1 ROLLBACK TRAN ELSE BEGIN COMMIT TRANSelec

29、t msg = 'Database ' + dbname + ' status reset!' PRINT msg PRINT ''PRINT 'WARNING: You must reboot SQL Server prior to ' PRINT ' accessing this database!' PRINT '' ENDMS Sql Server 提供了很多数据库修复的命令，当数据库置疑或是有的无法完成 读取时可以尝试这些修复命令。1. DBCC CHECKDB重启服务器后，在没有进行任何

30、操作的情况下，在SQL查询分析器中执行以下SQL进行数据库的修复，修复数据库存在的一致性错误与分配错误。use masterdeclare databasename varchar(255)set database name='需要修复的数据库实体的名称'exec sp_dboption databasename, N'single', N'true' - 将目标数据库置为单用户状态dbcccheckdb(databasename,REPAIR_ALLOW_DATA_LOSS)dbcccheckdb(databasename,REPAIR_REB

31、UILD)exec sp_dboption databasename, N'single', N'false'- 将目标数据库置为多用户状态然后执行DBCC CHECKDB需要修复的数据库实体的名称')检查数据库是否仍旧存在错 误。注意：修复后可能会造成部分数据的丢失。2. DBCC CHECKTABLE如果DBCC CHECKDB检查仍旧存在错误，可以使用DBCC CHECKTABL来修复。use 需要修复的数据库实体的名称 declare dbname varchar(255)set dbname=' 需 要 修 复的 数 据库 实 体的 名

32、 称 ' exec sp_dboption dbname,'single user','true'dbcc checktable('需要修复的数据表的名称,REPAIR_ALLOW_DATA_LOSS)dbcc checktable(' 需要修复的数据表的名称 ',REPAIR_REBUILD)把' 需要修复的数据表的名称更改为执行DBCC CHECKD时报错的数据表的名称exec sp_dboption dbname,'single user','false'3. 其他的一些常用的修复命令

33、DBCC DBREINDEX重建指定数据库中表的一个或多个索引用法：DBCC DBREINDEX(表名,'') 修复此表所有的索引。SQL SERVER数据库的检测及修复方法数据库文件被频繁地使用，由于某些原因，数据 库有可能被损坏，本文将针对这种情况的数据库检测及修复方法做一简单讲解。1.1 SQLSERVER数据库的检测SQL SERVE提供了数据库检测的命令， 可用DBCC CHECKD对数据库中各个对象的分配 及结构的正确性进行检测， 并可通过一参数控制， 将所有的错误信息显示出来。 其语法如下： DBCC CHECKDB('database_name'

34、 ,NOINDEX | REPAIR_ALLOW_DATA_LOSS| REPAIR_FAST |REPAIR_REBUILD )WITH ALL_ERRORMSGS | NO_INFOMSGS参数说明：'database_name'代表被检测的数据库实体名；NOINDEX指非系统表的非聚族索引不检测；REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REB指直接修复发现的错误，其 中REPAIR_ALLOW_DATA_LOSS表，若此错误不能修复时，系统将直接删除相关数据。带此 三个参数的任一个时，数据库必须处于单用户模式，可在 Ent

35、erprise Manager 中的数据库属 性中设置； ALL_ERRORMSG代表将检测到的错误信息全部显示出来，否则，对于每张表最 多只显示200条错误信息；NO_INFOMSGS代表隐藏所有的信息及占用空间的报告。经过检测，对于错误的对象，将以OBJECT ID勺形式报告具体出错的信息，可根据OBJECTID到系统表sysobjects中查找到相关的表，即 NAME。1.2 SQL SERVE问题数据库的修复经过数据库检测后， 可针对出现的问题采取相应的措施进行处理。 如通过检测后， 发现 对象的物理存放存在问题，可用DBCC CHECKALLC来进行修复：DBCC CHECKALLO

36、C('database_name' | REPAIR_REBUILD ) WITH ALL_ERRORMSGS| NO_INFOMSGS若是非系统对象的索引出错，则可用DBCC DBREINDEX进行修复：DBCC DBREINDEX( 'database.owner.table_name' , index_name , fillfactor ) WITH NO_INFOMSGS 以上两种情况也可直接使用DBCCCHECKDB( db_name',repair_rebuild) 来修复。另外一种情况是在进行检测时， 提示无法建立数据连接， 此时表明， 数

37、据库已损坏。 对 于这种情况，我们可采取如下措施来尝试修复。首先，在企业管理器中新建一数据库(如数据库名为test );建好数据库后，停止SQL服务器，并将客户数据库的 MDF文件更名为test_data.mdf( 即新建数据库的主文件名 )，然后用更名后的文件覆盖新建数据库同名文件，接着，启动SQL服务器。对Master数据库将系统表设置为可更改状态Use Master Gosp_configure 'allow updates', 1 reconfigure with override Go 将数据库设为紧急状态：update sysdatabases set status

38、 = 32768 where dbid=DB_ID('test')停止并重新启动 SQL服务器，并重建 Log文件：DBCC TRACEON (3604)DBCC REBUILD_LOG(' test ','test _log_ldf')将数据库设置为单用户模式，然后进行检测：sp_dboption ' test ', 'single user', 'true'DBCC CHECKDB(' test ') Go此数据库执行CHECKDB的过程中发现一些表的索引被破坏，于是针对具体的表

39、进行重建索弓I的操作：DBCC DBREINDEX表名)如执行以上操作仍然不能解决， 若索引破坏的表是临时表或不是关键表， 则可从新建账 套中引入，若是主表，则可能通过近期的备份来(部份)恢复。若没有一个备份，则无法修 复。1.3 SQL Server数据库为什么易损坏呢？以下是微软提供的一些可能引起数据库损坏的原因及一些预防措施：操作问题，包括冷起动机器、热拔硬盘、删除一些数据库文件； 硬件问题，包括磁盘控制器的问题； 操作系统问题，包括与系统相关的一些致命错误。1.4 预防措施：1、定期/不定期执行CHKDSK不带参数),以检测硬盘物理结构并修复一些CHKDSK报告的问题； 2、常备份数据

40、。1.5 应用数据库修复举例declare databasename varchar(255)set databasename='AIS20021224170730' 一定要手工输入 执行一般性修复还存在问题时 ,进行允许数据丢失的修复 许数据丢失的修复要求在单用户下进行,此时请退出中间层，客户端,sql的其他模块-所有功能退出 , 在查询分析器 master 里设置数据库为单用户exec sp_dboption databasename, N'single', N'true'在查询分析器 master 里 , 进行修复数据库dbcccheckd

41、b(databasename,REPAIR_ALLOW_DATA_LOSS)dbcccheckdb(databasename,REPAIR_REBUILD) 还- 原数据库状态exec sp_dboption databasename, N'single', N'false'第 2 章数据库日志损坏的修复请遵照如下步骤来试图重建数据库事务日志 .注意 : 由于事务日志丢失 , 数据库可能有没有提交的数据 . 注:都要替换成真实的数据库名字-1 我们使用默认方式建立一个供恢复使用的数据库(如pos)。可以在 SQL ServerEnterprise Manager 里面建立。 -2停掉数据库服务器。-3将刚才生成的数据库的日志文件pos_log.ldf 删除，用要恢复的数据库 mdf 文件覆盖刚才生成的数据库数据文件 pos_data.mdf 。 -4启动数据库服务器。此时会看到数据库 pos 的状态为“置疑” 。这时候不能对此数据库进行任何操作。-5设置数据库允许直接操作系统表。此操作