How can a database have a logical name that is different than displayed in SSMS?

Question

I found that on a production server there is a database named MYOBInterim. On other environments (development, testing), there is, instead, a database named MYOBTraining. (Don't ask me why - I'm still trying to get that answer..)

I created a backup of one of the MYOBTraining databases from a dev instance and then tried restoring it to another dev instance that had no such database.

My SQL is roughly below.

use master;
go


If(db_id(N'MYOBTraining') IS NULL) 
   create database MYOBTraining
   on
   (name = MYOBTraining,
    filename = 'D:\Database\Dev4\MYOBTraining.mdf' )
   log on
   (name = MYOBTraining_log,
    filename = 'E:\Logs\Dev4\MYOBTraining_1.ldf' )
   ;
go

-- BIG NOTE:
-- Note we are moving 'MYOBInterim' (and not 'MYOBTraining')! 
-- Prod calls this DB MYOBInterim. Even though we obtain the backup for use with this restore from DEV - where the DB is called MYOBTraining,
-- It seems the logical name within SQL Server is still MYOBInterim despite the DB appearing in the list with the name "MYOBTraining".

RESTORE DATABASE MYOBTraining FROM  DISK = 'E:\_DBA\BackupFiles\MYOBTraining.bak' WITH  FILE = 1
,  MOVE 'MYOBInterim' TO 'D:\Database\Dev4\MYOBTraining.mdf'
,  MOVE 'MYOBInterim_log' TO 'E:\Logs\Dev4\MYOBTraining_1.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 1

What happened is that initially the MOVE clauses said MOVE 'MYOBTraining' etc, but this returned an error mentioning "RESTORE failing with OS error 32, file being used by another process". Although MS has an article saying this is a known issue (on striped backup sets containing filestream data) which is fixed in a patch, an article on stack exchange suggested double-checking that you are using the correct logical name. When I read the error message again more closely, it was saying that MYOBInterim could not be restored. Clearly it was getting this name from within the backed up database file because it did not exist in the SQL I was using for the restore, so my question is this - how come it was referring to the database as MYOBInterim when it appeared in the Dev server from where I took the backup, in SSMS, as MYOBTraining?

For what it's worth, the SQL I've given above (ie. after changing the logical name to MYOBInterim, as shown) did work and restored the database.

Detailed steps

Initially I didn't give full steps and error messages because I'd already resolved the issue and didn't have the exact errors available anymore. However per discussion, these are needed, so I am re-creating the issue per below.

• Dropped the MYOBTraining database on Dev4 - takes Dev4 back to the starting point > Refresh view on Databases folder in SSMS - and the database is gone.

• In SSMS, go to Dev > Databases > right-click on MYOBTraining > Tasks > Back Up...

• General tab > Source shows "MYOBTraining", Recovery model shows FULL, Backup type shows Full, Backup component has Database selected, Destination / Back up to shows Disk, destination locations shows NUL > select NUL > Remove button > Add button > Filename = E:\Backup\MYOBTraining_20201203_Full.bak > OK button

• Media Options and Backup Options tabs - no changes from default

• Hit OK button > backup executes > dialogue appears saying successful backup > Hit OK button and all dialogues close

• Remote Desktop to that machine > backup file is found exactly where specified earlier (on E drive, etc)

• Open a query window in SSMS and connect to Dev4 > Execute the below SQL

  use master; go

  If(db_id(N'MYOBTraining') IS NULL) create database MYOBTraining on (name = MYOBTraining, filename = 'D:\Database\Dev4\MYOBTraining.mdf' ) log on (name = MYOBTraining_log, filename = 'E:\Logs\Dev4\MYOBTraining_1.ldf' ) ; go

• Run the below SQL:

  RESTORE DATABASE MYOBTraining FROM DISK = 'E:\Backup\MYOBTraining_20201203_Full.bak' WITH FILE = 1 , MOVE 'MYOBTraining' TO 'D:\Database\Dev4\MYOBTraining.mdf' , MOVE 'MYOBTraining_log' TO 'E:\Logs\Dev4\MYOBTraining_1.ldf' , NOUNLOAD, REPLACE, STATS = 1

• Following errors shown:

  Msg 3634, Level 16, State 1, Line 17 The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\Database\MYOBTraining.mdf'. Msg 3156, Level 16, State 8, Line 17 File 'MYOBInterim' cannot be restored to 'D:\Database\MYOBTraining.mdf'. Use WITH MOVE to identify a valid location for the file. Msg 3634, Level 16, State 1, Line 17 The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\Logs\MYOBTraining_1.ldf'. Msg 3156, Level 16, State 8, Line 17 File 'MYOBInterim_log' cannot be restored to 'E:\Logs\MYOBTraining_1.ldf'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 17 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 17 RESTORE DATABASE is terminating abnormally.

• My key point: Note the presence of "MYOBInterim" in the error messages -

  • This was not the database name in SSMS on Dev (in the databases folder) which I right-clicked in order to take a backup
  • This was not in the back up dialogue box which showed the database selected for backup as MYOBTraining
  • But if I modify that last (restore) SQL statement and replace "MOVE MYOBTraining" with "MOVE MYOBInterim" (and similar for the log file), the restore statement works (and shows me a database named MYOBTraining in Dev4).

Additional info using suggestions in answers

