MSDB retains history of backups purely for informational purposes. This history does not affect the ability to perform restores in any way.
To see backup history, I use a query like this:
DECLARE @DBName SYSNAME;
DECLARE @HistoryDays INT;
SET @HistoryDays = 7; --limit the display to backups taken in the past 7 days.
SET @DBName = DB_NAME(); -- modify these as you desire.
SET @DBName = NULL; -- comment this line if you want to limit the displayed history
--SET @DBName = 'some_db_name';
;WITH src AS
(
SELECT DatabaseName = bs.database_name
, BackupStartDate = bs.backup_start_date
, CompressedBackupSizeMB = bs.compressed_backup_size / 1048576
, ExpirationDate = bs.expiration_date
, BackupSetName = bs.name
, RecoveryModel = bs.recovery_model
, ServerName = bs.server_name
, BackupType = CASE bs.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
ELSE '[unknown]' END
, LogicalDeviceName = bmf.logical_device_name
, PhysicalDeviceName = bmf.physical_device_name
, rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name
ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE (bs.database_name = @DBName
OR @DBName IS NULL)
AND bs.type = 'D'
AND bs.backup_finish_date >= DATEADD(DAY, 1 - @HistoryDays, GETDATE())
)
SELECT src.DatabaseName
, src.BackupStartDate
, CompressedBackupSizeMB = CONVERT(INT, src.CompressedBackupSizeMB)
, src.ExpirationDate
, src.BackupSetName
, src.RecoveryModel
, src.ServerName
, src.BackupType
, src.LogicalDeviceName
, src.PhysicalDeviceName
FROM src
WHERE src.rn = 1
AND src.DatabaseName NOT IN (
'master'
, 'model'
, 'msdb'
, 'tempdb'
)
ORDER BY src.BackupStartDate;
As you can see, I'm ordering the results by msdb.dbo.backup_set.backup_start_date -> the last row will be the most recently taken backup, which might or might not be the one I will restore in a disaster recovery effort. As the old saying goes, "you can never have too many backups". I may very well need to restore a backup from prior to the last backup for any number of reasons, including perhaps database corruption, user error, malicious intent, etc. The concept of "current" backups does not really exist since the backup file may or may not exist at the time of restore. If I take a backup now, to a disk that fails two minutes after the backup completes, what is the "current" backup?
You can use sp_delete_backuphistory to remove backup history from msdb:
DECLARE @oldestDate datetime;
SET @oldestDate = CONVERT(datetime, '2016-12-01T00:00:00');
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @oldestDate;
Modify @oldestDate to whatever time period you like.
