实验背景:

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


一、搭建环境

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文件。

204641971.png


5. 附加数据库时报错

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

204052979.png


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. 事务日志中未提交的事务,将无法撤销。