2022年SQL数据库修复总汇 .pdf

《2022年SQL数据库修复总汇 .pdf》由会员分享，可在线阅读，更多相关《2022年SQL数据库修复总汇 .pdf（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、t log record ID(274:377:2).数据库可以分离，但分离后无法附加，附加时出现“823”号错误。-微软公司 SQL联机从书解释:错误 823 严重级别 24 消息正文在文件%4!的偏移量%3!处的%2!过程中，检测到 I/O 错误%1!。解释Microsoft?SQL Server?在对某设备进行读或写请求时遇到 I/O 错误。该错误通常表明磁盘问题。但是，错误日志中在错误 823 之前记录的其它核心消息应指出涉及了哪个设备。对策检查该设备的可访问性和状态。如果可能，执行硬件诊断并纠正问题。从最新的数据库备份还原损坏的文件。从数据库备份中还原应始终是修复已损坏数据库的首选方

3、法。如果没有备份或者检测到的错误是孤立的，则 DBCC CHECKDB 的修复功能可能很有用。然而，比起从备份中还原损坏的文件，可能使用 DBCC CHECKDB 消耗的时间更多，且可能无法恢复全部数据。注意?如果使用修复子句运行 DBCC CHECKDB 时，问题没有得到纠正，或者不知道该过程将如何影响数据，请与主要的支持提供者联系。出错原因:名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 20 页 -通常这个问题是由于硬盘空间不够/硬盘读写错误/忽然断电(停电/死机),SQL系统异常。1.日志文件被破坏 823 错误-日志文件被破坏的数据库文件，通过如下方法附加上去后，数据库

4、里所有的表都不能访问，提示错误832，请问要如何解决？use master go sp_configure allow updates,1 go reconfigure with override go/*注意输正确，如果输入后执行此语句，并且下面显示DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。已将配置选项 allow updates 从 0 改为 1。请运行 RECONFIGURE 语句以安装。说明执行正确，如果不显示以上信息，请检查是否有输错！此语句是的作用是：设置数据库允许直接操作系统表。*/update sysdatabases set status=-32

5、768 where dbid=DB_ID(icyqshsf)/*设置数据库为紧急修复模式。*/go dbcc rebuild_log(icyqshsf,e:Program FilesMicrosoft SQL ServerMSSQLDataicyqshsf_log.ldf)/*重新数据库日志(ldf)文件。下面显示:警告:数据库 test 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。*/go dbcc checkdb(icyqshsf)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 2

6、0 页 -/*现在检查有没有错误，再输入语法下面显示CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 tiger 中）。那说明第 6 步就建立成功没问题了，下面就可以把SQL恢复模式了*/go sp_dboption icyqshsf,dbo use only,false go sp_configure allow updates,0 go reconfigure with override go-2.附加数据库文件时,提示 823 错误-EXEC sp_configure allow updates,1 RECONFIGURE WITH OVERRIDE/*打开修改系统表

7、的开关 */update sysdatabases set status=32768 where name=数据库名 DBCC REBUILD_LOG(数据库名,E:dzzdatabase dzz1204_Log.LDF)update sysdatabases set status=0 where name=数据库名 restore database 数据库名 WITH RECOVERY EXEC sp_configure allow updates,0 RECONFIGURE WITH OVERRIDE/*关闭打开修改系统表的开关 */3 因为停电等原因造成MSSQL 数据库,提示 823错

8、误-USE MASTER GO sp_dboption databaseName,single user,true Go DBCC CHECKDB(databaseName,REPAIR_REBUILD)Go USE databaseName go exec sp_msforeachtable DBCC CHECKTABLE(?,REPAIR_REBUILD)go sp_dboption databaseName,single user,false Go 名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 20 页 -如果还不行，可以采用允许丢失数据的方式修复，如下：USE MAST

9、ER GO sp_dboption databaseName,single user,true Go DBCC CHECKDB(databaseName,REPAIR_ALLOW_DATA_LOSS)Go USE databaseName go exec sp_msforeachtable DBCC CHECKTABLE(?,REPAIR_REBUILD)go sp_dboption databaseName,single user,false Go=修复方法恢复语句:-drop database wgy-create database wgy-RESTORE FILELISTONLY fro

