SQL Server的还原(3)——STOPATMARK和STOPBEFOREMARK

简介:

0.参考文献

[1]SQL Server的还原(2)——STOPAT

[2]SQL Server的还原

[3]恢复到日志序列号 (SQL Server)

1.日志序列号的概述

事务日志中的每个记录都由一个日志序列号 (LSN) 唯一标识。并且LSN是有序的,后发生的日志其LSN肯定大于早发生的日志。

2.还原到 LSN 的 Transact-SQL 语法

通过使用 RESTORE 语句,可以在 LSN 处或刚好在 LSN 之前停止,如下所示:

  • 使用 WITH STOPATMARK = 'lsn:<lsn_number>' 子句,其中 lsn:<lsnNumber> 是一个字符串,它指出包含指定 LSN 的日志记录是恢复点。

    STOPATMARK 前滚到 LSN,并且前滚中包括该日志记录。

  • 使用 WITH STOPBEFOREMARK = 'lsn:<lsn_number>' 子句,其中 lsn:<lsnNumber> 是一个字符串,它指出位于包含指定 LSN 的日志记录之前的日志记录是恢复点。

    STOPBEFOREMARK 前滚到 LSN,并从前滚中排除该日志记录。

通常会选择要包括或排除的特定事务。 虽然实践中并不总是如此,但指定的日志记录就是事务提交记录。

3.实例

下面给出操作实例,执行顺序是1->(2-1)->3, 1->(2-2)->3, 1->(2-3)->3,第二个跟第三个操作成功,因此无法区分STOPBEFOREMARK跟STOPATMARK有什么区别。

复制代码
--step1:完整还原数据库
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1,这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO

--step2-1:STOPATMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:22000000044000001';--log1的first LSN,失败
GO
--错误信息:The specified STOPAT time is too early. All or part of the database is already rolled forward beyond that point.

--step2-2:STOPATMARK,据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:23000000008700001';--log1的Last LSN,成功
GO
/*查询结果
id          name
----------- ---------------
1           日志1备份前
*/

--step2-3:STOPBEFOREMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPBEFOREMARK = 'lsn:23000000008700001';--log1的Last LSN,成功
GO
/*查询结果
id          name
----------- ---------------
1           日志1备份前
*/

--step3:是在执行上述操作以后,TESTDB2往往处于restoring状态,使用此语句使数据库可用
RESTORE DATABASE TESTDB2 WITH RECOVERY; 
复制代码

Log Sequence Number (PS:2012-7-23)——book:<sql server 2000 internal data structure>

Each log record is uniquely identified by an LSN that gives Microsoft® SQL Server™ enough information to easily locate a record on disk and read it into cache in a single disk read operation. Each new log record is written to the logical end of the log with an LSN higher than the LSN of the record before it. A log record with a given LSN has been generated after all records that have a lower LSN.

Table 34 lists the elements that make up an LSN.

Table 34 Log Sequence Number (LSN) Elements

Field Size Offset Description
m_fSeqNo 4 0x00 VLF's Sequence Number
m_blockOffset 4 0x04 Offset of log block DIV 512
m_slotId 2 0x08 Slot ID

我们使用dbcc log(dbname,1|2|3)获得的LSN就是按照上述格式显示的,比如00000017:000000ba:0002,但是如果我们在STOPATMARK中使用这个LSN:

STOPATMARK= 'lsn:00000017:000000ba:0002';

就会报错:The named mark does not identify a valid LSN.这是因为STOPATMARK使用的是10进制的数字,而上面的数字是16进制的。通过SSMS我们可以发现这些LSN的格式,如下图所示:

十六进制的LSN:00000017:000000ba:0002转化为十进制所得的LSN=23 0000000186 00002=23:0000000186:00002。之所以这么分割,是因为最大的四位十六进制数FFFF=65535,是一个5位的十进制数;最大的八位十六进制数FFFFFFFF=4294967295,是一个10位的十进制数。所以上面0000000186一共有10位,00002一共有2两位,而23之前的0全部被省略。

下面进行一些实例演示。首先我们创建一个空的数据库,备份设备,以及表,然后插入数据,备份数据库。

