SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.
USE msdb
GO
DECLARE @DaysToKeepHistory DATETIME
SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 101)
EXEC sp_delete_backuphistory @DaysToKeepHistory
GO
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Also, be aware that if you don’t purge history regularly, you can end up with a really large MSDB – even just several gigs can give you performance problems when deleting history. I’ve run into servers that had to take an outage just to delete the history because it’d grown so large.
hii pinal
is it possible to restore or get back deleted backup history…………
regards
Hi.
I only wanted to mention if you’re using a German MS-SQL Server you got to replace the parameter 101 with 104 to get the correct input-Format for the sp_delete_backuphistory procedure.
i.e. SET @DaysToKeepHistory = CONVERT(VARCHAR(10), DATEADD(dd, -30, GETDATE()), 104)
regards.
How we can check the details of back history using MSDB.