如何恢复SQL Server 2000损坏的数据库文件

SQL Server2000中，如果数据库文件（非系统数据库文件）遇到错误的时候，我们该怎么办。以下是笔者以前的笔记。仅适用于非master,msdb的数据库。

说明如下：

1 建一个测试数据库test(数据库类型为完全)
2 建一个表，插入点记录

create table a(c1 varchar(2))
go
insert into a values(#39;aa#39;)
go
insert into a values(#39;bb#39;)
go

3 作完全备份，到文件test_1.bak
4 在作一点修改

insert into a values(#39;cc#39;)
go
create table b(c1 int)
go
insert into b values(1)
go
insert into b values(2)
go

5 shutdown 数据库服务器
6 用ultraedit编辑数据库文件test_data.mdf,随便修改点字节内容,相当于数据库遭到致命的损坏。
7 启动数据库，并且运行企业管理器，点开数据库，看到test变成灰色，而且显示置疑。
8 运行isql -SLocalhost -Usa -P
1> backup log test TO DISK=#39;D:Program FilesMicrosoft SQL ServerMSSQLBACKUP
est_2.bak#39; WITH NO_TRUNCATE
2>go

已处理 2 页，这些页属于数据库 #39;test#39; 的文件 #39;TEST_Log#39;（位于文件 1 上）。
BACKUP LOG 操作成功地处理了 2 页，花费了 0.111 秒（0.087 MB/秒）。

9 进行恢复最老的完全备份

1> RESTORE DATABASE test FROM DISK=#39;D:Program FilesMicrosoft SQL ServerMSSQL
BACKUP est_1.bak#39; WITH NORECOVERY
2> go

已处理 96 页，这些页属于数据库 #39;test#39; 的文件 #39;TEST_Data#39;（位于文件 1 上）。
已处理 1 页，这些页属于数据库 #39;test#39; 的文件 #39;TEST_Log#39;（位于文件 1 上）。
RESTORE DATABASE 操作成功地处理了 97 页，花费了 0.107 秒（7.368 MB/秒）。

10 恢复最近的日志

1> RESTORE LOG test FROM DISK=#39;D:Program FilesMicrosoft SQL ServerMSSQLBACKU
P est_2.bak#39; WITH RECOVERY
2> go

已处理 2 页，这些页属于数据库 #39;test#39; 的文件 #39;TEST_Log#39;（位于文件 1 上）。
RESTORE LOG 操作成功地处理了 2 页，花费了 0.056 秒（0.173 MB/秒）。

数据已经完全恢复了，可以使用了。

select * from a
go

总结，DBA应该有一个完善的数据库备份计划。本例中，如果没有一个完全备份的话，数据库的恢复就不可能

当sql server数据库崩溃时如何恢复？

　　任何数据库系统都无法避免崩溃的状况，即使你使用了clustered，双机热备……仍然无法完全根除系统中的单点故障，何况对于大部分用户来说，无法承受这样昂贵的硬件投资。所以，在系统崩溃的时候，如何恢复原有的宝贵数据就成为一个极其重要的问题了。

　　在恢复的时候，最理想的情况就是你的数据文件和日志文件都完好无损了，这样只需要sp_attach_db，把数据文件附加到新的数据库上即可，或者在停机的时候把所有数据文件(一定要有master等)都copy到原有路径下也行，不过一般不推荐这样的做法，sp_attach_db比较好，虽然麻烦许多。

　　但是呢，一般数据库崩溃的时候系统是未必能有时间把未完成的事务和脏页等写入磁盘的，这样的情况sp_attach_db就会失败。那么，寄期望于dba制定了一个良好的灾难恢复计划吧。按照你的恢复计划，还原最新的完全备份，增量备份或者事务日志备份，然后如果你的活动事务日志还能读得出来的话，恭喜你！你可以还原到崩溃前的状态。

　　一般的单位都是没有专职的dba的，如果没有可用的备份，更可能是最近一次备份的时间过于久远而导致不可接受的数据损失，而且你的活动事务日志也处于不可用的状态，那就是最麻烦的情况了。

　　不幸的很的是，一般数据库崩溃都是由于存储子系统引起的，而这样的情况是几乎不可能有可用的日志用于恢复的。那么就只好试一下这些方案了。当然，是要求至少你的数据文件是存在的，要是数据文件、日志文件和备份都没有了的话，别找我，你可以到楼顶上去唱“神啊，救救我吧”。

　　首先，你可以试一下sp_attach_single_file_db，试着恢复一下你的数据文件，虽然能恢复的可能性不大，不过假如这个数据库刚好执行了一个checkpoint的话，还是有可能成功的。

　　如果你没有好到有摸彩票的手气，最重要的数据库没有像你期盼的那样attach上去，不要气馁，还是有别的方案的。

　　我们可以试着重新建立一个log，先把数据库设置为emergency mode，sysdatabases的status为32768 就表示数据库处于此状态。

　　不过系统表是不能随便改的，设置一下先

　　use master
　　go
　　sp_configure #39;allow updates#39;, 1
　　reconfigure with override
　　go

　　然后
　　update sysdatabases set status = 32768 where name = #39;#39;
　　现在，祈求满天神佛的保佑吧，重新建立一个log文件。成功的机会还是相当大的，系统一般都会认可你新建立的日志。如果没有报告什么错误，现在就可以松一口气了。

　　虽然数据是恢复了，可是别以为事情就算完成了，正在进行的事务肯定是丢失了，原来的数据也可能受到一些损坏。

　　先把sql server 重新启动一下，然后检查你的数据库吧。
　　先设置成单用户模式，然后做dbcc

　　sp_dboption #39;#39;, #39;single user#39;, #39;true#39;
　　dbcc checkdb(#39;#39;)

　　如果没有什么大问题就可以把数据库状态改回去了，记得别忘了把系统表的修改选项关掉。

　　update sysdatabases set status = 28 where name = #39;#39; --当然你的数据库状态可能不是这个，自己改为合适的值吧。也可以用sp_resetstatus
　　go
　　sp_configure #39;allow updates#39;, 0
　　reconfigure with override
　　go

　　checkdb的时候可能报告有一些错误，这些错误的数据你可能就只好丢弃了。
　　checkdb有几种修复选项，自己看着用吧，不过最后你可能还是得用repair_allow_data_loss，完成所有修复。
　　chekcdb并不能完成所有的修复，我们需要更进一步的修复，用dbcc checktable对每一个表做检查吧。

　　表的列表可以用sysobjects里面得到，把objectproperty是istable的全部找出来检查一下吧，这样能够基本上解决问题了，如果还报告错误，试着把数据select into到另一张表检查一下。
　　这些都做完了之后，把所有索引、视图、存储过程、触发器等重新建立一下。dbcc dbreindex也许可以帮你一些忙。

数据库日志文件丢失时的恢复步骤，描述我误删除了数据库的事务日志文件(.ldf)之后，如何经过各种尝试恢复数据库的。

但是不少网友在处理“数据库置疑”的实践过程中，又产生了许多新的疑问。
我还是总结一下出现的几种情况，以供参考。

2.Zach的灵验脚本

Zach说他每次遇到这种数据库置疑情况，就运行下面这个脚本，屡试不爽：
======================================================
--before running any script, run the following to set the
master database to allow updates
USE master
GO
sp_configure #39;allow updates#39;, 1
GO
RECONFIGURE WITH OVERRIDE
GO

--Run the following script
UPDATE master..sysdatabases SET status = status ^ 256
WHERE name = #39;Database_Name#39;

--Run the following script
exec SP_resetstatus Database_Name

--stop and start the MSDTC at this stage

--After the procedure is created, immediately disable
updates to the system tables:
exec sp_configure #39;allow updates#39;, 0
GO
RECONFIGURE WITH OVERRIDE
GO
=====================================

从上面可以看出，处理置疑的基本步骤还是我那篇文章中说的(注意我使用的字体颜色)：
执行 sp_configure 以允许对系统表进行更新，然后用 RECONFIGURE WITH OVERRIDE 语句强制实施该配置；
数据库重置紧急模式；
执行sp_resetstatus关闭数据库的置疑标志，但是原封不动地保持数据库的其它选项（只有系统管理员才能执行）。执行该过程后，立即重启 SQL Server服务；
执行 sp_configure 以禁止对系统表进行更新，然后用 RECONFIGURE WITH OVERRIDE 语句强制实施该配置。

status ^ 256的意思就是：
Constant  Value  Description
SQLDMODBStat_Suspect  256  Database integrity is suspect for the referenced database.

不同的是，有时候丢失了数据库日志文件，额外需要以下步骤：
 把应用数据库设置为Single User模式；
 做DBCC CHECKDB；
才可以。

但是几位网友的实践结果就是这个DBCC CHECKDB执行失败。一位网友yang说：“但是 DBCC CHECKDB就是执行不了，总是说“该数据库处于回避恢复模式”。我已经试了很多次了，就是改变不了这个状态。”
还有一位Rui执行DBCC CHECKDB时报错：“Server: Msg 943, Level 14, State 1, Line 1 Database #39;his_yb#39; cannot be opened because its version (539) is later than the current server version (515).”

对于Yang，可能他没有一步一步做，。我的切身体会是，把应用数据库设置为Single User模式后就可以做DBCC CHECKDB。之后呢，也许SQL Server重启后自动检查数据库是否正常。但是数据应该是可以读出来的，至少可以被DTS Wizard读出来的。这时候的数据库还存在问题，比如我的组件使用数据库时，报告说：“发生错误：-2147467259,未能在数据库 #39;XXX#39; 中运行 BEGIN TRANSACTION，因为该数据库处于回避恢复模式。”

对于Rui，他碰到的那个错误
Server: Msg 943, Level 14, State 1, Line 2
Database #39;XXXX#39; cannot be opened because its version (536) is later than
the current server version (515).
这表明Rui正试图：
从一个SQL Server 2000(version 539,536之类的)的数据库备份恢复到一个SQL Server 7.0中
或者
把一个SQL Server 2000(version 539,536之类的)的数据库attach到一个SQL Server 7.0中，
这是不允许的。如果你必须使用这个SQL Server 2000的数据备份，那么请您首先把这个备份倒入SQL Server 2000，最后用DTS把数据库从SQL Server 2000上transfer到SQL Server 7.0上。