AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-12-20 1172
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
日志服务 SLS，月写入数据量 50GB 1个月
简介：

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，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
日志服务
Go
SQL
数据库
关键词：
SQL server
SQL还原
SQL Server还原
相关实践学习
使用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
嗯哼9925
目录
相关文章
吃番茄长大的西红柿
|
5月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
吃番茄长大的西红柿
383 2
游客j4mujezz7vm7y
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
35 0
sunrr
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化？
【7月更文挑战第23天】SQL Server的并行实施如何优化？
sunrr
98 13
小王老师呀
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能，可使用`generate_series`函数生成整数序列，例如：`SELECT value FROM generate_series(1, 10)。此外，`date_bucket`函数能按指定间隔（如周）对日期时间值分组，这些工具结合窗口函数和其他时间日期函数，能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
小王老师呀
65 9
以山向海
|
4月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
以山向海
58 6
小王老师呀
|
4月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符（1-8000），适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符，适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好，空间固定。 - VARCHAR(max) 对于大文本更合适，但可能影响性能。 - 选择取决于数据长度预期和业务需求。
小王老师呀
303 1
VipSoft
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
VipSoft
431 0
源码星辰
|
4月前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统（DBMS）而异，以下将以MySQL、SQL Server和Oracle为例，分别概述其安装部署的基本步骤。请注意，由于软件版本和操作系统的不同，具体步骤可能会有所变化。
源码星辰
271 3
1436047922066202
|
4月前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境： 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库，SQL Server数据库存放在D盘分区中。 SQL Server数据库故障： 存放SQL Server数据库的D盘分区容量不足，管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错，连接失效，SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
1436047922066202
76 1
以山向海
|
4月前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
以山向海
71 2

热门文章

最新文章

  • 1
    RDS for SQL server 空间问题排查汇总
  • 2
    SQL Server 2008 R2 死锁监控
  • 3
    Oracle实用SQL语句
  • 4
    【译】SQL Server索引进阶第十篇：索引的内部结构
  • 5
    Oracle PL/sql 主要特点
  • 6
    SQL Server 2005合并列成字符串 2008-11-07
  • 7
    SQL Server BI Step by Step SSIS 4 ---合并数据1
  • 8
    SQL疑难杂症【3】链接服务器提示"无法启动分布式事物"
  • 9
    SQL Server 事务日志传输
  • 10
    sql script: select database select all table
  • 1
    实时计算 Flink版产品使用合集之将数据写入Elasticsearch时，若Elasticsearch中的字段类型为date，对应的SQL类型应该是什么
    61
  • 2
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
    340
  • 3
    实时计算 Flink版产品使用合集之如果产品是基于ak的，可以提交sql任务到ecs自建hadoop集群吗
    60
  • 4
    实时计算 Flink版产品使用合集之使用Flink CDC SQL MongoDB Connector时，可以采取什么措施来提升数据消费速率
    98
  • 5
    实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后，CDC 吐出的操作（op）是怎样的
    57
  • 6
    MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
    115
  • 7
    实时计算 Flink版产品使用合集之如何选用 Flink SQL 的方式进行开发
    59
  • 8
    Mybatis多表关联查询与动态SQL（下）
    124
  • 9
    spark执行sql的原理是什么
    114
  • 10
    Mybatis多表关联查询与动态SQL（上）
    179

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • SQLServer数据库的创建
  • SQL进阶之约束、索引
  • SQL进阶之子句、关键字和操作符
  • 一小时快速掌握 SQL 语法
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月