10、m disk=F:data1218wgy.db ALTER DATABASE wgy SET OFFLINE WITH ROLLBACK IMMEDIATE restore database wgy from disk=F:data1218wgy.db with replace,MOVE Syb_Data TO D:DataNPDFDN.mdf,MOVE Syb_Log TO D:DataNPDFDN.ldf ALTER DATABASE wgy SET ONLINE WITH ROLLBACK IMMEDIATE 方法一：1.新建一个同名的数据库2.再停掉 sql server 3.用 md

11、f 和 ldf数据库的文件覆盖掉这个新建的同名数据库4.再重启 sql server 5.此时打开企业管理器时新建的同名数据库会出现置疑，先不管，执行下面的语句（注意修改其中的数据库名)USE MASTER GO SP_CONFIGURE ALLOW UpdateS,1 RECONFIGURE WITH OVERRIDE GO 名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 20 页 -Update SYSDATABASES SET STATUS=32768 Where NAME=aaa Go sp_dboption aaa,single user,true Go DBCC CH

12、ECKDB(aaa)Go update sysdatabases set status=28 where name=aaa Go sp_configure allow updates,0 reconfigure with override Go sp_dboption aaa,single user,true Go 6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了.方法二：1.新建一个同名的数据库2.再停掉 sql server 3.用 mdf 和 ldf数据库的文件覆盖掉这个新建的同名数据库4.再重启

13、 sql server 5.此时打开企业管理器时新建的同名数据库会出现置疑，先不管，执行下面的语句（注意修改其中的数据库名)USE MASTER GO SP_CONFIGURE ALLOW UpdateS,1 RECONFIGURE WITH OVERRIDE GO Update SYSDATABASES SET STATUS=32768 Where NAME=aaa Go sp_dboption aaa,single user,true Go DBCC REBUILD_LOG(aaa,C:Program FilesMicrosoft SQL ServerMSSQLDataaaa_Log2.L

14、DF)Go update sysdatabases set status=0 where name=aaa Go restore database aaa WITH RECOVERY -如果表有错的话用 DBCC CHECKTABLE来进行修复go DBCC CHECKDB(aaa)名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 20 页 -Go update sysdatabases set status=28 where name=aaa Go sp_configure allow updates,0 reconfigure with override Go sp_dbopt

15、ion aaa,single user,true Go 6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用数据库的脚本创建一个新的数据库,并将数据导进去就行了.方法三：.非置疑数据库修复技术:USE MASTER GO sp_dboption aaa,single user,true Go-无损修复DBCC CHECKDB(aaa,REPAIR_REBUILD)-有损修复-DBCC CHECKDB(ke_2008,repair_allow_data_loss)WITH TABLOCK-如果表有错的话用 DBCC CHECKTABLE来进行修复Go sp_d

16、boption aaa,single user,false Go 方法四：/*-重置置疑状态系统方法:如果 sql server 因为磁盘驱动器不再有可用空间，而不能完成数据库的恢复，那么 microsoft?sql server?2000 会返回错误 1105 并且将 sysdatabases 中的 status 列设为置疑。按下面的步骤解决这个问题：执行 sp_resetstatus。语法为：sp_resetstatus aaa sp_resetstatus 数据库名 用 alter database 向数据库添加一个数据文件或日志文件。停止并重新启动 sql server。名师资料总结-

17、精品资料欢迎下载-名师精心整理-第 6 页，共 20 页 -用新的数据文件或日志文件所提供的额外空间，sql server 应该能完成数据库的恢复。释放磁盘空间并且重新运行恢复操作。sp_resetstatus 关闭数据库的置疑标志，但是原封不动地保持数据库的其它选项。-*/方法五：-手工重置置疑状态use master go sp_configure allow updates,1 reconfigure with override go declare dbname varchar(30)set dbname=lqgs if trancount 0 print 正在进行事务处理,操作不能进

18、行 else if suser_id()!=1 print 你不是系统管理员(sa),不能进行此操作 else if not exists(select 1 from master.sysdatabases where name=dbname)print 你要操作的数据库不存在 else if not exists(select 1 from master.sysdatabases where name=dbname and status&256=256)print 你的数据库没有被置疑 else begin begin tran update master.sysdatabases set

