Physical_Device_Name Not Updated On Restore

This post has been republished via RSS; it originally appeared at: Premier Field Engineering articles.

The Problem

You restored a database on top of an existing database to refresh your development environment, but the Physical_Device_Name in msdb..backupmediafamily does not get updated.  That seems odd, but let's take a look at the documentation on that table.  We can find the documentation here on Backup Media Family.  Zooming in on the Physical_Device_Name column we see this (Click for a larger image).

Verify

Trust but verify!  Let's try to reproduce this and see if the documentation is correct. Here are the steps we'll take.

• Step 1
  • Put a backup file in C:\Temp, restore it, and then check the physical_device_name column in msdb..backupmediafamily
• Result 1
  • We see C:\Temp\MyBackup.bak
• Step 2
  • Move the backup file to C:\Temp\Test, restore it again, and check the physical_device_name column in msdb..backupmediafamily.
• Result 2
  • We see C:\Temp\MyBackup.bak

Well that's not at all what we expected was it? We expected to see C:\Temp\Test\MyBackup.bak the second time we checked. Well that verifies the documentation. Here is the query we used to verify.

SELECT [rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Solutions

Let's take a look at some options to get that column updated with the most recent and correct information.  We are also going to include options for just finding the correct data in other places.

• Drop the database prior to the restore (This deletes the backup/restore history as well)
• Delete the backup/restore history prior to the restore using sp_delete_backuphistory
• Create an XE session for database_recovery_progress_report events and include the sql_text field.  I would also filter on phase so we don’t waste space capturing all of the restore steps and only see the completion.
• Query the error log

I'm not going to cover the first two options since they are quite simplistic, but let's take a look at how we could use the last two options.

Extended Event

This does not fix what gets stored in the msdb..backupmediafamily table, but is an alternate way to track and store restore information with the accurate information we desire.  We can track this information using the sqlserver.database_recovery_progress_report event in Extended Events.  Here is what that would look like:

CREATE EVENT SESSION [Restore Tracking] ON SERVER
ADD EVENT sqlserver.database_recovery_progress_report(SET collect_database_name=(1)
ACTION(sqlserver.sql_text)
WHERE ([phase]=(4)))
ADD TARGET package0.event_file(SET filename=N'Restore Tracking')
WITH (STARTUP_STATE=ON)
GO
 
ALTER EVENT SESSION [Restore Tracking] ON SERVER
STATE = start;
GO

You can read more about Extended Events for Database Recovery HERE.

Error Log

Even though the Physical_Device_Name does not get updated it is logged correctly in the SQL Server error log.  Here is what that would look like.  Note that parameter 5 and 6 allow for start and finish times if you need to narrow that down, but I left them NULL here to search the whole log.

EXEC master.dbo.xp_readerrorlog 0, 1, N'Database was restored', NULL, NULL, NULL, N'asc'