検索

ホームページ  >  記事  >  データベース  >  您真的理解了SQLSERVER的日志链了吗？

您真的理解了SQLSERVER的日志链了吗？

WBOY
WBOYオリジナル
2016-06-07 15:34:321176ブラウズ

您真的理解了SQLSERVER的日志链了吗？ 先感谢 宋沄剑 给本人指点迷津，还有 郭忠辉 童鞋今天在QQ群里抛出的问题 这个问题跟宋沄剑讨论了三天，再次感谢宋沄剑 一直以来，SQLSERVER提供了一个非常好的管理工具：SSMS 又因为这个管理工具太好了，所有操作的简

 您真的理解了SQLSERVER的日志链了吗？

先感谢宋沄剑给本人指点迷津，还有郭忠辉童鞋今天在QQ群里抛出的问题

这个问题跟宋沄剑讨论了三天，再次感谢宋沄剑

 

一直以来，SQLSERVER提供了一个非常好的管理工具：SSMS

又因为这个管理工具太好了，所有操作的简单化，以至于使我们中毒太深，

对于SQLSERVER内部的一些概念搞得不清不楚

比如这些概念：日志备份链，备份日志链，日志链，备份链，备份集

 

大部分都是由于SSMS的界面所导致，有时候有些问题做一下实验就可以验证了，偏偏我们信赖了GUI

 

阅读下文之前大家可以先看一下宋沄剑的文章

SQL Server CheckPoint的几个误区

再谈SQL Server中日志的的作用

SQL Server误区30日谈-Day20-破坏日志备份链之后，需要一个完整备份来重新开始日志链

 

先说清楚这些概念吧

SQLSERVER只有日志链，备份记录（有些人也叫备份链）本人觉得叫备份记录更合适

下面三个东西说的都是同一样东西

备份集=备份记录=备份链

备份集：比如备份的集合，比如有对一个数据库的完备1、差备、日备1、完备2、日备2，这些数据库的备份的集合就是备份集

不过我更喜欢叫备份记录

备份记录实际上指 SELECT * FROM [msdb].[dbo].[backupset]

截断日志跟日志链断裂是否是同一样东西？

截断日志跟日志链断裂不是同一样东西

什么是日志链

其实大家可以把bak文件理解成一个压缩包，完整备份和差异备份的时候会把数据和日志一起带进压缩包，

而日志备份的时候只会把日志带进压缩包

您真的理解了SQLSERVER的日志链了吗？

我们先从一个实验开始吧

测试环境：SQLSERVER2012 开发版

脚本

为了不产生额外的日志，所以脚本里面没有select into语句，本来想select into进去临时表再对临时表进行排序

但是因为select into会产生额外的日志，只有直接对fn_dblog进行排序了

创建数据库

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span>1</span> <span>USE</span><span> master
</span><span>2</span> <span>GO</span>
<span>3</span> <span>--</span><span>创建数据库</span>
<span>4</span> <span>CREATE</span> <span>DATABASE</span><span> LogChainTest;
</span><span>5</span> <span>GO</span>
<span>6</span> <span>--</span><span>改为完整恢复模式</span>
<span>7</span> <span>ALTER</span> <span>DATABASE</span> LogChainTest <span>SET</span> RECOVERY <span>FULL</span><span>;
</span><span>8</span> <span>GO</span>
View Code

查看当前的事务日志

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span>1</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>fn_dblog</span><span>]</span>(<span>NULL</span>,<span>NULL</span>) <span>ORDER</span> <span>BY</span> <span>[</span><span>Begin Time</span><span>]</span> <span>ASC</span>
View Code

您真的理解了SQLSERVER的日志链了吗？

进行完整备份

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span>1</span> <span>--</span><span>第一个完整备份</span>
<span>2</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>3</span> <span>--</span><span>改为日期加时间的</span>
<span>4</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full1_</span><span>'</span>
<span>5</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>6</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>7</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span><span>@strbackup</span>  <span>WITH</span><span> INIT,CHECKSUM ;
</span><span>8</span> <span>GO</span>
View Code

查看bak文件中的事务日志

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>SELECT</span>  <span>*</span>
<span> 2</span> <span>FROM</span>    fn_dump_dblog(<span>NULL</span>, <span>NULL</span>, N<span>'</span><span>DISK</span><span>'</span>, <span>1</span><span>,
</span><span> 3</span>                       N<span>'</span><span>c:\LogChainTest_full1_20131206202536.bak</span><span>'</span>, <span>DEFAULT</span><span>,
</span><span> 4</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 5</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 6</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 7</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 8</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 9</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>10</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>11</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>12</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>13</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>14</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>)
View Code