19、status=status 256 where name=dbname if error!=0 or rowcount!=1 rollback tran else begin commit tran print 操作成功,请重新启动 SQL end end go sp_configure allow updates,1 reconfigure with override go sp_resetstatus lqgs 方法六：名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 20 页 -重置置疑状态如果 SQL Server 因为磁盘驱动器不再有可用空间，而不能完成数据库的恢复，那么

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

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

22、 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)名师资料总结-精品资料欢迎下载-名师精心整理-第

23、8 页，共 20 页 -Drop PROCEDURE sp_resetstatus GO Create PROC sp_resetstatus dbname varchar(30)AS DECLARE msg varchar(80)IF trancount 0 BEGIN PRINT Cant run sp_resetstatus from within a transaction.RETURN(1)END IF suser_id()!=1 BEGIN Select msg=You must be the System Administrator(SA)Select msg=msg+to ex

24、ecute this procedure.RETURN(1)END IF(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.sysdatabases Where name=dbname AND status&256=256)!=1 BEGIN PRINT sp_resetstatus can only be run on

25、 suspect databases.RETURN(1)END BEGIN TRAN Update master.sysdatabases SET status=status 256 Where name=dbname IF error!=0 or rowcount!=1 ROLLBACK TRAN ELSE BEGIN COMMIT TRAN Select msg=Database +dbname+status reset!PRINT msg PRINT PRINT WARNING:You must reboot SQL Server prior to PRINT accessing thi

26、s database!PRINT END 名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 20 页 -MS Sql Server 提供了很多数据库修复的命令，当数据库置疑或是有的无法完成读取时可以尝试这些修复命令。1.DBCC CHECKDB 重启服务器后，在没有进行任何操作的情况下，在SQL查询分析器中执行以下 SQL进行数据库的修复，修复数据库存在的一致性错误与分配错误。use master declare databasename varchar(255)set databasename=需要修复的数据库实体的名称 exec sp_dboption databasename

27、,Nsingle,Ntrue-将目标数据库置为单用户状态dbcc checkdb(databasename,REPAIR_ALLOW_DATA_LOSS)dbcc checkdb(databasename,REPAIR_REBUILD)exec sp_dboption databasename,Nsingle,Nfalse-将目标数据库置为多用户状态然后执行 DBCC CHECKDB(需要修复的数据库实体的名称)检查数据库是否仍旧存在错误。注意：修复后可能会造成部分数据的丢失。2.DBCC CHECKTABLE 如果 DBCC CHECKDB 检查仍旧存在错误，可以使用DBCC CHECKTA

28、BLE来名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 20 页 -修复。use 需要修复的数据库实体的名称declare dbname varchar(255)set dbname=需要修复的数据库实体的名称 exec sp_dboption dbname,single user,true dbcc checktable(需要修复的数据表的名称,REPAIR_ALLOW_DATA_LOSS)dbcc checktable(需要修复的数据表的名称,REPAIR_REBUILD)-把 需要修复的数据表的名称更改为执行DBCC CHECKDB 时报错的数据表的名称exec sp_d

29、boption dbname,single user,false 3.其他的一些常用的修复命令DBCC DBREINDEX 重建指定数据库中表的一个或多个索引用法：DBCC DBREINDEX(表名,)修复此表所有的索引。SQL SERVER 数据库的检测及修复方法名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 20 页 -数据库文件被频繁地使用，由于某些原因，数据库有可能被损坏，本文将针对这种情况的数据库检测及修复方法做一简单讲解。1.1 SQL SERVER 数据库的检测SQL SERVER 提供了数据库检测的命令，可用DBCC CHECKDB对数据库中各个对象的分配及结构

30、的正确性进行检测，并可通过一参数控制，将所有的错误信息显示出来。其语法如下：DBCC CHECKDB (database_name,NOINDEX|REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD )WITH ALL_ERRORMSGS|NO_INFOMSGS 参数说明：database_name 代表被检测的数据库实体名；NOINDEX 指非系统表的非聚族索引不检测；REPAIR_ALLOW_DATA_LOSS|REPAIR_FAST|REPAIR_REBUILD 指直接修复发现的错误，其中REPAIR_ALLOW_DATA_LOSS代表，若