复制代码
--开始:实验4:使用STOPATMARK关键字来还原数据-----------------------------------
--1:创建备份设备
EXEC sp_addumpdevice 'disk', 'TESTDB2Backups', 'd:\backup\TESTDB2Backups.bak';

--2:创建数据库TESTDB2,然后再执行下面的sql语句
USE TESTDB2
create table customers
(
    id int identity(1,1) primary key not null, 
    name varchar(15)
);

--3:创建完整备份,这样日志不会automatic truncate
BACKUP DATABASE TESTDB2 TO TESTDB2Backups
   WITH INIT;--override the device
GO

--4.先checkpoint,然后dbcc log查看日志
checkpoint
dbcc log(TESTDB2,1)--dbcc log只会看到minLSN以后的日志。假如没有未提交事务的话,MinLSN就是checkpoint的LSN。

--5:插入一条记录
insert into customers(name) values('aaaa');   --LSN:00000017:000000b6:0002,00000017:000000b6:0014,00000017:000000b6:0015
dbcc log(TESTDB2,1);
insert into customers(name) values('bbbb');   --LSN:00000017:000000ba:0002,00000017:000000ba:0003,00000017:000000ba:0004
dbcc log(TESTDB2,1);
insert into customers(name) values('cccc');   --LSN:00000017:000000bb:0002,00000017:000000bb:0003,00000017:000000bb:0004
dbcc log(TESTDB2,1);
select * from customers

--6.创建一个事务,插入一条记录,
begin tran
insert into customers(name) values('ddd');    --begin:00000017:000000bc:0002,insert:00000017:000000bc:0003
dbcc log(TESTDB2,1);

--7:在事务结束(commit/rollback)之前备份事务日志。Cannot perform a backup or restore operation within a transaction.
BACKUP LOG TESTDB2     TO TESTDB2Backups;
GO

--8.回滚事务
rollback      --del:00000017:000000bc:0004,abort-00000017:000000bc:0007
dbcc log(TESTDB2,1);

--9.再次备份事务日志。
BACKUP LOG TESTDB2 TO TESTDB2Backups;
GO

--10.备份完日志以后事务日志被truncate了。使用dbcc log 发现事务日志记录减少。
dbcc log(TESTDB2,1)

--11.插入一条记录
insert into customers(name) values('fff'); --00000017:000000bc:000c,00000017:000000bc:000d,00000017:000000bc:000e
dbcc log(TESTDB2,1);

--12.备份尾日志,用于还原上面插入的那一条fff,备份日志会自动checkpoint,并且truncate日志。
use master
BACKUP LOG TESTDB2     TO TESTDB2Backups
with norecovery;
GO
dbcc log(TESTDB2,1);
复制代码

错误更正

上述实验第7步表明:如果事务未提交,则不能进行日志备份。如果备份则会报错:Cannot perform a backup or restore operation within a transaction.

更正上面第7步操作,之所以报错,是因为backup database在一个explicit transaction中执行,这是不允许的。并不是说有一个事务没有提交,就不能执行backup database操作。很简单的一个道理,我们通常会设置每一个小时备份一次事务日志,如果有事务没有提交就不能备份,那么自动备份事务日志的时候会经常报错,不符合常理。我们在这里新建一个query,然后执行backup database就可以正确执行事务日志备份。

------------------------

上述已经进行了一次完整备份和一次事务日志备份,以及一次尾日志之备份,接下来就可以进行数据库还原操作了。首先我们测试stopatmark在insert操作的三个不同的LSN上的效果:

复制代码
--13.完整数据库还原
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1,这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO

--还原到insert into customers(name) values('bbbb');位置的比较。
--step13-1:STOPATMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:23000000018600004';--这个LSN是事务insert into customers(name) values('bbbb'); 的末尾.
GO
use TESTDB2
select * from customers;    --恢复了两条记录aaaa和bbbb.

--step13-2:STOPATMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:23000000018600002';--这个LSN是事务insert into customers(name) values('bbbb'); 的起始位置.
GO
use TESTDB2
select * from customers;    --相比13-1,这里只恢复了一条记录aaaa.


--step13-3:STOPATMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:23000000018600003';--这个LSN是事务insert into customers(name) values('bbbb'); 的insert位置.
GO
use TESTDB2
select * from customers;    --相比13-1,这里也只恢复了一条记录aaaa.
--总结:只有在STOPATMARK='LSN:事务结束的LSN时才会包含这个事务,否者不包含'
复制代码