您真的理解了SQLSERVER的日志链了吗？

我们再查看此时的数据库事务日志

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span>1</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span>2</span> <span>GO</span>
<span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>fn_dblog</span><span>]</span>(<span>NULL</span>,<span>NULL</span>) <span>ORDER</span> <span>BY</span> <span>[</span><span>Begin Time</span><span>]</span> <span>ASC</span>
View Code

您真的理解了SQLSERVER的日志链了吗？

发现完整备份之后事务日志比之前少了69-10=59行

我们发现bak文件中只记录AllocUnitId，而不记录表名，可能因为bak文件里的日志给SQLSERVER还原用的

而不是给用户查看事务日志用的，所以SQLSERVER干脆不记录表名了，以节省备份时间

您真的理解了SQLSERVER的日志链了吗？

您真的理解了SQLSERVER的日志链了吗？

看到这里大家会有问题了，为什麽日志会截断了？完整备份之后事务日志比之前少了69-10=59行

这里只能说明SQLSERVER把一些跟本数据库无关紧要的日志截断了，例如创建数据库时候修改master数据库的表

而不能说完整备份可以截断日志

而paul的文章给出了解释：

If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup,

you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint.

文章地址：

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

问题：为什麽bak文件里的日志的最后的三条记录会是

LOP_BEGIN_CKPT

LOP_XACT_CKPT

LOP_END_CKPT

我们用下图来表示吧

您真的理解了SQLSERVER的日志链了吗？

 

这里大家可以看一下宋沄剑的文章：再谈SQL Server中日志的的作用

 将CheckPoint标记写入日志（标记中包含当前数据库中活动的事务信息），并将Log Block写入持久化存储


我在开头说过事务日志中会放进去bak文件里，但是并不是整个事务日志文件里的日志记录全部放进去

而是把（1）已经checkpoint了的 （2）LAZY WRITTER   （3）EAGER WRITTER

还是看宋沄剑的文章吧，这麽复杂的过程我就不概括了：再谈SQL Server中日志的的作用

还有paul的文章：

Debunking a couple of myths around full database backups（揭穿一系列数据库完备的误区）

More on how much transaction log a full backup includes（数据库完备包含了多少事务日志）

实际上checkpoint和数据库备份有着密切联系，备份的时候SQLSERVER需要将哪些数据存入去bak文件

而在备份期间所新生成的事务和变化的数据要不要存入bak文件，这里面比较复杂，就不详细说了

不过有一点要说的是：在数据库备份之前，数据库引擎会自动执行checkpoint，以便在备份中包含对数据库页的全部更改。

我摘抄了网上的一些资料

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> http:<span>//</span>blog.csdn.net<span>/</span>tjvictor<span>/</span>article<span>/</span>details<span>/</span><span>5209604</span>
<span> 2</span> 导致CheckPoint检查点的事件： <span>1</span><span>.在数据库备份之前，数据库引擎会自动执行checkpoint，以便在备份中包含对数据库页的全部更改。
</span><span> 3</span> 
<span> 4</span> <span>2</span><span>.日志的活动部分超出了服务器在 recovery interval 服务器配置选项中指定的时间内可以恢复的大小。
</span><span> 5</span> 
<span> 6</span> <span>3</span>.日志的 <span>70</span><span>%</span><span> 已满，并且数据库处于日志截断模式。
</span><span> 7</span> 
<span> 8</span> 当下列条件都为 TRUE 时，数据库就处于日志截断模式：数据库使用的是简单恢复模式，并且在执行上一条引用数据库的 <span>BACKUP</span> <span>DATABASE</span><span> 语句后，发生下列事件之一：
</span><span> 9</span> 
<span>10</span> 在数据库中执行一项最小日志记录大容量复制操作或一条最条小日志记录的 <span>WRITETEXT</span><span> 语句。
</span><span>11</span> 
<span>12</span> 执行一个在数据库中添加或删除文件的 <span>ALTER</span> <span>DATABASE</span><span> 语句。
</span><span>13</span> 
<span>14</span> <span>4</span><span>.停止服务器也会在服务器上的每个数据库中发出一个检查点命令。下列停止 SQL Server 的方法将为每个数据库执行检查点：
</span><span>15</span> 
<span>16</span> <span>使用 SQL Server 配置管理器。
</span><span>17</span> 
<span>18</span> <span>使用 SQL Server Management Studio。
</span><span>19</span> 
<span>20</span> 使用 <span>SHUTDOWN</span><span> 语句。
</span><span>21</span> <span>--</span><span>------------------------------------------------------------------------</span>
<span>22</span> http:<span>//</span>www.cnblogs.com<span>/</span>CareySon<span>/</span>p<span>/</span><span>3315041</span><span>.html
</span><span>23</span> <span>5</span><span>.将恢复间隔设置为1分钟，意味着每1分钟会对所有的数据库做一次CheckPoint
</span><span>24</span> 
<span>25</span>     错误。将恢复间隔设置为1分钟不能想成建立一个Agent，每分钟写一个CheckPoint命令，这是两码事。这只是意味着每分钟去检查一次是否需要做CheckPoint，如果期间积累的日志量足够，才会对积累足够日志量的数据库去做CheckPoint。即使中间积累了巨量的日志，不到1分钟也不会做CheckPoint。
View Code

 

 