Following John's suggestion, before successfully restoring the database, I just now ran this:

RESTORE FILELISTONLY FROM DISK = 'E:\Backup\MYOBTraining_20201203_Full.bak'

... and indeed it shows the LogicalName values as 'MYOBInterim' and 'MYOBInterim_Log' - so my question is exactly this: why is it showing as 'MYOBTraining' in SSMS?

The PhysicalName values, however, do not match the expected values in the answer. Rather, they are:

• D:\Database\MYOBTraining.mdf
• E:\Logs\MYOBTraining_1.ldf

Next, running the second suggested query below:

RESTORE HEADERONLY FROM DISK = 'E:\Backup\MYOBTraining_20201203_Full.bak'

DatabaseName shows 'MYOBTraining'

... so I guess that answers my question: SSMS is showing the database name, but the restore statement requires the LogicalName.

In that case could I revise my question to this: why? What is the difference between DatabaseName and LogicalName? What is the use of LogicalName if I can't even see it in SSMS?

A quick online search shows me that three names exist for a database: database name (in SSMS), logical name (referenced internally by SQL Server?) and physical name (file name), and shows me how to check all three, but doesn't really explain why MS has done this.

Probable answer

Ok, leave me stewing long enough and I think you've pointed me in the direction of the answer:

• A single database consists of multiple files.
• Each file has a physical file name but may be referenced by a simpler logical name
• The restore statement is referencing the files - and in my case, those files have logical names consisting of 'MYOBInterim'
• ... even though those files together constitute the database which has the name 'MYOBTraining' - which is shown in SSMS

Sound reasonable?

Answer 1

When you have a backup file it always helps if you can run the following command first:

RESTORE FILELISTONLY FROM DISK = 'E:\_DBA\BackupFiles\MYOBTraining.bak'

This returns the information of the files that are contained in the backup file. I'm guessing that the result for your .bak file would be similar to this:

 +-----------------+----------------------------------+-----+
 |   LogicalName   |           PhysicalName           | ... |
 +-----------------+----------------------------------+-----+
 | MYOBInterim     | D:\Database\Prod\MYOBInterim.mdf | ... |
 | MYOBInterim_log | E:\Logs\Prod\MYOBInterim_1.ldf   | ... |
 +-----------------+----------------------------------+-----+

Now if you look at the Header information of the backup file with the command:

RESTORE HEADERONLY FROM DISK = 'E:\_DBA\BackupFiles\MYOBTraining.bak'

...you'll probably have somehting like this:

+-----+--------------+-----+
| ... | DatabaseName | ... |
+-----+--------------+-----+
| ... | MYOBInterim  | ... |
+-----+--------------+-----+

So your database MYOBInterim consists of two logical files MYOBInterim and MYOBInterim_log, which have the physical files D:\Database\Prod\MYOBInterim.mdf and E:\Logs\Prod\MYOBInterim_1.ldf (or whatever you actually have).

When you restore a database from one location to another and the database name and the physical names of the database files are different, then you have to remap the old logical names to the new physical location, while providing the new database name.

This is what you have done in your script.

Answering Your Question

The error message you are observing is because the restore failed initially when you provided the new logical names and the new physical location, and because the *.bak file contains the name of the database you are restoring it displays that name.

I reproduced by restoring any of my database to a new database and providing the new logical names and the new physical locations:

RESTORE DATABASE Q280721  FROM DISK = 'C:\SQL\BACKUP\StackExchange\FULL\StackExchange_FULL_20201128_223110.bak' 
WITH FILE = 1, REPLACE, 
MOVE 'Q280721' TO 'C:\SQL\SQL_DATA\Q280721.mdf',
MOVE 'Q280721_log' TO 'C:\SQL\SQL_LOGS\Q280721_log.ldf'

 Msg 3156, Level 16, State 4, Line 1
 File 'StackExchange' cannot be restored to 'C:\SQL\SQL_DATA\StackExchange.mdf'. Use 
 WITH MOVE to identify a valid location for the file.

Update after Question was Modified

Your observation is incorrect. Let's have a look at what you are stating:

My key point: Note the presence of "MYOBInterim" in the error messages -

The exact messages is (with emphasis):

Msg 3156, Level 16, State 8, Line 17 File 'MYOBInterim' cannot be restored to 'D:\Database\MYOBTraining.mdf'. Use WITH MOVE to identify a valid location for the file.

The error message is due to the logical file name and not the database. Because you provided a MOVE in your initial RESTORE DATABASE... command it was trying to move the logical file MYOBInterim to a new location. The logical file name MYOBInterim doesn't exist in your backup file. Hence the error message.

This was not the database name in SSMS on Dev (in the databases folder) which I right-clicked in order to take a backup

See previous explanation above.

This was not in the back up dialogue box which showed the database selected for backup as MYOBTraining

See previous explanation above.

But if I modify that last (restore) SQL statement and replace "MOVE MYOBTraining" with "MOVE MYOBInterim" (and similar for the log file), the restore statement works (and shows me a database named MYOBTraining in Dev4).

This is what I explained in my initial answer and which I had already said you had done correctly:

When you restore a database from one location to another and the database name and the physical names of the database files are different, then you have to remap the old logical names to the new physical location, while providing the new database name.

This is what you have done in your script.

I hope this answers all your questions.