上述操作可以得出结论,如果stopatmark是在insert这样的事务操作的最后的lsn上面,那个恢复的时候,stopatmark会包含这个事务的操作。如果不是事务末尾的LSN则不会包含。

接下来测试stopatmark在rollback的事务上进行的操作。

复制代码
--还原到roll事务的lsn上-----------
--14.完整数据库还原
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1,这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO

--step14-1:STOPATMARK,根据日志序列号还原,WITH STOPATMARK指出包含指定 LSN 的日志记录是恢复点。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPATMARK = 'lsn:23000000018800003';--这个LSN是事务insert into customers(name) values('ddd');  的insert位置.
GO
--执行上述恢复操作会报告如下信息:
--This log file contains records logged before the designated mark. The database is being left in the Restoring state so you can apply another log file

--use TESTDB2发现不能使用,这条语句报错:Database 'TESTDB2' cannot be opened. It is in the middle of a restore.
use TESTDB2   

--step3:TESTDB2往往处于restoring状态,使用此语句强制使数据库可用
RESTORE DATABASE TESTDB2 WITH RECOVERY; 
use TESTDB2   
select * from customers; --含有3条记录(aaaa,bbbb,cccc),也就是没有包含未提交事务的操纵。
复制代码

上述实验表明,如果事务未提交,那么即使强制stopatmark在rollback的操纵上面,也不会包含这个操作。

 STOPBEFOREMARK测试

上面测试的是STOPATMARK,下面来测试SOTPBEFOREMARK

复制代码
--STOPBEFOREMARK测试------------------------------
--19.完整数据库还原
use master
RESTORE DATABASE TESTDB2
   FROM TESTDB2Backups 
   WITH FILE=1,--备份集为1,这个在前面完整备份的时候从message中得到。
   REPLACE,
   NORECOVERY;
GO

--还原到insert into customers(name) values('bbbb');位置的比较。
--19-1.STOPBEFOREMARK 前滚到 LSN,并从前滚中排除该日志记录。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPBEFOREMARK = 'lsn:23000000018600004';--这个LSN是事务insert into customers(name) values('bbbb'); 的末尾.
GO
use TESTDB2
select * from customers;    --恢复了一条记录aaaa.

--19-2.STOPBEFOREMARK 前滚到 LSN,并从前滚中排除该日志记录。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPBEFOREMARK = 'lsn:23000000018600002';--这个LSN是事务insert into customers(name) values('bbbb'); 的起始位置.
GO
use TESTDB2
select * from customers;    --同19-1,这里只恢复了一条记录aaaa.


--19-3.STOPBEFOREMARK 前滚到 LSN,并从前滚中排除该日志记录。
RESTORE LOG TESTDB2
   FROM TESTDB2Backups
   WITH RECOVERY, 
        FILE=2,--备份集为2
        STOPBEFOREMARK = 'lsn:23000000018600003';--这个LSN是事务insert into customers(name) values('bbbb'); 的insert位置.
GO
use TESTDB2
select * from customers;    --相比13-1,这里也只恢复了一条记录aaaa.
复制代码

--结论:

  1. 如果使用STOPBEFOREMARK,那么不管是在事务的哪一个阶段,都不会包含事务中的内容。
  2. 与STOPATMARK的区别是在事务最后的哪一个LSN上面。

 

 

 本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2012/07/09/2583087.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
57 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
54 0
|
1月前
|
SQL 人工智能 算法
【数据库SQL server】传统运算符与专门运算符
【数据库SQL server】传统运算符与专门运算符
33 0
|
1月前
|
SQL 数据库 数据安全/隐私保护
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
48 0
|
2月前
|
SQL 流计算
Flink CDC 1.12版本引入了对SQL Server的支持
【1月更文挑战第26天】【1月更文挑战第124篇】Flink CDC 1.12版本引入了对SQL Server的支持
24 1
|
3月前
|
SQL 关系型数据库 MySQL
使用Flink CDC从SQL Server同步数据到MySQL
使用Flink CDC从SQL Server同步数据到MySQL
110 1
|
7天前
|
SQL 存储 数据安全/隐私保护
|
9天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
1月前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
75 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
23 0