您真的理解了SQLSERVER的日志链了吗？

那么大家可以将bak文件里的事务日志当作为数据库事务日志

 

备份脚本

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>创建数据库</span>
<span> 4</span> <span>CREATE</span> <span>DATABASE</span><span> LogChainTest;
</span><span> 5</span> <span>GO</span>
<span> 6</span> <span>--</span><span>改为完整恢复模式</span>
<span> 7</span> <span>ALTER</span> <span>DATABASE</span> LogChainTest <span>SET</span> RECOVERY <span>FULL</span><span>;
</span><span> 8</span> <span>GO</span>
<span> 9</span> 
<span>10</span> 
<span>11</span> 
<span>12</span> 
<span>13</span> 
<span>14</span> 
<span>15</span> <span>--</span><span>第一个完整备份</span>
<span>16</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>17</span> <span>--</span><span>改为日期加时间的</span>
<span>18</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full1_</span><span>'</span>
<span>19</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>20</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>21</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span><span>@strbackup</span>  <span>WITH</span><span> INIT,CHECKSUM ;
</span><span>22</span> <span>GO</span>
<span>23</span> 
<span>24</span> 
<span>25</span> 
<span>26</span> 
<span>27</span> 
<span>28</span> <span>--</span><span>第一个差异备份</span>
<span>29</span> <span>USE</span><span> LogChainTest
</span><span>30</span> <span>GO</span>
<span>31</span> <span>CREATE</span> <span>TABLE</span> tt(id <span>INT</span><span>)
</span><span>32</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>33</span> <span>SELECT</span> <span>1</span>
<span>34</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>35</span> <span>--</span><span>改为日期加时间的</span>
<span>36</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_diff_</span><span>'</span>
<span>37</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>38</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>39</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT, DIFFERENTIAL;
</span><span>40</span> <span>GO</span>
<span>41</span> 
<span>42</span> 
<span>43</span> 
<span>44</span> <span>--</span><span>第一个日志备份</span>
<span>45</span> <span>USE</span><span> LogChainTest
</span><span>46</span> <span>GO</span>
<span>47</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>48</span> <span>SELECT</span> <span>2</span>
<span>49</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>50</span> <span>--</span><span>改为日期加时间的</span>
<span>51</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log1_</span><span>'</span>
<span>52</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>53</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>54</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>55</span> <span>GO</span>
<span>56</span> 
<span>57</span> 
<span>58</span> 
<span>59</span> 
<span>60</span> <span>--</span><span>第二个完整备份</span>
<span>61</span> <span>USE</span><span> master
</span><span>62</span> <span>GO</span>
<span>63</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>64</span> <span>--</span><span>改为日期加时间的</span>
<span>65</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full2_</span><span>'</span>
<span>66</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>67</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>68</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>69</span> <span>GO</span>
<span>70</span> 
<span>71</span> 
<span>72</span> <span>--</span><span>第二个日志备份</span>
<span>73</span> <span>USE</span><span> LogChainTest
</span><span>74</span> <span>GO</span>
<span>75</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>76</span> <span>SELECT</span> <span>3</span>
<span>77</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>78</span> <span>--</span><span>改为日期加时间的</span>
<span>79</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log2_</span><span>'</span>
<span>80</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>81</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>82</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>83</span> <span>GO</span>
View Code

备份策略：完整备份1-》差异备份-》日志备份1-》完整备份2-》日志备份2

