MDF损坏或LDF文件损坏
MDF损坏或LDF损坏
MDF丢失或LDF丢失
注意，这些情况必须要相同版本的sql server才能操作成功
【1】当MDF损坏时
参考：备份结尾日志
http://www.cnblogs.com/gered/p/8964424.html
【1.1】实例还可以访问
直接
backup log TEST to DISK = N'D:\Backuplog\logbackup.trn' with no_truncate
【1.2】实例崩溃不可访问（替换法）
场景：在10号 20:00 对 TEST 库 执行了完全备份，在11号 8:00 时，mdf文件损坏 目标：不丢失 10号 20:00 到 11号 8:00 这段时间内的数据 前提：数据库恢复模式为完整模式 几个信息点： 1、在完全恢复模式下，完全备份不会截断LOG，只有备份LOG，才会截断 2、日志链从数据库的完整备份开始 3、恢复点，恢复点可以是上次日志备份的结尾，也可以是任何日志备份中的特定恢复点 4、最终的目的是要做一次未补日志备份 1、停止MSSQLSERVER 服务，保存好LDF文件 2、启动MSSQLSERVER 服务，尝试删除 TEST 库，可能会失败，如果失败，就还原上一次的完全备份，选项勾上 覆盖 3、停止MSSQLSERVER 服务，将保存下来的LDF文件 覆盖 到现在的LDF文件 4、启动MSSQLSERVER 服务，尝试访问TEST库，发现提示 不可访问，且数据库状态为恢复挂起，由于MDF文件与LDF文件的LSN匹配不上，切换到MASTER库，执行尾部日志备份 语句: backup log TEST to DISK = N'D:\Backuplog\logbackup.trn' with no_truncate (注意必须要加 no_truncate 选项，不然无法进行尾部日志备份,路径自己创建，自己改) 5、再次覆盖还原 TEST 的完整备份，勾上 with norecovery 选项，还原后数据库状态为 正在还原 6、还原尾部日志备份，选择时间点
【2】当LDF损坏时
（参考：http://www.cnblogs.com/CareySon/archive/2013/06/16/3138742.html）
【2.0】数据库还在线，只是状态不是online，重做日志文件
--设置库为紧急状态 alter database [11test] set emergency --设置为单用户模式 alter database [11test] set single_user use master; GO alter database [11test] set Emergency; GO exec sp_dboption [11test], single, true GO --重建数据库日志文件 alter database [11test] Rebuild Log on (name=FightWarPkDb,filename='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\11test_log.LDF') GO alter database [11test] set multi_user
【2.1】数据库正常关闭，日志损坏
（推荐方法1）
（1）重建日志
当数据库正常关闭时，日志损坏就不是那么重要了，因为此时数据库中所有提交的事务对应的脏数据都已经CheckPoint到物理磁盘，因此不存在数据不一致的问题。因此，如果MDF和NDF文件完好，直接指定 FOR ATTACH_REBUILD_LOG参数后附加即可，如图所示。
但值得注意的是，使用该方式附加数据库会自动重建日志文件，日志文件大小为0.5MB，也就是2个VLF，自动增长为10%，因此您需要手动再来设置一下日志的大小，避免出现太多VLF的情况。
--如果数据库不存在了，只有MDF create database CQXX5000 on ( filename = 'D:\CQXX5000_Data.MDF') for attach_rebuild_log --如果数据库存在（这个经过实践） alter database DataSource_Data set emergency go --置数据库为单用户模式 alter database DataSource_Data set single_user with rollback immediate go use master go --重建数据库日志文件 alter database DataSource_Data Rebuild Log on (name=DataSource_Data_log,filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataSource_Data.LDF') go --最后设置数据库为多用户模式。 alter database DataSource_Data set multi_user
【2.2】替换法
【】新建一个同名数据库
【】下线（或关掉引擎服务），然后删掉新建同名数据库的MDF，把出问题的数据库MDF文件替换
【】上线/重启引擎服务
【3】数据库非正常关闭，导致脏数据丢失
（推荐使用方法2）
方案 1：重建日志
只有一个mdf文件如何修复 1.首先新建一个同名数据库，注意数据库的版本一定要一直，2005的库文件在2008下无论如何也修不好 2.停止sql服务，拷贝mdf文件到新建数据库的目录替换原本的mdf文件，并且删掉ldf文件 3.启动sql服务，执行如下操作 alter database DataSource_Data set emergency go --置数据库为单用户模式 alter database DataSource_Data set single_user with rollback immediate go use master go --重建数据库日志文件 alter database DataSource_Data Rebuild Log on (name=DataSource_Data_log,filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataSource_Data.LDF') go --最后设置数据库为多用户模式。 alter database DataSource_Data set multi_user
方案 2：替换法
只有一个mdf文件如何修复
1.首先新建一个同名数据库，注意数据库的版本一定要一直，2005的库文件在2008下无论如何也修不好
2.停止sql服务，拷贝mdf文件到新建数据库的目录替换原本的mdf文件
3.启动sql服务，执行如下操作
create database CQXX5000 on ( name = 'CQXX5000_data' ,filename = 'D:\CQXX5000_Data.MDF' ) LOG ON ( NAME = N'CQXX5000_log', FILENAME = N'D:\CQXX5000_log.lDF' , SIZE = 1024KB , FILEGROWTH = 10%) alter database CQXX5000 set emergency alter database cqxx5000 set single_user with rollback immediate dbcc checkdb('cqxx5000',REPAIR_ALLOW_DATA_LOSS) alter database CQXX5000 set multi_user ALTER DATABASE CQXX5000 SET online
【3】深入附加MDF（实践）
（3.1）本机MDF附加
由于种种原因，我们如果当时仅仅备份了mdf文件，那么恢复起来就是一件很麻烦的事情了。
如果您的mdf文件是当前数据库产生的，那么很侥幸，也许你使用sp_attach_db或者sp_attach_single_file_db可以恢复数据库，但是会出现类似下面的提示信息
设备激活错误。物理文件名 'C:/Program Files/Microsoft SQL Server/MSSQL/data/test_Log.LDF' 可能有误。
已创建名为 'C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.LDF' 的新日志文件。
（3.2）跨机器实例 mdf 附加
但是，如果您的数据库文件是从其他计算机上复制过来的，那么很不幸，也许上述办法就行不通了。你也许会得到类似下面的错误信息
服务器: 消息 1813，级别 16，状态 2，行 1
未能打开新数据库 'test'。CREATE DATABASE 将终止。
设备激活错误。物理文件名 'd:/test_log.LDF' 可能有误。
怎么办呢？别着急，下面我们举例说明恢复办法。
A．我们使用默认方式建立一个供恢复使用的数据库(如test)。可以在SQL Server Enterprise Manager里面建立。
B．停掉数据库服务器。
C．将刚才生成的数据库的日志文件test_log.ldf删除，用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。
D．启动数据库服务器。此时会看到数据库test的状态为“置疑”。这时候不能对此数据库进行任何操作。
E．设置数据库允许直接操作系统表。此操作可以在SQL Server Enterprise Manager里面选择数据库服务器，按右键，选择“属性”，在“服务器设置”页面中将“允许对系统目录直接修改”一项选中。也可以使用如下语句来实现。
use master
go
sp_configure 'allow updates',1
go
reconfigure with override
go
F．设置test为紧急修复模式
update sysdatabases set status=-32768 where dbid=DB_ID('test')
此时可以在SQL Server Enterprise Manager里面看到该数据库处于“只读/置疑/脱机/紧急模式”可以看到数据库里面的表，但是仅仅有系统表
G．下面执行真正的恢复操作，重建数据库日志文件
dbcc rebuild_log('test','C:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.ldf')
执行过程中，如果遇到下列提示信息：
服务器: 消息 5030，级别 16，状态 1，行 1
未能排它地锁定数据库以执行该操作。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
说明您的其他程序正在使用该数据库，如果刚才您在F步骤中使用SQL Server Enterprise Manager打开了test库的系统表，那么退出SQL Server Enterprise Manager就可以了。
正确执行完成的提示应该类似于：
警告: 数据库 'test' 的日志已重建。已失去事务的一致性。应运行 DBCC CHECKDB 以验证物理一致性。将必须重置数据库选项，并且可能需要删除多余的日志文件。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
此时打开在SQL Server Enterprise Manager里面会看到数据库的状态为“只供DBO使用”。此时可以访问数据库里面的用户表了。
H．验证数据库一致性（可省略）
dbcc checkdb('test')
一般执行结果如下：
CHECKDB 发现了 0 个分配错误和 0 个一致性错误（在数据库 'test' 中）。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
I．设置数据库为正常状态
sp_dboption 'test','dbo use only','false'
如果没有出错，那么恭喜，现在就可以正常的使用恢复后的数据库啦。
J．最后一步，我们要将步骤E中设置的“允许对系统目录直接修改”一项恢复。因为平时直接操作系统表是一件比较危险的事情。当然，我们可以在SQL Server Enterprise Manager里面恢复，也可以使用如下语句完成
sp_configure 'allow updates',0
go
reconfigure with override
go
说到底还是这几个语句起了作用：(ExamMIS==test)
update sysdatabases set status=-32768 where dbid=DB_ID('ExamMIS') dbcc rebuild_log('ExamMIS','C:/Program Files/Microsoft SQL Server/MSSQL/Data/ExamMIS_log.ldf') dbcc checkdb('ExamMIS') sp_dboption 'ExamMIS','dbo use only','false'
【实践案例参考】
以下部分转自： http://blog.51cto.com/jimshu/1341289
在备份与恢复数据库时，偶尔使用分离/附加的方法。如果在附加时丢失了或者删除了日志文件（LDF），可能会有哪些风险呢？下面通过实验来验证。
（1）搭建环境
1. 创建数据库
CREATE DATABASE [db01] ON PRIMARY ( NAME = N'db01', FILENAME = N'C:\SQLDATA\db01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'db01_log', FILENAME = N'C:\SQLDATA\db01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
2. 创建表
USE db01 CREATE TABLE [dbo].[Table01]( [IntID] [int] NULL, [CharFill] [varchar](50) NULL )
（2）使用nowait选项停止SQL Server实例（服务）造成数据丢失？
1. 添加2条记录
USE db01 insert Table01 values(1,'abcd') CHECKPOINT insert Table01 values(2,'hijk') select * from Table01
查询添加的结果，确认上述2条记录已经添加到数据库。
区别是：第1条记录后面有一个检查点，此时这条记录已经被回写到MDF文件，而第2条记录还在data cache pool，等待下一个检查点才会写入MDF文件。
2. 使用nowait选项停止SQL Server实例（服务）
SHUTDOWN WITH NOWAIT
3. 转移文件后启动SQL Server服务
删除LDF文件，再将MDF文件（这个文件我们称之为“A文件”）移动到另一个文件夹
再启动SQL Server服务，然后删除db01数据库。
4. 附加时删除LDF的链接信息
附加时，由于找不到LDF文件，会显示“找不到”的信息。删除它，让系统重新创建一个LDF文件。
5. 附加数据库时报错
继续附加数据库，出现报错信息。
6. 修复数据库（使用替换法）
(1) 新建db01数据库
CREATE DATABASE [db01] ON PRIMARY ( NAME = N'db01', FILENAME = N'C:\SQLDATA\db01.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'db01_log', FILENAME = N'C:\SQLDATA\db01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
（2） 替换MDF文件
停止SQL Server服务，把上一步新建的MDF文件删除。再把最初的MDF文件（即前面所称的“A文件”）转移回来。（即用“A文件”替换上一步新建的MDF文件）。
（3）重启SQL Server服务
重启之后，db01数据库为“可疑”状态，如果直接访问这个数据库则会报错“无法访问数据库db01。”
执行以下命令，修复数据库。
alter database db01 set emergency alter database db01 set single_user dbcc checkdb('db01',REPAIR_ALLOW_DATA_LOSS) dbcc checkdb('db01',REPAIR_REBUILD) alter database db01 set multi_user
（4）检查数据
use db01 select * from Table01
执行上述检查，发现只有第1条记录，丢失了第2条记录。假设这是一家银行的取款操作数据库，由于数据库shutdown with nowait并且LDF文件损坏，你的提款记录就不见了。多爽啊！
结论：
SQL Server为了加快关机的速度，允许使用NOWAIT选项。此选项将跳过检查点操作，导致部分数据未回写到MDF文件（仅记录在LDF中）。在这种情况下，如果丢失了LDF文件，尽管可以修复数据库，却会有数据丢失。
（3）未提交的事务导致不能回滚？
1. 创建事务
使用上一步的数据库，添加一个事务。
BEGIN TRAN T1 insert Table01 values(3,'lmn')
2. 停止SQL Server
使用“SQL Server配置管理器”停止SQL Server。
3. 转移MDF文件
参考前面的实验，把MDF文件转转移到另一个文件夹，并删除LDF文件。
4. 修复数据库（替换法）
参考前面的实验，修复数据库
5. 检查数据
参考前面的实验，查看修复后的数据。
你将发现第3条记录已经提交（尽管它属于一个未提交的事务）！
假如这是一家银行的存款操作数据库，重启数据库以后发现LDF坏了，即使你的存款操作最后撤销了，可是数据库里显示你的存款操作已经提交（存款成功）。多爽啊！
结论：
本实验是正常shutdown，所以第3条记录遇到检查点操作而被回写到磁盘的MDF文件，然后事务日志中记录了这条insert操作需要回滚（因为这个事务未提交）。
由于LDF文件已丢失，导致数据库启动时不能回滚所有未提交的事务。
（4）结论
丢失了数据库的事务日志文件，最多只能恢复到最后一个检查点。但是：
1. 在最后一个检查点之后，data cache pool中修改过的数据，将全部丢失。
2. 事务日志中未提交的事务，将无法撤销。
