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).
Trust but verify! Let's try to reproduce this and see if the documentation is correct. Here are the steps we'll take.
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
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.
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.
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.
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'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.