还原脚本

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>--</span><span>差异备份和日志备份1打乱</span>
<span> 2</span> <span>USE</span><span> master
</span><span> 3</span> <span>GO</span>
<span> 4</span> <span>--</span><span>还原第一个完整备份</span>
<span> 5</span> <span>RESTORE</span> <span>DATABASE</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_full1_20131206230857.bak</span><span>'</span> 
<span> 6</span> <span>WITH</span> <span>REPLACE</span><span> ,CHECKSUM, NORECOVERY
</span><span> 7</span> <span>GO</span>
<span> 8</span> 
<span> 9</span> <span>--</span><span>还原第一个日志备份</span>
<span>10</span> <span>RESTORE</span> <span>LOG</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>c:\LogChainTest_diff_20131206230920.bak</span><span>'</span> 
<span>11</span> <span>WITH</span><span>  NORECOVERY
</span><span>12</span> <span>GO</span> 
<span>13</span> 
<span>14</span> <span>--</span><span>还原差异备份</span>
<span>15</span> <span>RESTORE</span> <span>DATABASE</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>c:\LogChainTest_diff_20131205222718.bak</span><span>'</span> 
<span>16</span> <span>WITH</span><span> NORECOVERY
</span><span>17</span> <span>GO</span>
<span>18</span> 
<span>19</span> 消息 <span>3136</span>，级别 <span>16</span>，状态 <span>3</span>，第 <span>1</span><span> 行
</span><span>20</span> <span>无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。
</span><span>21</span> 消息 <span>3013</span>，级别 <span>16</span>，状态 <span>1</span>，第 <span>1</span><span> 行
</span><span>22</span> <span>RESTORE</span> <span>DATABASE</span><span> 正在异常终止。
</span><span>23</span> 
<span>24</span> 
<span>25</span> 
<span>26</span> 
<span>27</span> <span>--</span><span>还原第二个日志备份，没有报错</span>
<span>28</span> <span>RESTORE</span> <span>LOG</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_log2_20131206230927.bak</span><span>'</span> 
<span>29</span> <span>WITH</span><span> RECOVERY
</span><span>30</span> <span>GO</span> 
<span>31</span> 
<span>32</span> 
<span>33</span> 
<span>34</span> 
<span>35</span> <span>--</span><span>可以查询出id列有三行记录</span>
<span>36</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span>37</span> <span>GO</span>
<span>38</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>tt</span><span>]</span>
View Code

上面的还原脚本，我先还原日志备份1，再还原差异备份结果就报错了

<span>1</span> 消息 <span>3136</span>，级别 <span>16</span>，状态 <span>3</span>，第 <span>1</span><span> 行
</span><span>2</span> <span>无法还原此差异备份，因为该数据库尚未还原到正确的早期状态。
</span><span>3</span> 消息 <span>3013</span>，级别 <span>16</span>，状态 <span>1</span>，第 <span>1</span><span> 行
</span><span>4</span> <span>RESTORE</span> <span>DATABASE</span> 正在异常终止。

还有，为什麽不用还原完整备份2数据也没有丢失？？

 

我们每次备份的时候，无论是完备、差备、日备都会把日志拷贝到bak文件里

而拷贝的时候会有一个last lsn确保日志顺序

您真的理解了SQLSERVER的日志链了吗？

当我先还原日志备份1，然后还原差异备份的时候因为last lsn的顺序不对所以就报错了

 

为什麽不用还原完整备份2数据也没有丢失？？

这里先说一下完备、差备、日备的大概方式

完备：复制数据和少量的log到bak

差备：复制有差异的数据和少量的log到bak

日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak

paul的文章里有解释：

http://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/

A log backup is *ALL* the log generated since the last log backup

备份策略：完整备份1-》差异备份-》日志备份1-》完整备份2-》日志备份2

我们没有还原完整备份2（相当于丢失了完整备份2），我们的还原顺序是

还原完整备份1（复制数据，根据redo/undo log保证事务一致性）

还原差异备份（复制差异数据，根据redo/undo log保证事务一致性）

还原日志备份1（数据全靠redo/undo log来恢复，根据redo/undo log保证事务一致性）

还原日志备份2（数据全靠redo/undo log来恢复，根据redo/undo log保证事务一致性）

因为日志备份2里面已经包含了从日志备份1到日志备份2的所有log，所以SQLSERVER可以凭借这些log来把数据恢复

而日志备份1里面已经包含了从完整备份1到日志备份1的所有log

所以，按理说，我们只需要还原完备1，日备1，日备2就可以恢复全部数据

 

测试：

我们使用下面备份脚本和还原脚本，看一下不还原日志备份1，直接还原日志备份2看有没有问题

