Delete BackUp and Restore History for databases in SQL Server 2005/2008

The msdb database stores details of all backup and restore operations, in tables. If for some reason, you want to delete the backup and restore details of a particular database, use the sp_delete_database_backuphistory stored procedure.

The following query deletes all entries for the 'PictureAlbum' database in the backup-and-restore history tables

USE msdb;

GO

EXEC sp_delete_database_backuphistory 'PictureAlbum';

In order to delete backup and restore details for all the databases older than a specified date, use the sp_delete_backuphistory stored procedure. The query shown below deletes the historical data for all databases from the current datetime.

USE msdb;

GO

DECLARE @dt as datetime

SELECT @dt = GETDATE();

EXEC sp_delete_backuphistory @dt;

If you want to specify an earlier date, do it this way

USE msdb;

GO

EXEC sp_delete_backuphistory '03/10/09';

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: