SQLSERVER数据库备份操作和还原操作做了什么 看了这篇文章： 还原/备份时做了些什么 自己也测试了一下，下面说的错误日志指的是SQLSERVER ERRORLOG 一般在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG路径下，如果SQLSERVER安装在C盘的话 要打
SQLSERVER数据库备份操作和还原操作做了什么
看了这篇文章：还原/备份时做了些什么
自己也测试了一下，下面说的错误日志指的是SQLSERVER ERRORLOG
一般在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG路径下，如果SQLSERVER安装在C盘的话
要打开跟踪标志：3004, 3605
完整备份
先清空错误日志
<span>1</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>sp_cycle_errorlog</span><span>]</span>
执行数据库完整备份
<span>1</span> <span>DBCC</span> traceon(<span>3004</span>, <span>3605</span>, <span>-</span><span>1</span><span>) </span><span>2</span> <span>--</span><span>完整备份</span> <span>3</span> <span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>pratice</span><span>]</span> <span>4</span> <span>TO</span> <span>DISK</span><span>=</span><span>'</span><span>D:\pratice_fullbackup_201207160152.bak</span><span>'</span>
读取错误日志
<span>1</span> <span>EXEC</span> <span>[</span><span>sys</span><span>]</span>.<span>[</span><span>xp_readerrorlog</span><span>]</span>
备份时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice</span><span>'</span> (位于文件 <span>2</span> 上)处理了 <span>28152</span><span> 页。 </span><span>2</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>2</span> 上)处理了 <span>2</span><span> 页。 </span><span>3</span> <span>BACKUP</span> <span>DATABASE</span> 成功处理了 <span>28154</span> 页，花费 <span>1.639</span> 秒(<span>134.195</span> MB<span>/</span>秒)。
错误日志输出
<span> 1</span> <span>1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.300</span> spid56 <span>DBCC</span> TRACEON <span>3004</span>, server process ID (SPID) <span>56</span>. This <span>is</span> an informational message <span>only</span>; no <span>user</span> action <span>is</span><span> required.
</span><span> 2</span> <span>2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.300</span> spid56 <span>DBCC</span> TRACEON <span>3605</span>, server process ID (SPID) <span>56</span>. This <span>is</span> an informational message <span>only</span>; no <span>user</span> action <span>is</span><span> required.
</span><span> 3</span> <span>3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.300</span> spid56 BackupDatabase: <span>Database</span><span> pratice
</span><span> 4</span> <span>4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.320</span> spid56 <span>Backup</span>: Media <span>open</span>
<span> 5</span> <span>5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.320</span> spid56 <span>Backup</span>: Media ready <span>to</span> <span>backup</span>
<span> 6</span> <span>6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.320</span> spid56 <span>Backup</span>: Clearing differential bitmaps <span>--</span><span>-正在清除DCM页面的位信息，因为是完整备份，差异变更(Differential Changed Map，DCM)页面 http://tech.it168.com/a2010/0921/1106/000001106857.shtml</span>
<span> 7</span> <span>7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.330</span> spid56 <span>Backup</span><span>: Bitmaps cleared
</span><span> 8</span> <span>8</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.330</span> spid56 BackupDatabase: <span>Checkpoint</span> done <span>--</span><span>做checkpoint，将所有修改的数据都被写入磁盘</span>
<span> 9</span> <span>9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.330</span> spid56 <span>Backup</span>: Scanning allocation bitmaps <span>--</span><span>扫描数据库第3页：GAM页 和数据库第4页：SGAM页</span>
<span>10</span> <span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 <span>Backup</span>: Done <span>with</span><span> allocation bitmaps
</span><span>11</span> <span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 BackupDatabase: <span>Work</span> estimates done <span>--</span><span>估计备份操作所需要的时间</span>
<span>12</span> <span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 <span>Backup</span><span>: Leading metadata div done
</span><span>13</span> <span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 <span>Backup</span>:Copying data <span>--</span><span>正在拷贝数据</span>
<span>14</span> <span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 <span>Backup</span>: DBReaderCount <span>=</span> <span>1</span> <span>--</span><span>备份数据库读线程只有一个</span>
<span>15</span> <span>15</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>50.340</span> spid56 Started <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
</span><span>16</span> <span>16</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.970</span> spid56 Completed <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf
</span><span>17</span> <span>17</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.970</span> spid56 Padding MSDA <span>with</span> <span>65536</span><span> bytes
</span><span>18</span> <span>18</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.970</span> spid56 BackupDatabase: <span>Database</span><span> files done
</span><span>19</span> <span>19</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.980</span> spid56 慂正灵慄慴慢敳退慂正灵潌g邐<span>%</span>s : <span>Log</span><span> files done
</span><span>20</span> <span>20</span> : <span>Log</span><span> files done
</span><span>21</span> <span>21</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.980</span> spid56 <span>Backup</span>: Trailing config done <span>--</span><span>后续配置完成</span>
<span>22</span> <span>22</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.980</span> spid56 <span>Backup</span><span>: MBC done
</span><span>23</span> <span>23</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.990</span> spid56 BackupDatabase: Writing history records <span>--</span><span>写入备份历史记录到错误日志</span>
<span>24</span> <span>24</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.990</span> 备份 <span>Database</span> backed up. <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), pages dumped: <span>28171</span>, first LSN: <span>3087</span>:<span>10132</span>:<span>37</span>, last LSN: <span>3087</span>:<span>10149</span>:<span>1</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span>, device information: (<span>FILE</span><span>=</span><span>1</span>, TYPE<span>=</span><span>DISK</span>: {D:\pratice_fullbackup_201207160152 <span>--</span><span>pages dumped: 28171 BACKUP DATABASE 成功处理了 28154 页，花费 1.639 秒(134.195 MB/秒)</span>
<span>25</span> <span>25</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>51.990</span> spid56 Writing <span>backup</span> history records <span>--</span><span>写入备份历史记录到msdb的相关备份表</span>
<span>26</span> <span>26</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>17</span>:<span>52.010</span> spid56 BackupDatabase: Finished
关键的语句我都做了一些解释
2013-12-01 19:17:50.320 spid56 Backup: Clearing differential bitmaps ---正在清除DCM页面的位信息，因为是完整备份，差异变更(Differential Changed Map，DCM)页面 http://tech.it168.com/a2010/0921/1106/000001106857.shtml
差异备份
<span>1</span> <span>--</span><span>差异备份</span> <span>2</span> <span>Backup</span> <span>Database</span> <span>[</span><span>pratice</span><span>]</span> <span>3</span> <span>To</span> <span>disk</span><span>=</span><span>'</span><span>d:\pratice_Diff_20070908.bak</span><span>'</span> <span>4</span> <span>With</span> Differential
备份时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>40</span><span> 页。 </span><span>2</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>1</span><span> 页。 </span><span>3</span> <span>BACKUP</span> <span>DATABASE</span> <span>WITH</span> DIFFERENTIAL 成功处理了 <span>41</span> 页，花费 <span>0.016</span> 秒(<span>20.019</span> MB<span>/</span>秒)。
错误日志输出
<span> 1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.790</span> spid56 BackupDatabase: <span>Database</span><span> pratice </span><span> 2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.820</span> spid56 <span>Backup</span>: Media <span>open</span> <span> 3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.820</span> spid56 <span>Backup</span>: Media ready <span>to</span> <span>backup</span> <span> 4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.820</span> spid56 BackupDatabase: <span>Checkpoint</span><span> done </span><span> 5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.820</span> spid56 <span>Backup</span><span>: Scanning allocation bitmaps </span><span> 6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 <span>Backup</span>: Done <span>with</span><span> allocation bitmaps </span><span> 7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 BackupDatabase: <span>Work</span><span> estimates done </span><span> 8</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 <span>Backup</span><span>: Leading metadata div done </span><span> 9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 <span>Backup</span><span>:Copying data </span><span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 <span>Backup</span>: DBReaderCount <span>=</span> <span>1</span> <span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 Started <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 Completed <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.830</span> spid56 Padding MSDA <span>with</span> <span>720896</span><span> bytes </span><span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span> spid56 BackupDatabase: <span>Database</span><span> files done </span><span>15</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span> spid56 慂正灵慄慴慢敳退慂正灵潌g邐<span>%</span>s : <span>Log</span><span> files done </span><span>16</span> : <span>Log</span><span> files done </span><span>17</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span> spid56 <span>Backup</span><span>: Trailing config done </span><span>18</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span> spid56 <span>Backup</span><span>: MBC done </span><span>19</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span><span> spid56 BackupDatabase: Writing history records </span><span>20</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.840</span> 备份 <span>Database</span> differential changes were backed up. <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), pages dumped: <span>139</span>, first LSN: <span>3087</span>:<span>10184</span>:<span>34</span>, last LSN: <span>3087</span>:<span>10200</span>:<span>1</span>, <span>full</span> <span>backup</span> LSN: <span>3087</span>:<span>10159</span>:<span>37</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span><span>, device information: </span><span>21</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.850</span> spid56 Writing <span>backup</span><span> history records </span><span>22</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>42</span>:<span>29.860</span> spid56 BackupDatabase: Finished
跟完整备份的错误日志差不多
日志备份
<span>1</span> <span>--</span><span>日志备份，默认截断日志</span> <span>2</span> <span>Backup</span> <span>Log</span> <span>[</span><span>pratice</span><span>]</span> <span>3</span> <span>To</span> <span>disk</span><span>=</span><span>'</span><span>d:\pratice_Log_20070908.bak</span><span>'</span>
备份时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>29</span><span> 页。 </span><span>2</span> <span>BACKUP</span> <span>LOG</span> 成功处理了 <span>29</span> 页，花费 <span>0.008</span> 秒(<span>27.954</span> MB<span>/</span>秒)。
错误日志输出
<span> 1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.080</span> spid56 BackupLog: <span>database</span><span> pratice
</span><span> 2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.100</span><span> spid56 BackupLog: SafeCheckpoint done
</span><span> 3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 <span>Backup</span>: Media <span>open</span>
<span> 4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 <span>Backup</span>: Media ready <span>to</span> <span>backup</span>
<span> 5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 BackupLog: <span>Work</span><span> estimate done
</span><span> 6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 <span>Backup</span><span>: Leading metadata div done
</span><span> 7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 慂正灵潌g邐<span>%</span>s : <span>Log</span><span> files done
</span><span> 8</span> : <span>Log</span><span> files done
</span><span> 9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 <span>Backup</span><span>: Trailing config done
</span><span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.110</span> spid56 <span>Backup</span><span>: MBC done
</span><span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.120</span><span> spid56 BackupLog: Writing history records
</span><span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.120</span> 备份 <span>Log</span> was backed up. <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), first LSN: <span>3087</span>:<span>9742</span>:<span>1</span>, last LSN: <span>3087</span>:<span>10200</span>:<span>1</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span>, device information: (<span>FILE</span><span>=</span><span>1</span>, TYPE<span>=</span><span>DISK</span>: {<span>'</span><span>d:\pratice_Log_20070908.bak</span><span>'</span>}). This <span>is</span><span> an informational m
</span><span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.120</span> spid56 Writing <span>backup</span><span> history records
</span><span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.140</span> spid56 BackupLog: Finished
日志备份里只有这句话跟差异备份和完整备份不一样
<span>1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>19</span>:<span>48</span>:<span>02.100</span> spid56 BackupLog: SafeCheckpoint done
文件备份
<span>1</span> <span>--</span><span>文件备份</span> <span>2</span> <span>Exec</span> Sp_Helpdb pratice <span>--</span><span>查看数据文件</span> <span>3</span> <span>Backup</span> <span>Database</span><span> pratice </span><span>4</span> <span>File</span><span>=</span><span>'</span><span>pratice</span><span>'</span> <span>--</span><span>数据文件的逻辑名</span> <span>5</span> <span>To</span> <span>disk</span><span>=</span><span>'</span><span>d:\pratice_File_20070908.bak</span><span>'</span>
备份时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>28152</span><span> 页。 </span><span>2</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>2</span><span> 页。 </span><span>3</span> <span>BACKUP</span> <span>DATABASE</span>...<span>FILE</span><span>=name<span>></span> 成功处理了 <span>28154</span> 页，花费 <span>1.663</span> 秒(<span>132.258</span> MB<span>/</span>秒)。</span>
错误日志输出
<span> 1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.480</span> spid56 BackupDatabase: <span>Database</span><span> pratice </span><span> 2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.500</span> spid56 <span>Backup</span>: Media <span>open</span> <span> 3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.500</span> spid56 <span>Backup</span>: Media ready <span>to</span> <span>backup</span> <span> 4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.500</span> spid56 <span>Backup</span><span>: Clearing differential bitmaps </span><span> 5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.500</span> spid56 <span>Backup</span><span>: Bitmaps cleared </span><span> 6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 BackupDatabase: <span>Checkpoint</span><span> done </span><span> 7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 <span>Backup</span><span>: Scanning allocation bitmaps </span><span> 8</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 <span>Backup</span>: Done <span>with</span><span> allocation bitmaps </span><span> 9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 BackupDatabase: <span>Work</span><span> estimates done </span><span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 <span>Backup</span><span>: Leading metadata div done </span><span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.510</span> spid56 <span>Backup</span><span>:Copying data </span><span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.520</span> spid56 <span>Backup</span>: DBReaderCount <span>=</span> <span>1</span> <span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>13.520</span> spid56 Started <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.200</span> spid56 Completed <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>15</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.200</span> spid56 Padding MSDA <span>with</span> <span>65536</span><span> bytes </span><span>16</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.200</span> spid56 BackupDatabase: <span>Database</span><span> files done </span><span>17</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.210</span> spid56 慂正灵慄慴慢敳退慂正灵潌g邐<span>%</span>s : <span>Log</span><span> files done </span><span>18</span> : <span>Log</span><span> files done </span><span>19</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.210</span> spid56 <span>Backup</span><span>: Trailing config done </span><span>20</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.210</span> spid56 <span>Backup</span><span>: MBC done </span><span>21</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.220</span><span> spid56 BackupDatabase: Writing history records </span><span>22</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.220</span> 备份 <span>Database</span> <span>file</span> was backed up. <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), <span>file</span> list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: <span>28171</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span><span>, device informatio </span><span>23</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.230</span> spid56 Writing <span>backup</span><span> history records </span><span>24</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>53</span>:<span>15.240</span> spid56 BackupDatabase: Finished
文件组备份
<span>1</span> <span>--</span><span>文件组备份</span> <span>2</span> <span>Exec</span> Sp_Helpdb pratice <span>--</span><span>查看数据文件</span> <span>3</span> <span>Backup</span> <span>Database</span><span> pratice </span><span>4</span> FileGroup<span>=</span><span>'</span><span>Primary</span><span>'</span> <span>--</span><span>数据文件的逻辑名</span> <span>5</span> <span>To</span> <span>disk</span><span>=</span><span>'</span><span>d:\pratice_FileGroup_20070908.bak</span><span>'</span> <span>6</span> <span>With</span> init
备份时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>28152</span><span> 页。 </span><span>2</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>2</span><span> 页。 </span><span>3</span> <span>BACKUP</span> <span>DATABASE</span>...<span>FILE</span><span>=name<span>></span> 成功处理了 <span>28154</span> 页，花费 <span>1.679</span> 秒(<span>130.998</span> MB<span>/</span>秒)。</span>
错误日志输出
<span> 1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.790</span> spid56 BackupDatabase: <span>Database</span><span> pratice </span><span> 2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.800</span> spid56 <span>Backup</span>: Media <span>open</span> <span> 3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.800</span> spid56 <span>Backup</span>: Media ready <span>to</span> <span>backup</span> <span> 4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.800</span> spid56 <span>Backup</span><span>: Clearing differential bitmaps </span><span> 5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.810</span> spid56 <span>Backup</span><span>: Bitmaps cleared </span><span> 6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.810</span> spid56 BackupDatabase: <span>Checkpoint</span><span> done </span><span> 7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.810</span> spid56 <span>Backup</span><span>: Scanning allocation bitmaps </span><span> 8</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 <span>Backup</span>: Done <span>with</span><span> allocation bitmaps </span><span> 9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 BackupDatabase: <span>Work</span><span> estimates done </span><span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 <span>Backup</span><span>: Leading metadata div done </span><span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 <span>Backup</span><span>:Copying data </span><span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 <span>Backup</span>: DBReaderCount <span>=</span> <span>1</span> <span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>49.820</span> spid56 Started <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 Completed <span>file</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf </span><span>15</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 Padding MSDA <span>with</span> <span>65536</span><span> bytes </span><span>16</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 BackupDatabase: <span>Database</span><span> files done </span><span>17</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 慂正灵慄慴慢敳退慂正灵潌g邐<span>%</span>s : <span>Log</span><span> files done </span><span>18</span> : <span>Log</span><span> files done </span><span>19</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 <span>Backup</span><span>: Trailing config done </span><span>20</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.480</span> spid56 <span>Backup</span><span>: MBC done </span><span>21</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.500</span><span> spid56 BackupDatabase: Writing history records </span><span>22</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.500</span> 备份 <span>Database</span> <span>file</span> was backed up. <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), <span>file</span> list: (pratice(D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice.mdf)), pages dumped: <span>28171</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span><span>, device informatio </span><span>23</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.500</span> spid56 Writing <span>backup</span><span> history records </span><span>24</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>20</span>:<span>58</span>:<span>51.520</span> spid56 BackupDatabase: Finished
在完整备份和差异备份里面为什麽，查询窗口输出的“成功处理页面”比 错误日志里pages dumped的页面还要少？？
就算加上数据库头部的一些关键页面PFS 、DCM、 BCM、 IAM页面加起来也没有那麽多
数据库头部的一些关键页面PFS 、DCM、 BCM、 IAM参考文章：查看SQLSERVER内部数据页面的小插件Internals Viewer
下面是完整备份时候的输出，多了17页
BACKUP DATABASE 成功处理了 28154 页
pages dumped: 28171
其实文章里也有提示：还原/备份时做了些什么
比如，你有个1TB的数据库，但是只储存了100GB的数据，那么备份的时候，只需要备份这100GB的数据。
然而，在还原数据库的时候，你必须重构1TB的数据库，那将意味着大量的时间将被消耗
这篇文章也有一些提示：谁用掉了我的数据库空间？
SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用
查询窗口里输出的是实际就是数据库真正存储数据的页面的数量
BACKUP DATABASE 成功处理了 28154 页
而错误日志就是所有页面，不论是存储数据还是PFS页面还是预留页面
相关实验：下面的实验创建了一个3GB大小的数据库，然后插入了一些数据到数据库中
（1）不对数据库进行收缩，直接备份数据库，然后还原
（2）先对数据库进行收缩，备份数据库，然后还原
<span>USE</span><span> master
</span><span>GO</span>
<span>--</span><span>创建数据库 初始大小为3G</span>
<span>CREATE</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span> <span>ON</span> <span>PRIMARY</span><span>
( NAME </span><span>=</span> N<span>'</span><span>testbackupdb</span><span>'</span><span>,
FILENAME </span><span>=</span> N<span>'</span><span>D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb.mdf</span><span>'</span> , SIZE <span>=</span> 3072000KB , FILEGROWTH <span>=</span><span> 1024KB )
</span><span>LOG</span> <span>ON</span><span>
( NAME </span><span>=</span> N<span>'</span><span>testbackupdb_log</span><span>'</span><span>,
FILENAME </span><span>=</span> N<span>'</span><span>D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\testbackupdb_log.ldf</span><span>'</span> , SIZE <span>=</span> 1024KB , FILEGROWTH <span>=</span> <span>10</span><span>%</span><span>)
</span><span>GO</span>
<span>--</span><span>创建表</span>
<span>USE</span> <span>[</span><span>testbackupdb</span><span>]</span>
<span>GO</span>
<span>CREATE</span> <span>TABLE</span> aa (id <span>INT</span>,NAME <span>NVARCHAR</span>(<span>MAX</span><span>))
</span><span>GO</span>
<span>--</span><span>插入数据</span>
<span>INSERT</span> <span>INTO</span><span> aa
</span><span>SELECT</span> <span>1</span>,<span>REPLICATE</span>(<span>'</span><span>aa</span><span>'</span>,<span>500</span><span>)
</span><span>GO</span> <span>10000</span>
<span>--</span><span>查看表使用的空间 大概26MB</span>
<span>sys.sp_spaceused aa
sys.sp_helpdb </span><span>[</span><span>testbackupdb</span><span>]</span>
<span>--</span><span>备份数据库</span>
<span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span> <span>TO</span> <span>DISK</span> <span>=</span><span>'</span><span>d:\testbackupdb_fullbackup.bak</span><span>'</span>
<span>--</span><span>删除数据库</span>
<span>DROP</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span>
<span>--</span><span>1、还原数据库 没有收缩数据库的情况下</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>
<span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span> <span>FROM</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\testbackupdb_fullbackup.bak</span><span>'</span> <span>WITH</span> <span>FILE</span> <span>=</span> <span>1</span><span>,
MOVE N</span><span>'</span><span>testbackupdb</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\testbackupdb.mdf</span><span>'</span><span>,
MOVE N</span><span>'</span><span>testbackupdb_log</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\testbackupdb_1.ldf</span><span>'</span><span>,
NOUNLOAD, </span><span>REPLACE</span>, STATS <span>=</span> <span>10</span>
<span>GO</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span><span>
SQL Server 执行时间:
CPU 时间 </span><span>=</span> <span>140</span> 毫秒，占用时间 <span>=</span> <span>3024</span><span> 毫秒。
SQL Server 分析和编译时间:
CPU 时间 </span><span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。
</span><span>--</span><span>-----------------------------------------------------------------------------------------</span><span>
--</span><span>收缩数据库为100MB</span>
<span>USE</span> <span>[</span><span>testbackupdb</span><span>]</span>
<span>GO</span>
<span>DBCC</span> SHRINKFILE (N<span>'</span><span>testbackupdb</span><span>'</span> , <span>100</span><span>)
</span><span>GO</span>
<span>--</span><span>备份数据库</span>
<span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span> <span>TO</span> <span>DISK</span> <span>=</span><span>'</span><span>d:\testbackupdb_fullbackup.bak</span><span>'</span>
<span>--</span><span>删除数据库</span>
<span>DROP</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span>
<span>--</span><span>2、还原数据库 没有收缩数据库的情况下</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>ON</span>
<span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>testbackupdb</span><span>]</span> <span>FROM</span> <span>DISK</span> <span>=</span> N<span>'</span><span>D:\testbackupdb_fullbackup.bak</span><span>'</span> <span>WITH</span> <span>FILE</span> <span>=</span> <span>1</span><span>,
MOVE N</span><span>'</span><span>testbackupdb</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\testbackupdb.mdf</span><span>'</span><span>,
MOVE N</span><span>'</span><span>testbackupdb_log</span><span>'</span> <span>TO</span> N<span>'</span><span>D:\testbackupdb_1.ldf</span><span>'</span><span>,
NOUNLOAD, </span><span>REPLACE</span>, STATS <span>=</span> <span>10</span>
<span>GO</span>
<span>SET</span> <span>STATISTICS</span> TIME <span>OFF</span><span>
SQL Server 执行时间:
CPU 时间 </span><span>=</span> <span>140</span> 毫秒，占用时间 <span>=</span> <span>1549</span><span> 毫秒。
SQL Server 分析和编译时间:
CPU 时间 </span><span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>10</span><span> 毫秒。
</span><span>--</span><span>查看数据库大小</span>
<span>sys.sp_spaceused aa
sys.sp_helpdb </span><span>[</span><span>testbackupdb</span><span>]</span>
<span>--</span><span>结论：备份数据库前先收缩，还原数据库的时候会比较快</span><span>
--</span><span>因为收缩了数据库之后，数据库的size变成了收缩之后的大小，其实还原数据库的时候</span><span>
--</span><span>SQLSERVER根据根据这个初始大小先初始化出这个数据库初始大小的磁盘空间，然后再复制数据</span>
View Code
结论：备份数据库前先收缩，还原数据库的时候会比较快
因为收缩了数据库之后，数据库的size变成了收缩之后的大小，其实还原数据库的时候
SQLSERVER根据根据这个初始大小先初始化出这个数据库初始大小的磁盘空间，然后再复制数据
数据库还原时候的信息
执行下面脚本先kill掉所有连接到pratice数据库的连接
<span> 1</span> <span>--</span><span>执行下面脚本先kill掉所有连接到pratice数据库的连接</span> <span> 2</span> <span>USE</span><span> master </span><span> 3</span> <span>GO</span> <span> 4</span> <span>DECLARE</span> <span>@spid</span> <span>INT</span> <span> 5</span> <span>DECLARE</span> <span>@count</span> <span>INT</span> <span> 6</span> <span>WHILE</span> <span>@count</span> <span>></span> <span>0</span> <span> 7</span> <span>BEGIN</span> <span> 8</span> <span>SELECT</span> <span>@count</span> <span>=</span> <span>COUNT</span>(<span>*</span><span>) </span><span> 9</span> <span>FROM</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>10</span> <span>WHERE</span> <span>[</span><span>dbid</span><span>]</span> <span>=</span> <span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span><span>) </span><span>11</span> <span>SELECT</span> <span>@spid</span> <span>=</span> <span>[</span><span>spid</span><span>]</span> <span>12</span> <span>FROM</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>13</span> <span>WHERE</span> <span>[</span><span>dbid</span><span>]</span> <span>=</span> <span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span><span>) </span><span>14</span> <span>EXEC</span>(<span>'</span><span>kill </span><span>'</span><span>+</span><span>@spid</span><span>) </span><span>15</span> <span>END</span>
<span>1</span> <span>--</span><span>完整备份还原</span> <span>2</span> <span>RESTORE</span> <span>DATABASE</span> <span>[</span><span>pratice</span><span>]</span> <span>3</span> <span>FROM</span> <span>DISK</span> <span>=</span><span>'</span><span>D:\pratice_backup_201207160152.bak</span><span>'</span> <span>WITH</span> <span>REPLACE</span>
还原的时候，查询窗口的输出
<span>1</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>28152</span><span> 页。 </span><span>2</span> 已为数据库 <span>'</span><span>pratice</span><span>'</span>，文件 <span>'</span><span>pratice_log</span><span>'</span> (位于文件 <span>1</span> 上)处理了 <span>2</span><span> 页。 </span><span>3</span> <span>RESTORE</span> <span>DATABASE</span> 成功处理了 <span>28154</span> 页，花费 <span>2.267</span> 秒(<span>97.020</span> MB<span>/</span>秒)。
错误日志输出
<span>1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.490</span> spid56 RestoreDatabase: <span>Database</span><span> pratice
</span><span>2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.490</span> spid56 Opening <span>backup</span> <span>set</span> <span>--</span><span>打开备份集</span>
<span>3</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.500</span> spid56 SetTargetRestoreAge: <span>0</span>
<span>4</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.500</span> spid56 <span>Restore</span>: Configuration div loaded <span>--</span><span>读取数据库配置，例如数据库的初始大小</span>
<span>5</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.500</span> spid56 <span>Restore</span>: <span>Backup</span> <span>set</span> <span>is</span> <span>open</span>
<span>6</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.500</span> spid56 <span>Restore</span><span>: Planning begins
</span><span>7</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.520</span> spid56 Halting FullText crawls <span>on</span> <span>database</span> pratice <span>--</span><span>因为pratice数据库使用了全文搜索，这里会Halting停止全文搜索抓取</span>
<span>8</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.520</span> spid56 Dismounting FullText catalogs <span>--</span><span>卸载FullText目录</span>
<span>9</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.520</span> spid56 X<span>-</span>locking <span>database</span>: pratice <span>--</span><span>用排他锁锁住pratice数据库</span>
<span>10</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>20</span>:<span>47.520</span> spid56 Resuming <span>any</span><span> halted fulltext crawls 恢复全文搜索抓取
</span><span>11</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.310</span> spid56 RestoreDatabase: <span>Database</span><span> pratice
</span><span>12</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.310</span> spid56 Opening <span>backup</span> <span>set</span>
<span>13</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.320</span> spid56 SetTargetRestoreAge: <span>0</span>
<span>14</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.320</span> spid56 <span>Restore</span><span>: Configuration div loaded
</span><span>15</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.320</span> spid56 <span>Restore</span>: <span>Backup</span> <span>set</span> <span>is</span> <span>open</span>
<span>16</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.320</span> spid56 <span>Restore</span><span>: Planning begins
</span><span>17</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span> spid56 Halting FullText crawls <span>on</span> <span>database</span><span> pratice
</span><span>18</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span><span> spid56 Dismounting FullText catalogs
</span><span>19</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span> spid56 X<span>-</span>locking <span>database</span><span>: pratice
</span><span>20</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span> spid56 <span>Restore</span><span>: Planning complete
</span><span>21</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span> spid56 <span>Restore</span>: BeginRestore (offline) <span>on</span><span> pratice
</span><span>22</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.340</span> spid56 AppDomain <span>2</span> (pratice.dbo<span>[</span><span>runtime</span><span>]</span>.<span>1</span>) <span>is</span> marked <span>for</span> unload due <span>to</span> common language runtime (CLR) <span>or</span> security data definition language (DDL) operations. <span>--</span><span>卸载CLR</span>
<span>23</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.350</span> spid20s AppDomain <span>2</span> (pratice.dbo<span>[</span><span>runtime</span><span>]</span>.<span>1</span><span>) unloaded.
</span><span>24</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.360</span> spid56 <span>Restore</span>: PreparingContainers <span>--</span><span>准备容器</span>
<span>25</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.390</span> spid56 <span>Restore</span>: Containers are ready <span>--</span><span>容器准备完成</span>
<span>26</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.390</span> spid56 Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf <span>from</span> page <span>1</span> <span>to</span> <span>6528</span> (<span>0x2000</span> <span>to</span> <span>0x3300000</span>) <span>--</span><span>日志填零初始化</span>
<span>27</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.430</span> spid56 <span>Restore</span>: Restoring <span>backup</span> <span>set</span>
<span>28</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.430</span> spid56 <span>Restore</span>: Transferring data <span>to</span> pratice <span>--</span><span>传输数据到pratice数据库</span>
<span>29</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>28.570</span> spid56 Zeroing completed <span>on</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
</span><span>30</span> http:<span>//</span>blogs.msdn.com<span>/</span>b<span>/</span>apgcdsd<span>/</span>archive<span>/</span><span>2013</span><span>/</span><span>06</span><span>/</span><span>17</span><span>/</span>sql<span>-</span>server<span>-</span><span>log</span><span>-</span><span>write.aspx
</span><span>31</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.670</span> spid56 <span>Restore</span>: Waiting <span>for</span> <span>log</span> zero <span>on</span><span> pratice
</span><span>32</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.680</span> spid56 <span>Restore</span>: LogZero complete <span>--</span><span>日志填零完毕</span>
<span>33</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.680</span> spid56 SetTargetRestoreAge: <span>0</span>
<span>34</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.700</span> spid56 FileHandleCache: <span>0</span> files opened. CacheSize: <span>12</span>
<span>35</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.700</span> spid56 <span>Restore</span>: Data transfer complete <span>on</span> pratice <span>--</span><span>数据传输完毕</span>
<span>36</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.710</span> spid56 <span>Restore</span>: <span>Backup</span> <span>set</span> restored <span>--</span><span>备份集已经还原</span>
<span>37</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.720</span> spid56 Starting up <span>database</span> <span>'</span><span>pratice</span><span>'</span>. <span>--</span><span>启动pratice数据库</span>
<span>38</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.780</span> spid56 The <span>database</span> <span>'</span><span>pratice</span><span>'</span> <span>is</span> marked RESTORING <span>and</span> <span>is</span> <span>in</span> a state that does <span>not</span> allow recovery <span>to</span> be run. <span>--</span><span>数据库处于正在还原状态，还未能完全恢复进行运行</span>
<span>39</span> <span>--</span><span>SQLSERVER数据库主要状态和切换路径 http://www.cnblogs.com/lyhabc/archive/2012/09/15/2687076.html</span>
<span>40</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.780</span> spid56 <span>Restore</span><span>-</span>Redo begins <span>on</span> <span>database</span> pratice <span>--</span><span>重做日志!!</span>
<span>41</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.790</span> spid56 RunOfflineRedo logIter.Init(): FirstLsn(PruId: <span>0</span>): <span>0xc0f</span>:<span>0x27dd</span>:<span>0x25</span>
<span>42</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.790</span> spid56 RunOfflineRedo logIter.Init(): LastLsn(PruId: <span>0</span>): <span>0xc0f</span>:<span>0x27ee</span>:<span>0x1</span>
<span>43</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.790</span> spid56 OfflineRollforward: StopLsn<span>/</span>LastLsn(PruId: <span>0</span>): <span>0xc0f</span>:<span>0x27ee</span>:<span>0x1</span>
<span>44</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.790</span> spid56 Rollforward complete <span>on</span> <span>database</span> pratice <span>--</span><span>为什麽会有Rollforward http://www.cnblogs.com/lyhabc/p/3448449.html</span>
<span>45</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.810</span> spid56 <span>Restore</span>: Done <span>with</span> fixups <span>--</span><span>checkdb检查完毕 fixup：修正</span>
<span>46</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.810</span> spid56 <span>Restore</span>: Transitioning <span>database</span> <span>to</span><span> ONLINE
</span><span>47</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.810</span> spid56 <span>Restore</span>: Restarting <span>database</span> <span>for</span><span> ONLINE
</span><span>48</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.820</span> spid56 Starting up <span>database</span> <span>'</span><span>pratice</span><span>'</span><span>.
</span><span>49</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.860</span> spid56 FixupLogTail(progress) zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf <span>from</span> <span>0x1efdc00</span> <span>to</span> <span>0x1efe000</span>. <span>--</span><span>修正日志尾部</span>
<span>50</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.860</span> spid56 Zeroing D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf <span>from</span> page <span>3967</span> <span>to</span> <span>4447</span> (<span>0x1efe000</span> <span>to</span> <span>0x22be000</span><span>)
</span><span>51</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.870</span> spid56 Zeroing completed <span>on</span><span> D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\pratice_log.ldf
</span><span>52</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.870</span><span> spid56 PostRestoreContainerFixups: fixups complete
</span><span>53</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.870</span> spid56 CHECKDB <span>for</span> <span>database</span> <span>'</span><span>pratice</span><span>'</span> finished without errors <span>on</span> <span>2013</span><span>-</span><span>11</span><span>-</span><span>23</span> <span>23</span>:<span>35</span>:<span>18.670</span> (local time). This <span>is</span> an informational message <span>only</span>; no <span>user</span> action <span>is</span><span> required.
</span><span>54</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.880</span> spid56 PostRestoreReplicationFixup <span>for</span> pratice starts <span>--</span><span>复制相关</span>
<span>55</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.970</span> spid56 PostRestoreReplicationFixup <span>for</span><span> pratice complete
</span><span>56</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.970</span> spid56 <span>Restore</span>: <span>Database</span> <span>is</span> restarted <span>--</span><span>数据库已经重启</span>
<span>57</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.970</span> 备份 <span>Restore</span> <span>is</span> complete <span>on</span> <span>database</span> <span>'</span><span>pratice</span><span>'</span>. The <span>database</span> <span>is</span> now available. <span>--</span><span>数据库还原完成，数据库现在是可用状态</span>
<span>58</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.980</span> spid56 Resuming <span>any</span> halted fulltext crawls <span>--</span><span>恢复全文搜索抓取</span>
<span>59</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.990</span> spid56 <span>Restore</span>: Writing history records <span>--</span><span>还原数据库记录写入msdb里相关的表</span>
<span>60</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.990</span> 备份 <span>Database</span> was restored: <span>Database</span>: pratice, creation date(time): <span>2013</span><span>/</span><span>11</span><span>/</span><span>19</span>(<span>19</span>:<span>35</span>:<span>23</span>), first LSN: <span>3087</span>:<span>10205</span>:<span>37</span>, last LSN: <span>3087</span>:<span>10222</span>:<span>1</span>, <span>number</span> <span>of</span> <span>dump</span> devices: <span>1</span>, device information: (<span>FILE</span><span>=</span><span>1</span>, TYPE<span>=</span><span>DISK</span>: {<span>'</span><span>D:\pratice_backup_201207160152.bak</span><span>'</span>}). Informational
<span>61</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>30.990</span> spid56 Writing <span>backup</span> history records <span>--</span><span>备份历史记录写入到错误日志</span>
<span>62</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>31.010</span> spid56 <span>Restore</span>: Done <span>with</span><span> MSDB maintenance
</span><span>63</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>21</span>:<span>21</span>:<span>31.010</span> spid56 RestoreDatabase: Finished
还有两个跟踪标志
跟踪标志：3014、3604
为了节省篇幅我就补贴出刚才两个跟踪标志的信息了，只贴出3014、3604这两个跟踪标志输出的信息
<span>1</span> <span>dbcc</span> traceon(<span>3014</span>, <span>3604</span>, <span>-</span><span>1</span>)
完整备份
<span> 1</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>22</span>:<span>22</span>:<span>03.300</span> spid56 Calculating expected total data <span>to</span> write <span>--</span><span>计算需要写入备份设备的数据量</span> <span> 2</span> <span>2013</span><span>-</span><span>12</span><span>-</span><span>01</span> <span>22</span>:<span>22</span>:<span>03.300</span> spid56 Fid(<span>1</span>) E
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
說明InnoDB重做日誌和撤消日誌的作用。Apr 15, 2025 am 12:16 AMInnoDB使用redologs和undologs確保數據一致性和可靠性。 1.redologs記錄數據頁修改，確保崩潰恢復和事務持久性。 2.undologs記錄數據原始值，支持事務回滾和MVCC。
在解釋輸出（類型，鍵，行，額外）中要查找的關鍵指標是什麼？Apr 15, 2025 am 12:15 AMEXPLAIN命令的關鍵指標包括type、key、rows和Extra。 1）type反映查詢的訪問類型，值越高效率越高，如const優於ALL。 2）key顯示使用的索引，NULL表示無索引。 3）rows預估掃描行數，影響查詢性能。 4）Extra提供額外信息，如Usingfilesort提示需要優化。
在解釋中使用臨時狀態以及如何避免它是什麼？Apr 15, 2025 am 12:14 AMUsingtemporary在MySQL查詢中表示需要創建臨時表，常見於使用DISTINCT、GROUPBY或非索引列的ORDERBY。可以通過優化索引和重寫查詢避免其出現，提升查詢性能。具體來說，Usingtemporary出現在EXPLAIN輸出中時，意味著MySQL需要創建臨時表來處理查詢。這通常發生在以下情況：1)使用DISTINCT或GROUPBY時進行去重或分組；2)ORDERBY包含非索引列時進行排序；3)使用複雜的子查詢或聯接操作。優化方法包括：1)為ORDERBY和GROUPB
描述不同的SQL交易隔離級別（讀取未讀取，讀取，可重複的讀取，可序列化）及其在MySQL/InnoDB中的含義。Apr 15, 2025 am 12:11 AMMySQL/InnoDB支持四種事務隔離級別：ReadUncommitted、ReadCommitted、RepeatableRead和Serializable。 1.ReadUncommitted允許讀取未提交數據，可能導致臟讀。 2.ReadCommitted避免臟讀，但可能發生不可重複讀。 3.RepeatableRead是默認級別，避免臟讀和不可重複讀，但可能發生幻讀。 4.Serializable避免所有並發問題，但降低並發性。選擇合適的隔離級別需平衡數據一致性和性能需求。
MySQL與其他數據庫：比較選項Apr 15, 2025 am 12:08 AMMySQL適合Web應用和內容管理系統，因其開源、高性能和易用性而受歡迎。 1)與PostgreSQL相比，MySQL在簡單查詢和高並發讀操作上表現更好。 2)相較Oracle，MySQL因開源和低成本更受中小企業青睞。 3)對比MicrosoftSQLServer，MySQL更適合跨平台應用。 4)與MongoDB不同，MySQL更適用於結構化數據和事務處理。
MySQL索引基數如何影響查詢性能？Apr 14, 2025 am 12:18 AMMySQL索引基数对查询性能有显著影响：1.高基数索引能更有效地缩小数据范围，提高查询效率；2.低基数索引可能导致全表扫描，降低查询性能；3.在联合索引中，应将高基数列放在前面以优化查询。
MySQL：新用戶的資源和教程Apr 14, 2025 am 12:16 AMMySQL學習路徑包括基礎知識、核心概念、使用示例和優化技巧。 1)了解表、行、列、SQL查詢等基礎概念。 2)學習MySQL的定義、工作原理和優勢。 3)掌握基本CRUD操作和高級用法，如索引和存儲過程。 4)熟悉常見錯誤調試和性能優化建議，如合理使用索引和優化查詢。通過這些步驟，你將全面掌握MySQL的使用和優化。
現實世界Mysql：示例和用例Apr 14, 2025 am 12:15 AMMySQL在現實世界的應用包括基礎數據庫設計和復雜查詢優化。 1)基本用法：用於存儲和管理用戶數據，如插入、查詢、更新和刪除用戶信息。 2)高級用法：處理複雜業務邏輯，如電子商務平台的訂單和庫存管理。 3)性能優化：通過合理使用索引、分區表和查詢緩存來提升性能。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
R.E.P.O.能量晶體解釋及其做什麼（黃色晶體）
4 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
4 週前By尊渡假赌尊渡假赌尊渡假赌
刺客信條陰影：貝殼謎語解決方案
2 週前ByDDD
R.E.P.O.如果您聽不到任何人，如何修復音頻
4 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解鎖Myrise中的所有內容
1 個月前By尊渡假赌尊渡假赌尊渡假赌
熱工具
SecLists
SecLists是最終安全測試人員的伙伴。它是一個包含各種類型清單的集合，這些清單在安全評估過程中經常使用，而且都在一個地方。 SecLists透過方便地提供安全測試人員可能需要的所有列表，幫助提高安全測試的效率和生產力。清單類型包括使用者名稱、密碼、URL、模糊測試有效載荷、敏感資料模式、Web shell等等。測試人員只需將此儲存庫拉到新的測試機上，他就可以存取所需的每種類型的清單。
Atom編輯器mac版下載
最受歡迎的的開源編輯器
DVWA
Damn Vulnerable Web App (DVWA) 是一個PHP/MySQL的Web應用程序，非常容易受到攻擊。它的主要目標是成為安全專業人員在合法環境中測試自己的技能和工具的輔助工具，幫助Web開發人員更好地理解保護網路應用程式的過程，並幫助教師/學生在課堂環境中教授/學習Web應用程式安全性。 DVWA的目標是透過簡單直接的介面練習一些最常見的Web漏洞，難度各不相同。請注意，該軟體中
mPDF
mPDF是一個PHP庫，可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件，並處理不同的語言。與原始腳本如HTML2FPDF相比，它的速度較慢，並且在使用Unicode字體時產生的檔案較大，但支援CSS樣式等，並進行了大量增強。支援幾乎所有語言，包括RTL（阿拉伯語和希伯來語）和CJK（中日韓）。支援嵌套的區塊級元素（如P、DIV），
SAP NetWeaver Server Adapter for Eclipse
將Eclipse與SAP NetWeaver應用伺服器整合。