备份脚本

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>创建数据库</span>
<span> 4</span> <span>CREATE</span> <span>DATABASE</span><span> LogChainTest;
</span><span> 5</span> <span>GO</span>
<span> 6</span> <span>--</span><span>改为完整恢复模式</span>
<span> 7</span> <span>ALTER</span> <span>DATABASE</span> LogChainTest <span>SET</span> RECOVERY <span>FULL</span><span>;
</span><span> 8</span> <span>GO</span>
<span> 9</span> 
<span>10</span> 
<span>11</span> 
<span>12</span> 
<span>13</span> 
<span>14</span> 
<span>15</span> <span>--</span><span>第一个完整备份</span>
<span>16</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>17</span> <span>--</span><span>改为日期加时间的</span>
<span>18</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full1_</span><span>'</span>
<span>19</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>20</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>21</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span><span>@strbackup</span>  <span>WITH</span><span> INIT,CHECKSUM ;
</span><span>22</span> <span>GO</span>
<span>23</span> 
<span>24</span> 
<span>25</span> 
<span>26</span> 
<span>27</span> 
<span>28</span> <span>--</span><span>第一个差异备份</span>
<span>29</span> <span>USE</span><span> LogChainTest
</span><span>30</span> <span>GO</span>
<span>31</span> <span>CREATE</span> <span>TABLE</span> tt(id <span>INT</span><span>)
</span><span>32</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>33</span> <span>SELECT</span> <span>1</span>
<span>34</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>35</span> <span>--</span><span>改为日期加时间的</span>
<span>36</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_diff_</span><span>'</span>
<span>37</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>38</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>39</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT, DIFFERENTIAL;
</span><span>40</span> <span>GO</span>
<span>41</span> 
<span>42</span> 
<span>43</span> 
<span>44</span> <span>--</span><span>第一个日志备份</span>
<span>45</span> <span>USE</span><span> LogChainTest
</span><span>46</span> <span>GO</span>
<span>47</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>48</span> <span>SELECT</span> <span>2</span>
<span>49</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>50</span> <span>--</span><span>改为日期加时间的</span>
<span>51</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log1_</span><span>'</span>
<span>52</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>53</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>54</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>55</span> <span>GO</span>
<span>56</span> 
<span>57</span> 
<span>58</span> 
<span>59</span> 
<span>60</span> <span>--</span><span>第二个完整备份</span>
<span>61</span> <span>USE</span><span> LogChainTest
</span><span>62</span> <span>GO</span>
<span>63</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>64</span> <span>SELECT</span> <span>3</span> <span>UNION</span> <span>ALL</span>
<span>65</span> <span>SELECT</span> <span>4</span>
<span>66</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>67</span> <span>--</span><span>改为日期加时间的</span>
<span>68</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full2_</span><span>'</span>
<span>69</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>70</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>71</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>72</span> <span>GO</span>
<span>73</span> 
<span>74</span> 
<span>75</span> <span>--</span><span>第二个日志备份</span>
<span>76</span> <span>USE</span><span> LogChainTest
</span><span>77</span> <span>GO</span>
<span>78</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>79</span> <span>SELECT</span> <span>5</span>
<span>80</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>81</span> <span>--</span><span>改为日期加时间的</span>
<span>82</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log2_</span><span>'</span>
<span>83</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>84</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>85</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span>86</span> <span>GO</span>
View Code

还原脚本

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>还原第一个完整备份</span>
<span> 4</span> <span>RESTORE</span> <span>DATABASE</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_full1_20131207102535.bak</span><span>'</span> 
<span> 5</span> <span>WITH</span> <span>REPLACE</span><span> ,NORECOVERY
</span><span> 6</span> <span>GO</span>
<span> 7</span> 
<span> 8</span> 
<span> 9</span> <span>--</span><span>还原第二个日志备份</span>
<span>10</span> <span>RESTORE</span> <span>LOG</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_log2_20131207102602.bak</span><span>'</span> 
<span>11</span> <span>WITH</span><span> RECOVERY
</span><span>12</span> <span>GO</span>
View Code

您真的理解了SQLSERVER的日志链了吗？

您真的理解了SQLSERVER的日志链了吗？

插入的数据太少，日志太少，搞得文件的size不那么明显

结果报错

<span>1</span> 消息 <span>4305</span>，级别 <span>16</span>，状态 <span>1</span>，第 <span>2</span><span> 行
</span><span>2</span> 此备份集中的日志开始于 LSN <span>35000000017200001</span>，该 LSN 太晚，无法应用到数据库。可以还原包含 LSN <span>35000000008600001</span><span> 的较早的日志备份。
</span><span>3</span> 消息 <span>3013</span>，级别 <span>16</span>，状态 <span>1</span>，第 <span>2</span><span> 行
</span><span>4</span> <span>RESTORE</span> <span>LOG</span> 正在异常终止。