31、此错误不能修复时，系统将直接删除相关数据。带此三个参数的任一个时，数据库必须处于单用户模式，可在Enterprise Manager中的数据库属性中设置；ALL_ERRORMSGS代表将检测到的错误信息全部显示出来，否则，对于每张表最多只显示200 条错误信息；名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 20 页 -NO_INFOMSGS代表隐藏所有的信息及占用空间的报告。经过检测，对于错误的对象，将以OBJECT ID 的形式报告具体出错的信息，可根据 OBJECT ID 到系统表 sysobjects中查找到相关的表，即 NAME。1.2 SQL SERVER 问题数据

32、库的修复经过数据库检测后，可针对出现的问题采取相应的措施进行处理。如通过检测后，发现对象的物理存放存在问题，可用DBCC CHECKALLOC来进行修复：DBCC CHECKALLOC(database_name|REPAIR_REBUILD )WITH ALL_ERRORMSGS|NO_INFOMSGS 若是非系统对象的索引出错，则可用DBCC DBREINDEX进行修复：DBCC DBREINDEX(database.owner.table_name,index_name,fillfactor )WITH NO_INFOMSGS 以上两种情况，也可直接使用DBCC CHECKDB(db_n

33、ame,repair_rebuild)来修复。另外一种情况是在进行检测时，提示无法建立数据连接，此时表明，数据库已损坏。对于这种情况，我们可采取如下措施来尝试修复。首先，在企业管理器中新建一数据库（如数据库名为test）；名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 20 页 -建好数据库后，停止 SQL 服务器，并将客户数据库的MDF 文件更名为 test _data.mdf(即新建数据库的主文件名)，然后用更名后的文件覆盖新建数据库同名文件，接着，启动SQL服务器。对 Master 数据库将系统表设置为可更改状态Use Master Go sp_configure all

34、ow updates,1 reconfigure with override Go 将数据库设为紧急状态：update sysdatabases set status=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的过程中发现一些表的索引被破坏，于是针对具名

35、师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 20 页 -体的表进行重建索引的操作：DBCC DBREINDEX（表名）如执行以上操作仍然不能解决，若索引破坏的表是临时表或不是关键表，则可从新建账套中引入，若是主表，则可能通过近期的备份来（部份）恢复。若没有一个备份，则无法修复。1.3 SQL Server数据库为什么易损坏呢？以下是微软提供的一些可能引起数据库损坏的原因及一些预防措施：操作问题，包括冷起动机器、热拔硬盘、删除一些数据库文件；硬件问题，包括磁盘控制器的问题；操作系统问题，包括与系统相关的一些致命错误。1.4 预防措施：1、定期/不定期执行 CHKDSK(不带参数

36、)，以检测硬盘物理结构并修复一些 CHKDSK 报告的问题；2、常备份数据。1.5 应用数据库修复举例declare databasename varchar(255)set databasename=AIS20021224170730-一定要手工输入-执行一般性修复还存在问题时,进行允许数据丢失的修复-许数据丢失的修复要求在单用户下进行,此时请退出中间层,名师资料总结-精品资料欢迎下载-名师精心整理-第 15 页，共 20 页 -客户端,sql的其他模块-所有功能退出,在查询分析器 master 里设置数据库为单用户exec sp_dboption databasename,Nsingle,

37、Ntrue -在查询分析器master 里,进行修复数据库dbcc checkdb(databasename,REPAIR_ALLOW_DATA_LOSS)dbcc checkdb(databasename,REPAIR_REBUILD)-还原数据库状态exec sp_dboption databasename,Nsingle,Nfalse 第 2 章数据库日志损坏的修复请遵照如下步骤来试图重建数据库事务日志.注意:由于事务日志丢失,数据库可能有没有提交的数据.注:都要替换成真实的数据库名字-1 我们使用默认方式建立一个供恢复使用的数据库(如 pos)。可以在SQL Server Enterp

