Previous Backup History

We can identify the Backup History like when was the backup taken, From which DB Server, Backup Type, Backup file size, Recovery Model,...Etc using backupset table on msdb database.

Script: 1

USE master

SELECT Server_Name 'DB Server',
database_name 'Database Name',
backup_finish_date 'Backup Date',
CASE [Type] WHEN 'D' THEN 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial' END 'Backup Type',
(Backup_size/1024.)/1024. 'Backup Size(MB)',
FROM msdb..backupset
ORDER BY backup_finish_date DESC

Can we delete the backup entries from the table backupset ?
Yes. We can delete the backup entries from the table using one system stored procedure sp_delete_backuphistory.
The stored procedure has only one parameter named @oldest_date, Here, I am going to delete the entries that are older than the given date.
Script: 2
USE msdb;

EXEC sp_delete_backuphistory '2010-07-21 21:49:14.000'
The backup history have been deleted from the table that are older than 2010-07-21 21:49:14.000.
Now, I executed the Script: 1 again, to listout the remaining entries.
Microsoft recommend that we have to periodically execute sp_delete_backuphistory to Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date.

No comments:

Post a Comment