因为没有还原日志备份1，缺少了完备1到日备1之间的日志，所以就报错了

我们使用下面的脚本来进行还原，只还原完备1，日备1，日备2

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>还原第一个完整备份</span>
<span> 4</span> <span>RESTORE</span> <span>DATABASE</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_full1_20131207102535.bak</span><span>'</span> 
<span> 5</span> <span>WITH</span> <span>REPLACE</span><span> ,NORECOVERY
</span><span> 6</span> <span>GO</span>
<span> 7</span> 
<span> 8</span> <span>--</span><span>还原第一个日志备份</span>
<span> 9</span> <span>RESTORE</span> <span>LOG</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_log1_20131207102542.bak</span><span>'</span> 
<span>10</span> <span>WITH</span><span> NORECOVERY
</span><span>11</span> <span>GO</span> 
<span>12</span> 
<span>13</span> <span>--</span><span>还原第二个日志备份</span>
<span>14</span> <span>RESTORE</span> <span>LOG</span> LogChainTest <span>FROM</span> <span>DISK</span><span>=</span><span>'</span><span>C:\LogChainTest_log2_20131207102602.bak</span><span>'</span> 
<span>15</span> <span>WITH</span><span> RECOVERY
</span><span>16</span> <span>GO</span> 
<span>17</span> 
<span>18</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span>19</span> <span>GO</span>
<span>20</span> <span>SELECT</span> <span>*</span> <span>FROM</span> tt
View Code

您真的理解了SQLSERVER的日志链了吗？

这次成功了，数据都没有丢失，那么说明我丢失了差异备份、完整备份2也没有关系

如果我丢失了日备1、差备、完备2，只有完备1和日备2，那么这个时候你只能祈祷了，你只能还原完备1

差备、日备1、完备2、日备2的数据都已经丢失

BAK文件中日志数量的多少

我刚才说

完备：复制数据和少量的log到bak

差备：复制有差异的数据和少量的log到bak

日备：不复制数据，如果是第一次日备，会把所有的log复制到bak，如果是第二次日备，会把自上一次日备到这次日备的log复制到bak

我怎麽看出来的？

测试：

我们看一下每次备份完毕后，bak文件里面的日志数量

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>SELECT</span>  <span>*</span>
<span> 4</span> <span>FROM</span>    fn_dump_dblog(<span>NULL</span>, <span>NULL</span>, N<span>'</span><span>DISK</span><span>'</span>, <span>1</span><span>,
</span><span> 5</span>                       N<span>'</span><span>c:\LogChainTest_full1_20131207102535.bak</span><span>'</span>, <span>DEFAULT</span><span>,
</span><span> 6</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 7</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 8</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span> 9</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>10</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>11</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>12</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>13</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>14</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>15</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>16</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>)
View Code

完备1

您真的理解了SQLSERVER的日志链了吗？

差备

您真的理解了SQLSERVER的日志链了吗？

日备1

您真的理解了SQLSERVER的日志链了吗？

完备2

您真的理解了SQLSERVER的日志链了吗？

日备2

您真的理解了SQLSERVER的日志链了吗？

在完备2的时候bak中的日志只有44行，说明完整备份只存储一些必要的日志，不是所有日志都存储

完备存储这些日志的作用是在还原的时候根据这些log去redo/undo 保证事务一致性，所以只会写入少量日志

因为完备和差备都是复制数据，所以就没有必要像日备那样全部事务日志都复制到bak里面

而日备2为什麽只有73行记录，因为在日备1的时候SQLSERVER已经截断了事务日志，日备2的日志就像我前面说的

如果是第二次日备，会把自上一次日备到这次日备的log复制到bak

 

如果我们不想在backup log 的时候截断事务日志，可以使用NO_TRUNCATE和COPY_ONLY这两个backup option

