When backing up a Microsoft SQL Server database, even if I specify to backup to a new backup set and “Overwrite all existing backup sets”, or even to a new media set, the msdb backupset/mediaset tables still retain the history of all previous backupsets. My questions are as follow:
- What does it mean to “Overwrite all existing backup sets” if the history is preserved? Does it simply mean that it will delete other backupsets from the media?
- How can I cleanup backup history for the database for all but current mediaset/backupset?
- Related to 2) how do I select only “current” records from backupset/mediaset tables? I don’t see any column indicating this. Does it mean I have to sort by date and somehow decide that this is the “current” one?
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;
@oldestDate to whatever time period you like.