-1

I restored a db to a different SQL server but interestingly the msdb backupset tables on this server are showing the db backup history from the source server.

Is this expected?

Note: I have not restored msdb.

Steps to reproduce:

  1. On source server perform full backup, followed by couple of log backups.
  2. On destination server, using the database restore UI, perform restore of the full backup and log backups.
  3. Fire below query on destination server and you will see the full and log backup history (from source server) of this db appear here.

Query:

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   CASE msdb..backupset.type 
      WHEN 'D' THEN 'Database' 
      WHEN 'L' THEN 'Log' 
      END AS backup_type, 
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name, 
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM 
   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE 
   (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
   msdb.dbo.backupset.backup_finish_date desc
4
  • The only ways are by restoring msdb or running backups on that database name on that instance, before. If you can repro this, please post a repro. Feb 18 at 17:58
  • I can reproduce this. I have full and log backups generated via ola backup solution. Now on another server when I restore the full and log backups, then check the msdb backupmediafamily and backupset tables I can see the log backup history.
    – variable
    Feb 19 at 6:33
  • 1
    Please post a way to do a minimal reproduction so that others can test. Feb 19 at 11:50
  • Did you get to try my repro steps.
    – variable
    Feb 23 at 17:14

2 Answers 2

2

This is documented in these articles: RESTORE Statements (Transact-SQL) and Backup History and Header Information (SQL Server)

When a restore is performed, the backup history tables are also modified. For information on these tables, see Backup History and Header Information.

When a restore is performed, backup history tables and restore history tables are modified.

This gives no explanation about it but i find it useful as from restorehistory you can extract only restore date, not the backup date, I use it in my log shipping monitoring on secondary server.

1

Restoring a database does not bring over any previous backup history. Restoring will update some of the msdb tables but it would only update the restore and add in the backupset from the backup that was used to create the restore.

Doing the repro provided only shows the single item I've mentioned above. If I backed up a database 100 times and then took the 100th backup and restored it to another instance, I do not see 100 backups in msdb. You're either hitting the wrong instance or have restored msdb as well, even though you've noted that you haven't restore msdb.

2
  • You are correct, I meant the backup history of restores, not all backup history. I think answer for sepupic addresses what I'm seeing.
    – variable
    Feb 23 at 19:27
  • Your description was, "the msdb backupset tables on this server are showing the db backup history from the source server." not "I'm seeing backup history added for the specific restore I'm doing" as these things are wildly different. Feb 23 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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