备份脚本 NO_TRUNCATE

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span>  1</span> <span>USE</span><span> master
</span><span>  2</span> <span>GO</span>
<span>  3</span> <span>--</span><span>创建数据库</span>
<span>  4</span> <span>CREATE</span> <span>DATABASE</span><span> LogChainTest;
</span><span>  5</span> <span>GO</span>
<span>  6</span> <span>--</span><span>改为完整恢复模式</span>
<span>  7</span> <span>ALTER</span> <span>DATABASE</span> LogChainTest <span>SET</span> RECOVERY <span>FULL</span><span>;
</span><span>  8</span> <span>GO</span>
<span>  9</span> 
<span> 10</span> 
<span> 11</span> 
<span> 12</span> 
<span> 13</span> 
<span> 14</span> 
<span> 15</span> <span>--</span><span>第一个完整备份</span>
<span> 16</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span> 17</span> <span>--</span><span>改为日期加时间的</span>
<span> 18</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full1_</span><span>'</span>
<span> 19</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span> 20</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span> 21</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span><span>@strbackup</span>  <span>WITH</span><span> INIT,CHECKSUM ;
</span><span> 22</span> <span>GO</span>
<span> 23</span> 
<span> 24</span> 
<span> 25</span> 
<span> 26</span> 
<span> 27</span> 
<span> 28</span> <span>--</span><span>第一个差异备份</span>
<span> 29</span> <span>USE</span><span> LogChainTest
</span><span> 30</span> <span>GO</span>
<span> 31</span> <span>CREATE</span> <span>TABLE</span> tt(id <span>INT</span><span>)
</span><span> 32</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span> 33</span> <span>SELECT</span> <span>1</span>
<span> 34</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span> 35</span> <span>--</span><span>改为日期加时间的</span>
<span> 36</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_diff_</span><span>'</span>
<span> 37</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span> 38</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span> 39</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT, DIFFERENTIAL;
</span><span> 40</span> <span>GO</span>
<span> 41</span> 
<span> 42</span> 
<span> 43</span> 
<span> 44</span> <span>--</span><span>第一个日志备份</span>
<span> 45</span> <span>USE</span><span> LogChainTest
</span><span> 46</span> <span>GO</span>
<span> 47</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span> 48</span> <span>SELECT</span> <span>2</span>
<span> 49</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span> 50</span> <span>--</span><span>改为日期加时间的</span>
<span> 51</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log1_</span><span>'</span>
<span> 52</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span> 53</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span> 54</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT,NO_TRUNCATE;
</span><span> 55</span> <span>GO</span>
<span> 56</span> 
<span> 57</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span> 58</span> <span>GO</span>
<span> 59</span> <span>SELECT</span> <span>*</span>  <span>FROM</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>fn_dblog</span><span>]</span>(<span>NULL</span>,<span>NULL</span>) <span>ORDER</span> <span>BY</span> <span>[</span><span>Begin Time</span><span>]</span> <span>ASC</span>
<span> 60</span> 
<span> 61</span> 
<span> 62</span> 
<span> 63</span> <span>--</span><span>第二个完整备份</span>
<span> 64</span> <span>USE</span><span> LogChainTest
</span><span> 65</span> <span>GO</span>
<span> 66</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span> 67</span> <span>SELECT</span> <span>3</span> <span>UNION</span> <span>ALL</span>
<span> 68</span> <span>SELECT</span> <span>4</span>
<span> 69</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span> 70</span> <span>--</span><span>改为日期加时间的</span>
<span> 71</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full2_</span><span>'</span>
<span> 72</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span> 73</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span> 74</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT;
</span><span> 75</span> <span>GO</span>
<span> 76</span> 
<span> 77</span> 
<span> 78</span> <span>--</span><span>第二个日志备份</span>
<span> 79</span> <span>USE</span><span> LogChainTest
</span><span> 80</span> <span>GO</span>
<span> 81</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span> 82</span> <span>SELECT</span> <span>5</span>
<span> 83</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span> 84</span> <span>--</span><span>改为日期加时间的</span>
<span> 85</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log2_</span><span>'</span>
<span> 86</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span> 87</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span> 88</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT,NO_TRUNCATE;
</span><span> 89</span> <span>GO</span>
<span> 90</span> 
<span> 91</span> 
<span> 92</span> 
<span> 93</span> 
<span> 94</span> 
<span> 95</span> 
<span> 96</span> <span>USE</span><span> master
</span><span> 97</span> <span>GO</span>
<span> 98</span> <span>SELECT</span>  <span>*</span>
<span> 99</span> <span>FROM</span>    fn_dump_dblog(<span>NULL</span>, <span>NULL</span>, N<span>'</span><span>DISK</span><span>'</span>, <span>1</span><span>,
</span><span>100</span>                       N<span>'</span><span>c:\LogChainTest_full1_20131207102535.bak</span><span>'</span>, <span>DEFAULT</span><span>,
</span><span>101</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>102</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>103</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>104</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>105</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>106</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>107</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>108</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>109</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>110</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span>, <span>DEFAULT</span><span>,
</span><span>111</span>                       <span>DEFAULT</span>, <span>DEFAULT</span>)
View Code

