2

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:

  1. 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?
  2. How can I cleanup backup history for the database for all but current mediaset/backupset?
  3. 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?
  • Probably in Media Options from Backup Wizard...? – Kevin3NF Jan 5 '17 at 21:22
  • Yes, I wasn't too sure of the spelling since I didn't have SSMS at hand. I'll edit the original question. Indeed I was talking about "Overwrite all existing backup sets". – mrQQ Jan 6 '17 at 9:54
1

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.

  • Thank you for detailed answer and an example. You've mentioned that backup history is only for historical purposes. However, is it being used when I use SSMS to Restore database to a point in time? My thought was that it used those tables to find the last full and later log backups to allow that. – mrQQ Jan 6 '17 at 9:57
  • It uses that as a hint about where the backup files might be located. You can however browse to select any file to restore. – Max Vernon Jan 6 '17 at 11:43
  • Hmm. This post seems to imply that these tables are not only for historical reasons: sqlskills.com/blogs/paul/… How else would SQL know what is the correct backup chain? – mrQQ Jan 8 '17 at 18:28
  • If the tables were required, how could you do disaster recovery onto a newly created instance? They are not required at all. The LSN chain defines what can be applied to a database in recovery mode. – Max Vernon Jan 8 '17 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.