Sql-server – How to a database have a logical name that is different than displayed in SSMS

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?