38、rise Manager里面建立。名师资料总结-精品资料欢迎下载-名师精心整理-第 16 页，共 20 页 -2 停掉数据库服务器。-3 将刚才生成的数据库的日志文件pos_log.ldf删除，用要恢复的数据库 mdf 文件覆盖刚才生成的数据库数据文件pos_data.mdf。-4 启动数据库服务器。此时会看到数据库pos 的状态为“置疑”。这时候不能对此数据库进行任何操作。-5 设 置 数 据 库 允 许 直 接 操 作 系 统 表。此 操 作 可 以 在SQL Server Enterprise Manager里面选择数据库服务器，按右键，选择“属性”，在“服务器设置”页面中将“允许对系统

39、目录直接修改”一项选中。也可以使用如下语句来实现。use master go exec sp_configure allow updates,1 go reconfigure with override go -6 设置 pos 为紧急修复模式update sysdatabases set status=-32768 where dbid=DB_ID(pos)-此时可以在 SQL Server Enterprise Manager里面看到该数据库处于“只读置疑脱机 紧急模式”可以看到数据库里面的表，但是仅仅有系统表-7 下面执行真正的恢复操作，重建数据库日志文件名师资料总结-精品资料欢迎下载-

40、名师精心整理-第 17 页，共 20 页 -go dbcc rebuild_log(pos,D:Program FilesMicrosoft SQL ServerMSSQLDatapos_log.ldf)go-执行过程中，如果遇到下列提示信息：-服务器:消息 5030，级别 16，状态 1，行 1 -未能排它地锁定数据库以执行该操作。-DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。-说明您的其他程序正在使用该数据库，如果刚才您在F 步骤中使用SQL Server Enterprise Manager打开了 pos 库的系统表，那么退出 SQL Server Enter

41、prise Manager就可以了。-正确执行完成的提示应该类似于：-警告:数据库 pos 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。-DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。-此时打开在 SQL Server Enterprise Manager里面会看到数据名师资料总结-精品资料欢迎下载-名师精心整理-第 18 页，共 20 页 -库的状态为“只供DBO 使用”。此时可以访问数据库里面的用户表了。-8 验证数据库一致性（可省略）go dbcc checkdb(p

42、os)-一般执行结果如下：-CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 pos 中）。-DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。-9 设置数据库为正常状态go exec sp_dboption pos,dbo use only,false go-如果没有出错，那么恭喜，现在就可以正常的使用恢复后的数据库啦。-10 最后一步，我们要将步骤 E中设置的“允许对系统目录直接修改”名师资料总结-精品资料欢迎下载-名师精心整理-第 19 页，共 20 页 -一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然，我们可以在 SQL Serve

43、r Enterprise Manager里面恢复，也可以使用如下语句完成exec sp_configure allow updates,0 go reconfigure with override go 利用第三方软件恢复数据库推荐软件：软件名称达思 SQL 数据库修复软件D-Recovery for MS Sql Server 1.0 主页http:/www.d-recovery.org/index.html下载地址http:/ For MS SQL Server 是一款专门针对损坏的MS SQL Server 数据文件（.mdf文件）进行数据恢复的软件，是国内第一款直接读取损坏的MDF 数

44、据文件的商用数据库恢复软件，具备数据表及记录直接恢复到MSSQL Server 数据库的功能。由于某种原因导致数据库质疑、数据文件损坏而无法正常附加到数据库引擎、数据文件能正常附加但查询某些表出现错误等引发的数据丢失，D-Recovery For MS QL Server 可以直接分析和提取这些损坏的数据库文件的正常的数据记录，进而达到最大限度的数据恢复，把用户数据丢失减少到最小范围。它具备如下功能：1、检测 MDF 文件，可以把文件错误页面检测出来并统计错误页面占用整个文件页面的比例。2、打开损坏的MDF 文件，列出所有用户表信息并可以浏览用户表数据记录。3、可以将用户数据表、存储过程等数据导出保存成sql 语句文件。4、可以将用户数据表、存储过程等数据直接导入MSSQL Server 中的某个数据库中。5、可以扫描并提取某些损坏表的所有正常的数据记录，丢弃那些损坏的记录并保存数据。名师资料总结-精品资料欢迎下载-名师精心整理-第 20 页，共 20 页 -