MDF损坏或LDF文件损坏

MDF损坏或LDF损坏

MDF丢失或LDF丢失

注意，这些情况必须要相同版本的sql server才能操作成功

【1】当MDF损坏时

1.备份结尾日志

　　http://www.cnblogs.com/gered/p/8964424.html

【2】当LDF损坏时

（参考：http://www.cnblogs.com/CareySon/archive/2013/06/16/3138742.html）

　　（1）重做日志文件

--设置库为紧急状态

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参数后附加即可，如图所示。

　　 MDF损坏或LDF文件损坏

但值得注意的是，使用该方式附加数据库会自动重建日志文件，日志文件大小为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

一下部分转自： http://blog.51cto.com/jimshu/1341289

　在备份与恢复数据库时，偶尔使用分离/附加的方法。如果在附加时丢失了或者删除了日志文件（LDF），可能会有哪些风险呢？下面通过实验来验证。

【4】实践参考

一、搭建环境

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 )

二、使用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文件。

MDF损坏或LDF文件损坏

5. 附加数据库时报错

　　继续附加数据库，出现报错信息。

MDF损坏或LDF文件损坏

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文件，尽管可以修复数据库，却会有数据丢失。

三、未提交的事务导致不能回滚？

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文件已丢失，导致数据库启动时不能回滚所有未提交的事务。

四、结论

　　丢失了数据库的事务日志文件，最多只能恢复到最后一个检查点。但是：

1. 在最后一个检查点之后，data cache pool中修改过的数据，将全部丢失。

2. 事务日志中未提交的事务，将无法撤销。

参考文档

DBCC修复不同情况下的损坏

秒客网

MDF损坏或LDF文件损坏

【1】当MDF损坏时

【2】当LDF损坏时

【2.1】.数据库正常关闭，日志损坏

【2.2】替换法

【3】数据库非正常关闭，导致脏数据丢失

【4】实践参考

一、搭建环境

二、使用nowait选项停止SQL Server实例（服务）造成数据丢失？

三、未提交的事务导致不能回滚？

1. 创建事务

2. 停止SQL Server

3. 转移MDF文件

4. 修复数据库

5. 检查数据

四、结论

参考文档

相关文章