physical_device_name not captured correctly in msdb.dbo.backupmediafamily - by normajeanb

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 306604 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 10/24/2007 4:04:53 PM
Access Restriction Public


I'm writing scripts to pull the most recent backupset for automating a restore (should I ever need to run a restore, errr I guess I should say 'WHEN' I need to run it!)
The problem is that the physical_device_name returned from the system table does not return the correct file name from disk. 
Here is the name of the filename in backup folder:
Here is the physical_device_name returned from the query:
Here is the query that I ran to get the physical_device_name:
SELECT mf.physical_device_name 
FROM msdb.dbo.backupset b 
join msdb.dbo.backupmediafamily mf 
on b.media_set_id = mf.media_set_id 
Where b.database_name = 'CypressReports'
AND b.backup_set_id = @backup_set_id_start (already set to value of most recent bk)

The backup is getting created via a maintenance job (SSMS generated).

The final result of my complete query is supposed to generate Restore statements that I can just execute. Here's what got generated (see below). I can see that it correctly found all the transaction logs that I do every 15 mins (keeps the log files small for replication agents), but I'm really wondering about that full backup name in the first command????
What is this name? How did it get created? Why is this name saved in the system records and not the actual name of the backup file? 

Thanks! Normajean
BackupSetID Command
7229 RESTORE DATABASE CypressReports FROM DISK = 'BE_SQLAgent-CypressReports__35bbfdc7_add4_48c5_bcdf_f41120e8f02a_' WITH NORECOVERY
7235 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240850.trn' WITH NORECOVERY
7240 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240920.trn' WITH NORECOVERY
7245 RESTORE LOG CypressReports FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CypressReports_backup_200710240950.trn' WITH NORECOVERY.....etc. etc. etc.
Sign in to post a comment.
Posted by Microsoft on 3/13/2009 at 2:50 PM
We have fixed this issue in the current SQL 2008 release.
You have a workaround for Yukon as well.
Posted by Covi on 9/10/2008 at 10:06 AM
Today discovered when backup DB to exisiting file (overwrite) the physical_device_name is not updated. If I delete the file first and backup the physical_device_name is updated.

Work around delete the file prior to backup. This updates MSDB.

Would like to have MSDB updated when I overwrite an existing device. I have many backup devices that have to be reported on.
Posted by SQL_Guru on 3/26/2008 at 10:29 AM
Ok, I did a scan of all my SQL servers at work and out of 75 servers and 7,293,671 backups, there were 463 entries in msdb that I could verify were incorrect for the physical device name. This is a 0.00635% chance of it happening or about 1 out of 15,753 backups. It didn't seem to matter if it was SQL 2000 or 2005, the chance was about the same.

Here's the script I used to get a count of the incorrect entries.

select *, convert(float, WrongPhysicalName) / convert(float, TotalBackups) * 100.0 as 'PercentChance'
    select rtrim(@@servername) as 'Server',
        convert(varchar(40), serverproperty('ProductVersion')) as SQLVersion,
        sum(case when bmf.physical_device_name not like '%' + bs.database_name + '%' then 1 else 0 end) as 'WrongPhysicalName',
        count(*) as 'TotalBackups'
        msdb.dbo.backupset bs with (nolock)
        join msdb.dbo.backupmediafamily bmf with (nolock)
        on bs.media_set_id = bmf.media_set_id
             and bmf.physical_device_name like '%\%'
) b
Posted by Microsoft on 12/13/2007 at 3:33 PM
Thanks for your report.
We are currently attempting to reproduce your problem with the backup filename. Is it possible that a backup device is defined, and that the physical filename is referring to that?