[转]Backup and restore history details

比眉伴天荒 2021-12-22 07:47 202阅读 0赞

原文地址：[http://www.sqlbackuprestore.com/backuprestorehistorytables.htm][http_www.sqlbackuprestore.com_backuprestorehistorytables.htm]

SQL Server maintains a set of tables in the **msdb** database, that stores details of all backups and restores that have been performed. Even if you are using a 3rd party backup application, the details are stored if the applications use the SQL Server [Virtual Device Interface][] (VDI) to perform the backups and restores.  
  
The tables that store the details are:

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;"><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;"><font>·</font></span></span> </td> 
   <td><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">backupset &nbsp;<br></span></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;"><font>·</font></span> </td> 
   <td><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">backupfilegroup</span> <span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;">(SQL Server 2005 upwards)<span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">&nbsp;<br></span></span></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;"><font>·</font></span> </td> 
   <td><span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">suspect_pages</span> <span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;">(SQL Server 2005 upwards)<span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">&nbsp;<br></span></span></td> 
  </tr> 
 </tbody> 
</table>

You can find out more about each table from Books Online.  
  
Here's a script to find out the most recent backups for each database.

<table> 
 <tbody> 
  <tr> 
   <td>SELECT b.name, a.type, MAX(a.backup_finish_date) lastbackup<br>FROM msdb..backupset a<br>INNER JOIN master..sysdatabases b ON a.database_name COLLATE DATABASE_DEFAULT = b.name COLLATE DATABASE_DEFAULT<br>GROUP BY b.name, a.type<br>ORDER BY b.name, a.type</td> 
  </tr> 
 </tbody> 
</table>

Another situation I find myself having to query these tables is when my log shipping breaks on the secondary server due to an out-of-sequence log. I could run something like this to find out the last 10 transaction log backups for a particular database:

<table> 
 <tbody> 
  <tr> 
   <td>SELECT TOP 20 b.physical_device_name, a.backup_start_date, a.first_lsn, a.user_name FROM msdb..backupset a<br>INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id<br>WHERE a.type = 'L'<br>ORDER BY a.backup_finish_date DESC</td> 
  </tr> 
 </tbody> 
</table>

The physical\_device\_name  tells me which file I will need to continue the transaction log restore sequence, using the first\_lsn  value as a reference (the value that the secondary server requires to continue restoring the transaciton logs). The user\_name  tells me who I need to have some serious words with!  
  
Or say I need to restore an entire sequence of transaction logs created after 10-Jan-2008, up to a point in time on 16-Jan-2008 3:30 AM. I could use the following query to display the files I need to restore in sequence:

<table> 
 <tbody> 
  <tr> 
   <td>SELECT b.physical_device_name, a.backup_set_id, b.family_sequence_number, a.position, a.backup_start_date, a.backup_finish_date<br>FROM msdb..backupset a<br>INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id<br>WHERE a.database_name = 'AdventureWorks'<br>AND a.type = 'L'<br>AND a.backup_start_date &gt; '10-Jan-2007'<br>AND a.backup_finish_date &lt; '16-Jan-2009 3:30'<br>ORDER BY a.backup_start_date, b.family_sequence_number</td> 
  </tr> 
 </tbody> 
</table>

The family\_sequence\_number  tells me how may files are containined in each backup set, and position  tells me which backup set in a file I need to use, when the backup file contains multiple backup sets.  
  
Now, these tables can grow pretty large over time. SQL Server provides the **sp\_delete\_backuphistory**  stored procedure to delete historical data. E.g.

<table> 
 <tbody> 
  <tr> 
   <td>EXEC msdb..sp_delete_backuphistory '1-Jan-2005'</td> 
  </tr> 
 </tbody> 
</table>

deletes all details of backup and restore processes created before January 1 2005. Note that this stored procedure exists in the **msdb**  database, not the **master**  database.

<table> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>&nbsp;&nbsp;</td> 
   <td><strong>NOTE:</strong> In SQL Server 2000, the performance of this stored procedure is pretty dismal when deleting large number of records, due to the use of cursors. Here's a stored procedure you can try, adapted from a <span style="font-family:Helvetica, Arial;color:#0000ff;font-size:10pt;"><u><span><a href="http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201" rel="nofollow">forum post</a></span></u></span> <span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;">on sqlteam.com, that does not use cursors:<br><br></span> 
    <table> 
     <tbody> 
      <tr> 
       <td><br>CREATE PROC sp_delete_backuphistory_alt<br>&nbsp;&nbsp;&nbsp;(@DaysToRetain INT)<br>AS<br><br>SET NOCOUNT ON<br><br>DECLARE&nbsp;&nbsp;&nbsp;@intErrNo int<br>DECLARE @dtCutoff datetime<br><br>BEGIN TRANSACTION DeleteBackupHistory<br><br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..restorefile<br>&nbsp;&nbsp;&nbsp;FROM msdb..restorefile rf<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id<br>&nbsp;&nbsp;&nbsp;WHERE bs.backup_finish_date &lt; (GETDATE() - @DaysToRetain)<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br><br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..restorefilegroup<br>&nbsp;&nbsp;&nbsp;FROM msdb..restorefilegroup rfg<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id<br>&nbsp;&nbsp;&nbsp;WHERE bs.backup_finish_date &lt; (GETDATE() - @DaysToRetain)<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..restorehistory<br>&nbsp;&nbsp;&nbsp;FROM msdb..restorehistory rh<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id<br>&nbsp;&nbsp;&nbsp;WHERE bs.backup_finish_date &lt; (GETDATE() - @DaysToRetain)<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;SELECT media_set_id, backup_finish_date<br>&nbsp;&nbsp;&nbsp;INTO #Temp<br>&nbsp;&nbsp;&nbsp;FROM msdb..backupset bs<br>&nbsp;&nbsp;&nbsp;WHERE backup_finish_date &lt; (GETDATE() - @DaysToRetain)<br>&nbsp;&nbsp;&nbsp;AND NOT EXISTS<br>&nbsp;&nbsp;&nbsp;(SELECT bs2.media_set_id FROM msdb..backupset bs2<br>&nbsp;&nbsp;&nbsp;WHERE bs.media_set_id = bs2.media_set_id AND bs2.backup_finish_date &gt; @dtCutoff)<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..backupfile<br>&nbsp;&nbsp;&nbsp;FROM msdb..backupfile bf<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..backupset bs on bf.backup_set_id = bs.backup_set_id<br>&nbsp;&nbsp;&nbsp;INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br><br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..backupset<br>&nbsp;&nbsp;&nbsp;FROM&nbsp;&nbsp;&nbsp;msdb..backupset bs<br>&nbsp;&nbsp;&nbsp;INNER JOIN #Temp t ON bs.media_set_id = t.media_set_id<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..backupmediafamily<br>&nbsp;&nbsp;&nbsp;FROM msdb..backupmediafamily bmf<br>&nbsp;&nbsp;&nbsp;INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id<br>&nbsp;&nbsp;&nbsp;INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br>&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;DELETE FROM msdb..backupmediaset<br>&nbsp;&nbsp;&nbsp;FROM msdb..backupmediaset bms<br>&nbsp;&nbsp;&nbsp;INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id<br>&nbsp;&nbsp;&nbsp;SELECT&nbsp;&nbsp;&nbsp;@intErrNo = @@ERROR<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0 GOTO AbortSQL<br><br>AbortSQL:<br>&nbsp;&nbsp;&nbsp;IF @intErrNo &lt;&gt; 0<br>&nbsp;&nbsp;&nbsp;BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROLLBACK<br>&nbsp;&nbsp;&nbsp;END<br>&nbsp;&nbsp;&nbsp;ELSE<br>&nbsp;&nbsp;&nbsp;BEGIN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COMMIT TRANSACTION SQBDeleteBackupHistory<br>&nbsp;&nbsp;&nbsp;END<br><br>&nbsp;&nbsp;&nbsp;DROP TABLE #Temp<br><br>ExitSQL:<br><br>&nbsp;&nbsp;&nbsp;SET NOCOUNT OFF<br></td> 
      </tr> 
     </tbody> 
    </table><br><br>Another suggestion to speed up the deletion is to create an index on the <span style="font-family:Helvetica, Arial;color:#0000ff;font-size:10pt;">media_set_id</span> <span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;">column in the <span style="font-family:Helvetica, Arial;color:#7f0000;font-size:10pt;">backupset</span></span> <span style="font-family:Helvetica, Arial;color:#000000;font-size:10pt;">table. However, modifying system tables should always be done with caution.<br></span></td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/sofire/archive/2010/01/11/1644458.html

[http_www.sqlbackuprestore.com_backuprestorehistorytables.htm]: http://www.sqlbackuprestore.com/backuprestorehistorytables.htm
[Virtual Device Interface]: http://www.sqlbackuprestore.com/3rdpartybackupapps_vdi.htm