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脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
<🎜>：種植花園 - 完整的突變指南
3 週前
By DDD
<🎜>：泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
如何修復KB5055612無法在Windows 10中安裝？
3 週前
By DDD
北端：融合系統，解釋
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆樹的耳語 - 如何解鎖抓鉤
3 週前
By 尊渡假赌尊渡假赌尊渡假赌
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
如何在 PHP 中使用 MySQL 備份和還原？
Jun 03, 2024 pm 12:19 PM
在PHP中備份和還原MySQL資料庫可透過以下步驟實現：備份資料庫：使用mysqldump指令轉儲資料庫為SQL檔。還原資料庫：使用mysql指令從SQL檔案還原資料庫。
iOS 18 新增「已復原」相簿功能 可找回遺失或損壞的照片
Jul 18, 2024 am 05:48 AM
蘋果公司最新發布的iOS18、iPadOS18以及macOSSequoia系統為Photos應用程式增添了一項重要功能，旨在幫助用戶輕鬆恢復因各種原因遺失或損壞的照片和影片。這項新功能在Photos應用的"工具"部分引入了一個名為"已恢復"的相冊，當用戶設備中存在未納入其照片庫的圖片或影片時，該相冊將自動顯示。 "已恢復"相簿的出現為因資料庫損壞、相機應用未正確保存至照片庫或第三方應用管理照片庫時照片和視頻丟失提供了解決方案。使用者只需簡單幾步
如何在PHP中處理資料庫連線錯誤
Jun 05, 2024 pm 02:16 PM
PHP處理資料庫連線報錯，可以使用下列步驟：使用mysqli_connect_errno()取得錯誤代碼。使用mysqli_connect_error()取得錯誤訊息。透過擷取並記錄這些錯誤訊息，可以輕鬆識別並解決資料庫連接問題，確保應用程式的順暢運作。
在PHP中使用MySQLi建立資料庫連線的詳盡教學
Jun 04, 2024 pm 01:42 PM
如何在PHP中使用MySQLi建立資料庫連線：包含MySQLi擴充（require_once）建立連線函數（functionconnect_to_db）呼叫連線函數（$conn=connect_to_db()）執行查詢（$result=$conn->query()）關閉連線（ $conn->close()）
Astar質押原理、收益拆解、空投項目及策略 & 操作保姆級攻略
Jun 25, 2024 pm 07:09 PM
目錄Astar Dapp 質押原理質押收益 拆解潛在空投項目：AlgemNeurolancheHealthreeAstar Degens DAOVeryLongSwap 質押策略 & 操作“AstarDapp質押”今年初已升級至V3版本，對質押收益規則做了不少調整。目前首個質押週期已結束，第二質押週期的「投票」子週期剛開始。若要獲得「額外獎勵」收益，需掌握此關鍵階段（預計持續至6月26日，現餘不到5天）。我將細緻拆解Astar質押收益，
如何在 Golang 中使用資料庫回呼函數？
Jun 03, 2024 pm 02:20 PM
在Golang中使用資料庫回呼函數可以實現：在指定資料庫操作完成後執行自訂程式碼。透過單獨的函數新增自訂行為，無需編寫額外程式碼。回調函數可用於插入、更新、刪除和查詢操作。必須使用sql.Exec、sql.QueryRow或sql.Query函數才能使用回呼函數。
如何在 Golang 中將 JSON 資料保存到資料庫中？
Jun 06, 2024 am 11:24 AM
可以透過使用gjson函式庫或json.Unmarshal函數將JSON資料儲存到MySQL資料庫中。 gjson函式庫提供了方便的方法來解析JSON字段，而json.Unmarshal函數需要一個目標類型指標來解組JSON資料。這兩種方法都需要準備SQL語句和執行插入操作來將資料持久化到資料庫中。
PHP與不同資料庫的連接：MySQL、PostgreSQL、Oracle和更多
Jun 01, 2024 pm 03:02 PM
PHP連接資料庫指南：MySQL：安裝MySQLi擴展，建立連線（servername、username、password、dbname）。 PostgreSQL：安裝PgSQL擴展，建立連線（host、dbname、user、password）。 Oracle：安裝OracleOCI8擴展，建立連線（servername、username、password）。實戰案例：取得MySQL資料、PostgreSQL查詢、OracleOCI8更新記錄。
