wrong name showing up in the Backup To dialogue

Question

Post reply

wrong name showing up in the Backup To dialogue

cafescott

SSCarpal Tunnel

Points: 4724
More actions
February 19, 2009 at 10:06 am

#383973

hello,
We run SQL 2005. I have been given the task of verifying that the SQL backup devices for our company's server are saving the correct files. I've got a script that I've been trying to use to help assist with this. Here's the SQL that the script uses:
select s1.database_name,
s1.backup_start_date,
s1.backup_finish_date,
s2.physical_device_name
from msdb..backupset s1
inner join msdb..backupmediafamily s2 on
(s1.media_set_id = s2.media_set_id)
where s1.type ='D' and
s1.backup_start_date =
(select max(s3.backup_start_date) from msdb..backupset s3
where s3.database_name = s1.database_name and s3.type ='D')
order by s1.database_name
The problem is that for some of the databases, i'm seeing the wrong Physical Name for them.
The incorrect Physical Name can also be seen when I take these steps:
1. right-click on a database.
2. Choose Tasks/Backup
3. remove the file that's listed under "Backup to"
4. Click Add
5. Choose the correct backup device.
6. Click OK (this selects the backup device).
7. Click OK. (this backs up the database to the backup device.)
After the last OK, I get the message the backup was made successfully. However, if I immediately go back into the Backup To dialogue, by right-clicking, then choosing Tasks/Backup, the wrong device shows up under "Backup to".
I would like to see the correct backup device show up for each database, particularly when I've just made a backup seconds before. 😉
Does anyone have any suggestions about how to fix our server so that the backup device that was last created for each database shows up in the Backup To dialogue? Alternatively, does anyone have a better SQL statement for me to run to verify the backup devices are configured correctly?
thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 1

Hi,

Here's another piece of info. For the database that I'm most concerned with, I have tried these statements:

use [database_name]

go

select * from sys.database_files

the physical names for the database and the log file are both correct.

meanwhile, this the same database that keeps on putting the wrong name in the Backup To dialogue box.

thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I'm a little confused. Perhaps if you could provide a picture of what you are talking about, as I have having a little difficulty seeing in my mind what you are doing.

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 3

Hi Lynn,

the Backup To dialogue I'm referring to is seen here:

(I used the Master database for the image. The Master db is fine.)

For a few databases, I see the wrong backup device name immediately after I make a backup in this dialogue. I also see the wrong Physical Name show up in the SQL command that I gave at the top of this thread.

thanks.