January 20, 2011 at 9:55 am
I have a weird issue.....i have a maintenance plan that takes full backup daily......when i query the BackupSet table of msdb db to get the name of the last generated backup it does not match with the backup file generated by the maintenance plan.....
e.g
Backup Name in BackupSet table = MyDB_backup_2011_01_19_210017_2413272
Actual File Name = MyDB_backup_2011_01_19_210017_2569523.bak
Here is the query i am using to get the last generated backup...
SELECT name
FROM msdb.dbo.Backupset
WHERE Database_Name = 'MyDB'
AND Type = 'D'
ORDER BY Backup_finish_Date DESC
Any idea what's going on?
February 14, 2011 at 2:14 pm
microsoft changes to GMT time
February 14, 2011 at 10:30 pm
which version of OS and sql server are you using?
this sounds something fishy to me, it shouldnt happen. i think SQL Server uses SYSDATETIME () in background when the backup file name is chosen.
i am getting same file names as of what they are in backupset and i have checked 4 servers. i can only checked 2 days backup files as we dont keep any backup file older than this
February 15, 2011 at 11:06 am
I am using SQL Server 2008 R2 on Windows Server 2003 R2 machine.
I figured out an alternate which is giving me the actual file name. Instead of using Backupset table now i am using backupmediafamily.
SELECT TOP 1 bsf.physical_device_name
FROM [backupset] bs
INNER JOIN backupmediafamily bsf on bs.media_set_id = bsf.media_set_id
WHERE Database_Name = 'CMan'
AND Name LIKE 'CMan_Backup_%'
AND Type = 'D'
ORDER BY Backup_finish_Date DESC
This query is giving the actual physical file name so for now i am all set, but still don't understand why the physical and logical names are different.
February 15, 2011 at 11:10 am
i think SQL Server uses SYSDATETIME () in background when the backup file name is chosen.
i ran select GETDATE(), SYSDATETIME() on the server and it gave me same values for both.
February 15, 2011 at 4:14 pm
Lazy DBA (2/15/2011)
i think SQL Server uses SYSDATETIME () in background when the backup file name is chosen.
i ran select GETDATE(), SYSDATETIME() on the server and it gave me same values for both.
try running it in results to text mode rather than grid view. i get difference of microseconds here.
February 15, 2011 at 4:15 pm
Lazy DBA (2/15/2011)
This query is giving the actual physical file name so for now i am all set, but still don't understand why the physical and logical names are different.
thats something microsoft has to answer 😉
October 20, 2011 at 10:07 am
I have had the same problem but I don't think it started until SQL 2005. Before that the .bak files didn't have milliseconds as part of the file name. I was guessing that there might be some slight time difference between when the OS timestamped the .bak file and when the entry was made in the msdb.backupset table causing the two to be stamped differently. What do you think - any merit to that idea?
October 20, 2011 at 11:17 am
My question (not being rude, just practical) is who cares? We know that logical and physical names can be different and that they are linked. So, if this is just to understand the process I would likely say it is exactly what has been said before in that the logical name is created at a slightly different time than the physical name. Nevertheless... what is the importance of this?
Jared
Jared
CE - Microsoft
October 20, 2011 at 11:21 am
It's not a problem as long as you know how to link them. I didn't know that until I read this thread. That solved the problem for me - much appreciated!
October 20, 2011 at 11:25 am
Not being completely familiar with these tables... Why are all of the values in both tables for 'name' field NULL?
Jared
Jared
CE - Microsoft
October 20, 2011 at 11:35 am
I should have been clearer in that last response. It's important to me because I query msdb on the source server via a linked server on the target server in order to restore a reporting database on the target server. The query worked for me when I was only looking at the msdb.backupset table as long as there wasn't a mismatch between the name in backupset and the .bak file name in the OS. Occassionally, when there was a mismatch then my restore script would fail with the message that the file name could not be found. Now that I know how to link the logical name in msdb.backupset with the device name in backupmediafamily my query never returns a .bak file name that doesn't exist in the OS and my reporting database restores don't fail for that particular reason anymore. That's why I care.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply