sp_delete_backuphistory (Transact-SQL)sp_delete_backuphistory (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

刪除指定日期之前備份組的項目,以縮減備份和還原記錄資料表的大小。Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date. 其他的資料列新增到備份和還原記錄資料表之後每個備份, 或還原作業;因此,我們建議您定期執行sp_delete_backuphistoryAdditional rows are added to the backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically execute sp_delete_backuphistory.


備份和還原記錄資料表位於msdb資料庫。The backup and restore history tables reside in the msdb database.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions


sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'   


[ @oldest_date = ] 'oldest\_date' 會保留在備份和還原記錄資料表的最舊日期。[ @oldest_date = ] 'oldest\_date' Is the oldest date retained in the backup and restore history tables. oldest_datedatetime,沒有預設值。oldest_date is datetime, with no default.

傳回碼值Return Code Values

0 (成功) 或 1 (失敗)0 (success) or 1 (failure)

結果集Result Sets



sp_delete_backuphistory必須從執行msdb資料庫,並會影響下列資料表:sp_delete_backuphistory must be run from the msdb database and affects the following tables:

實體備份檔案會保留下來,即使所有記錄都遭刪除也一樣。The physical backup files are preserved, even if all the history is deleted.


需要的成員資格sysadmin固定的伺服器角色,但權限可以授與給其他使用者。Requires membership in the sysadmin fixed server role, but permissions can be granted to other users.


下列範例會刪除備份和還原記錄資料表中,The following example deletes all entries that are older than January 14, 2010, 12:00 A.M. 在 2010 年 1 月 14 日 12:00 A.M. 之前的所有項目。in the backup and restore history tables.

USE msdb;  
EXEC sp_delete_backuphistory @oldest_date = '01/14/2010';  

另請參閱See Also

sp_delete_database_backuphistory (-SQL)) sp_delete_database_backuphistory (Transact-SQL)
備份記錄與標頭資訊 (SQL Server)Backup History and Header Information (SQL Server)