verifying backup devices

[MDCrab]

Hello,

I'm the SQL DBA and one of my responsibilities is verifying the SQL backup devices (that I have configured). We're running SQL 2005.

One method I've been using is to run a query similiar to this in the Management Studio's query analyzer:

restore filelistonly from
disk='\\our-server\our_folder\backup_device.bak'

The result display for some of the backup devices shows the wrong PhysicalName. The name is actually a different database than what I expect.

However, if I inspect the backup device, I see a different result. I right-click on the backup device and choose 'properties'. I move the cursor to 'media contents'. I look in the database tab, and the database is correct.

Does anyone know why the PhysicalName for a backup device is not pointing to the correct database (after running the 'restore filelistonly' command)? Also, does anyone have any advice to offer about verifying backup devices?

thanks,

 

[mrdenny]

You are probably looking at the wrong file. Why not simply to a restore filelistonly against the backup device. This will query the correct file and return the correct information.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDCrab]

Hi Denny,

thanks for the suggestion. I did a filelistonly on the backup device and the PhysicalName is again, wrong. However, when I view the "database" name for the device it shows the correct database.

I deleted the backup device and created it again. Then I ran the job that writes to it. The result is the same. The PhysicalName is still wrong, but the database name is correct.

thanks.

 

[mrdenny]

What happens when you backup directly to a database file and not use the backup device?

What is it putting for the PhysicalName? What value are you expecting?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDCrab]

Hi Denny,

I just made a backup of one of our key databases. Then I did a restore filelistonly on the backup I created. This backup also has the wrong PhysicalName.

I expect that the PhysicalName will be the name of the database file. So, if I had a database named:

c:\program files\Microsoft sql server\mssql.1\mssql\Customers.mdf

and, after making a backup, the PhysicalName should be the full path to the Customers.mdf.

However, I'm seeing another database as the PhysicalName; i.e.,:

c:\program files\Microsoft sql server\mssql.1\mssql\Customers_Test.mdf

I think it is kind of strange that the PhysicalNames for our databases are often wrong. However, there are no signs of trouble yet.

thanks.

 

[mrdenny]

Query the sys.database_files (or dbo.sysfiles if SQL 2000) and see what the logical names for the files are. Are these names the ones that are showing up in the backup files?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDCrab]

Denny,

Yes, the Physical_Name that shows up when I query the sys.database_files is the incorrect file name that keeps on showing up when I do "restore filelistonly" on the backups.

thanks.

 

[mrdenny]

That's why it's the name in the backups.

You can use the ALTER DATABASE statement to change the name of the logical file name.

ALTER DATABASE YourDatabase
   MODIFY FILE (name='OldName', newname='NewName')

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDCrab]

Hi Denny,

I'm afraid to use the the 'alter database modify file' command on our production database. The database has the correct name in SQL Enterprise Manager. The MDF and LDF files are also appropriately named. It just doesn't have the right Physical_Name when backups are made.

If that's the only remedy, then I guess I will have to just accept the fact that the Physical_names are wrong.

thanks,

 

[mrdenny]

Changing the logical name of the physical files doesn't effect the database name or the physical file names.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[MDCrab]

Hi Denny,

I appreciate the reply. I've been slammed recently so I haven't had a chance yet to try your remedy.

thanks.