SQLSERVER数据库备份操作和还原操作做了什么
s
sqlserver
什么
备份
操作
数据库
还原
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>登录后复制
结论：备份数据库前先收缩，还原数据库的时候会比较快
因为收缩了数据库之后，数据库的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
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
2 周前
By DDD
R.E.P.O.如果您听不到任何人，如何修复音频
3 周前
By 尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解锁Myrise中的所有内容
4 周前
By 尊渡假赌尊渡假赌尊渡假赌
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
sqlserver怎么导入mdf文件
Apr 08, 2024 am 11:41 AM
导入步骤如下：将 MDF 文件复制到 SQL Server 的数据目录（通常为 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，打开数据库并选择“附加”。单击“添加”按钮，选择 MDF 文件。确认数据库名称，点击确定按钮即可。
如何在 PHP 中使用 MySQL 备份和还原？
Jun 03, 2024 pm 12:19 PM
在PHP中备份和还原MySQL数据库可通过以下步骤实现：备份数据库：使用mysqldump命令转储数据库为SQL文件。还原数据库：使用mysql命令从SQL文件还原数据库。
mysql和sqlserver语法有什么区别
Apr 22, 2024 pm 06:33 PM
MySQL 和 SQL Server 的语法差异主要体现在数据库对象、数据类型、SQL 语句和其他方面。数据库对象差异包括存储引擎和文件组的指定方式、索引和约束的创建。数据类型差异涉及数值类型、字符类型和日期时间类型的差异。SQL 语句差异体现在结果集限制、数据插入、更新和删除操作等方面。其他差异还包括标识列、视图和存储过程的创建方式。了解这些差异对于使用不同的数据库系统时避免错误非常重要。
Hibernate 如何实现多态映射？
Apr 17, 2024 pm 12:09 PM
Hibernate多态映射可映射继承类到数据库，提供以下映射类型：joined-subclass：为子类创建单独表，包含父类所有列。table-per-class：为子类创建单独表，仅包含子类特有列。union-subclass：类似joined-subclass，但父类表联合所有子类列。
navicat数据库文件在哪
Apr 23, 2024 am 10:57 AM
Navicat 数据库配置文件的存储位置因操作系统而异：Windows：用户特定路径为 %APPDATA%\PremiumSoft\Navicat\macOS：用户特定路径为 ~/Library/Application Support/Navicat\Linux：用户特定路径为 ~/.config/navicat\配置文件名称包含连接类型，如 navicat_mysql.ini。这些配置文件存储数据库连接信息、查询历史和 SSH 设置。
iOS 18 新增'已恢复”相册功能 可找回丢失或损坏的照片
Jul 18, 2024 am 05:48 AM
苹果公司最新发布的iOS18、iPadOS18以及macOSSequoia系统为Photos应用增添了一项重要功能，旨在帮助用户轻松恢复因各种原因丢失或损坏的照片和视频。这项新功能在Photos应用的"工具"部分引入了一个名为"已恢复"的相册，当用户设备中存在未纳入其照片库的图片或视频时，该相册将自动显示。"已恢复"相册的出现为因数据库损坏、相机应用未正确保存至照片库或第三方应用管理照片库时照片和视频丢失提供了解决方案。用户只需简单几步
深入解析HTML如何读取数据库
Apr 09, 2024 pm 12:36 PM
HTML无法直接读取数据库，但可以通过JavaScript和AJAX实现。其步骤包括建立数据库连接、发送查询、处理响应和更新页面。本文提供了利用JavaScript、AJAX和PHP来从MySQL数据库读取数据的实战示例，展示了如何在HTML页面中动态显示查询结果。该示例使用XMLHttpRequest建立数据库连接，发送查询并处理响应，从而将数据填充到页面元素中，实现了HTML读取数据库的功能。
如何在PHP中处理数据库连接错误
Jun 05, 2024 pm 02:16 PM
PHP中处理数据库连接报错，可以使用以下步骤：使用mysqli_connect_errno()获取错误代码。使用mysqli_connect_error()获取错误消息。通过捕获并记录这些错误信息，可以轻松识别并解决数据库连接问题，确保应用程序的顺畅运行。