我们看一下第一个日志备份和第二个日志备份之后，数据库事务日志和bak文件里面的日志数量
日备1 数据库日志

您真的理解了SQLSERVER的日志链了吗？

日备1 bak文件日志

您真的理解了SQLSERVER的日志链了吗？

日备2 数据库日志

您真的理解了SQLSERVER的日志链了吗？

日备2 bak文件日志

您真的理解了SQLSERVER的日志链了吗？

 

备份脚本 COPY_ONLY

您真的理解了SQLSERVER的日志链了吗？您真的理解了SQLSERVER的日志链了吗？ 

<span> 1</span> <span>USE</span><span> master
</span><span> 2</span> <span>GO</span>
<span> 3</span> <span>--</span><span>创建数据库</span>
<span> 4</span> <span>CREATE</span> <span>DATABASE</span><span> LogChainTest;
</span><span> 5</span> <span>GO</span>
<span> 6</span> <span>--</span><span>改为完整恢复模式</span>
<span> 7</span> <span>ALTER</span> <span>DATABASE</span> LogChainTest <span>SET</span> RECOVERY <span>FULL</span><span>;
</span><span> 8</span> <span>GO</span>
<span> 9</span> 
<span>10</span> 
<span>11</span> 
<span>12</span> 
<span>13</span> 
<span>14</span> 
<span>15</span> <span>--</span><span>第一个完整备份</span>
<span>16</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>17</span> <span>--</span><span>改为日期加时间的</span>
<span>18</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_full1_</span><span>'</span>
<span>19</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>20</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>21</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span><span>@strbackup</span>  <span>WITH</span><span> INIT,CHECKSUM ;
</span><span>22</span> <span>GO</span>
<span>23</span> 
<span>24</span> 
<span>25</span> 
<span>26</span> 
<span>27</span> 
<span>28</span> <span>--</span><span>第一个差异备份</span>
<span>29</span> <span>USE</span><span> LogChainTest
</span><span>30</span> <span>GO</span>
<span>31</span> <span>CREATE</span> <span>TABLE</span> tt(id <span>INT</span><span>)
</span><span>32</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>33</span> <span>SELECT</span> <span>1</span>
<span>34</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>35</span> <span>--</span><span>改为日期加时间的</span>
<span>36</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_diff_</span><span>'</span>
<span>37</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>38</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>39</span> <span>BACKUP</span> <span>DATABASE</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT, DIFFERENTIAL;
</span><span>40</span> <span>GO</span>
<span>41</span> 
<span>42</span> 
<span>43</span> 
<span>44</span> <span>--</span><span>第一个日志备份</span>
<span>45</span> <span>USE</span><span> LogChainTest
</span><span>46</span> <span>GO</span>
<span>47</span> <span>INSERT</span> <span>INTO</span><span> tt
</span><span>48</span> <span>SELECT</span> <span>2</span>
<span>49</span> <span>DECLARE</span> <span>@strbackup</span> <span>NVARCHAR</span>(<span>100</span><span>)
</span><span>50</span> <span>--</span><span>改为日期加时间的</span>
<span>51</span> <span>SET</span> <span>@strbackup</span> <span>=</span> <span>'</span><span>C:\LogChainTest_log1_</span><span>'</span>
<span>52</span>     <span>+</span> <span>REPLACE</span>(<span>REPLACE</span>(<span>REPLACE</span>(<span>CONVERT</span>(<span>VARCHAR</span>, <span>GETDATE</span>(), <span>120</span>), <span>'</span><span>-</span><span>'</span>, <span>''</span>), <span>'</span> <span>'</span><span>,
</span><span>53</span>                       <span>''</span>), <span>'</span><span>:</span><span>'</span>, <span>''</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>
<span>54</span> <span>BACKUP</span> <span>LOG</span> LogChainTest <span>TO</span> <span>DISK</span> <span>=</span> <span>@strbackup</span> <span>WITH</span><span> INIT,COPY_ONLY;
</span><span>55</span> <span>GO</span>
<span>56</span> 
<span>57</span> <span>USE</span> <span>[</span><span>LogChainTest</span><span>]</span>
<span>58</span>
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：bcp将本地txt文本导入远程sqlserver中，本地未装sqlserver次の記事：mysql The server quit without updating PID file异常解决办法

関連記事

続きを見る