January 21, 2009 at 9:19 am
On my SQL 2000 instance all of the file names from the last full backup are corrupt when viewed using the Restore Database dialog. I have verified that the file names look fine on the disk drive. So it appears that the system tables were populated with invalid file names
On the Restore database the Restore From column shows {26815961-f1f4-4ad9-94e0-c23f10bd2d6}5 where it should show a valid file name.
How do you fix a corrupted file name, and does anyone know where this value is stored in the sytem tables?
January 21, 2009 at 9:52 am
Use this code to find out what SQL thinks is the lastest backup file name and date of last full backup. (Use your databasename rather than 'databasename' in the code:
SELECT physical_device_name backup_start_date
FROM msdb.dbo.backupset a JOIN msdb.dbo.backupmediaset b ON a.media_set_id = b.media_set_id
JOIN msdb.dbo.backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type='D' AND a.database_name= 'databasename' AND backup_start_date =
(SELECT TOP 1 backup_start_date FROM msdb.dbo.backupset bs2
WHERE bs2.database_name = a.database_name AND type = 'D'
ORDER BY backup_start_date DESC)
After running this I would do another full backup and then run this code again to see if SQL has got the name correct. If not were there any messages in the SQL log or the NT log?
Francis
January 23, 2009 at 9:34 am
Thanks Francis you save me tons of time,
SQL logs show "Database backed up: ......" and it looks fine.
select Physical_Device_name
from msdb.dbo.backupmediafamily
where Physical_Device_name like '{%'
Returns about 1400 rows of stuff. While a NOT like version shows 17K rows of valid file names.
It looks like the Device_Type column = 7 on the bad rows and its device_type =2 on the good rows.
Thanks for the help. I will check the maint job
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply