Database has wrong logical names after restore

SQL Server Administration
1

Not had this happen before, and am at a loss to explain why.

I made a Full backup of a DEV database, preformed some rollout tests, and then wanted to restore the backup

I restored the backup file, saw that the Logical Names were a bit odd (truncated compared to what we normally use) so I tried to ALTER them:

ALTER DATABASE MyDB_DEV MODIFY FILE (NAME = 'MyDB_DEV_dat', NEWNAME = 'MyDB_DEV_data')
ALTER DATABASE MyDB_DEV MODIFY FILE (NAME = 'MyDB_DEV_lo', NEWNAME = 'MyDB_DEV_log')

and got an error that those logical names didn't exist. I checked the names and they were the correct names after all - how can that be?

So I ran the RESTORE again, more carefully! and checking the names as I went.

RESTORE FILELISTONLY FROM DISK =
    'F:\MSSQL\MyDB_BACKUP\MyDB_DEV_20160409_161254_Full.BAK'

gave me:

LogicalName  PhysicalName               Type FileGroupName Size        MaxSize        FileId CreateLSN DropLSN UniqueId                             ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN   DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint
------------ -------------------------- ---- ------------- ----------- -------------- ------ --------- ------- ------------------------------------ ----------- ------------ ----------------- --------------- ----------- ------------ --------------------- ------------------------------------ ---------- --------- ------------- 
MyDB_DEV_dat G:\MSSQL\DATA\MyDB_DEV.mdf D    PRIMARY       13290700800 35184372080640 1      0         0       00000000-0000-0000-0000-000000000000 0           0            9732620288        512             1           NULL         174823000002546200067 BA7784F1-41FA-48E6-9721-8B77C72C3220 0          1         NULL
MyDB_DEV_lo  H:\MSSQL\Log\MyDB_DEV.ldf  L    NULL          10223878144 35184372080640 2      0         0       00000000-0000-0000-0000-000000000000 0           0            0                 512             0           NULL         0                     00000000-0000-0000-0000-000000000000 0          1         NULL

these are the "abbreviated" logical names that I was trying to ALTER. So far so good.

I restored the database:

RESTORE DATABASE MyDB_DEV
	FROM DISK = 'F:\MSSQL\MyDB_BACKUP\MyDB_DEV_20160409_161254_Full.BAK'
	WITH
		REPLACE,
		NORECOVERY,
		STATS = 10,
	MOVE 'MyDB_DEV_dat' TO 'G:\MSSQL\DATA\MyDB_DEV.mdf', 
	MOVE 'MyDB_DEV_lo' TO 'H:\MSSQL\Log\MyDB_DEV.ldf'

then I checked the logical names:

SELECT
	name AS [Logical_name],
	physical_name AS [File_Path],
	type_desc AS [File_Type],
	state_desc AS [State]
FROM	sys.master_files
WHERE	database_id = DB_ID(N'MyDB_DEV')

which gave me this:

Logical_name File_Path                  File_Type State
------------ -------------------------- --------- ---------
MyDB_DEV_dat G:\MSSQL\DATA\MyDB_DEV.mdf ROWS      RESTORING
MyDB_DEV_lo  H:\MSSQL\Log\MyDB_DEV.ldf  LOG       RESTORING

which is what I was expecting. Then I set RECOVERY on the the database

RESTORE DATABASE MyDB_DEV WITH RECOVERY

and re-ran the Logical Files query and I got this:

Logical_name  File_Path                  File_Type State
------------- -------------------------- --------- ------ 
MyDB_DEV_data G:\MSSQL\DATA\MyDB_DEV.mdf ROWS      ONLINE
MyDB_DEV_log  H:\MSSQL\Log\MyDB_DEV.ldf  LOG       ONLINE

I was expecting to have to use ALTER to make those changes.

How did that happen?

I made the backup, from the same database, just an hour or so ago. I did not used any ALTER commands thereafter, other than when I made the RESTORE.

EDIT: Ignore the following. I now realise that the first backup was made automatically by scheduled task - after I created the database and before I restored content into it

I have got one other FULL backup, shortly after I established the database and that has the logical names:

LogicalName
-----------
MyDB_DEV
MyDB_DEV_log

and they look wrong to me too. I created the DB in SSMS, and it probably gave it those names, but then I realised I needed to Restore the database from a Full Backup of Production (which made the previous Database Creation unnecessary). I would have expected that Restore operation to have changed the Logical Names (from those used by the Production Database) to MyDB_DEV_data and MyDB_DEV_log (because I use a script to do Restored and that has a fixed method of determining the logical names, and that same script was used to generate the Restore command this time, giving me those logical names.

The logical names ARE what I want them to be, but its a mystery to me how they have changed from what is stored in the Backup file - without me explicitly having used an ALTER command

3

Thanks but I'm not understanding whereabouts I need to put the complete path?

(My response to a post that has now been removed as the poster was a spammer)

5

Thanks. However my problem is that the names in the Backup File (as shown by RESTORE FILELISTONLY) are not the ones that the RESTORE DATABASE command restores them too, and I'm curious to find out how that can be the case as I've never seen it happen before.

(My response to a post that has now been removed as the poster was a spammer)

6

Are you restoring over an existing database? If so - what are the logical names for that database?

7

Yes. The backup was made from the same database. I made the backup, did some data manipulations, decided they were wrong and wanted to restore and start again.

As part of doing my restore I checked the logical names in the Backup File, and at that point I was surprised that they were not the same as our normal naming conventions, so I decided to change them after restoring the file. That's when I discovered that the RESTORE had not preserved the logical names from the BACKUP file - I've never seen that before, and my understanding was that a Restore would always set the logical names the same as the backup file (until ALTER is used to change them to something else).

Maybe I've misunderstood that?

8

I was thinking that someone modified the logical names on the database being overwritten - and when you first looked you were seeing the values from the original database. When you performed the recover the logical names would then be changed to what exists in the backup.

9

[quote="jeffw8713, post:8, topic:5903, full:true"]
I was thinking that someone modified the logical names on the database being overwritten[/quote]

There is only me :slight_smile: - mind you, I might have done something ... but I don't remember doing so.

I did create the DB in SSMS (which gave me default logical names) THEN restored a Full backup from another database (and changed the logical names as part of that restore - I don't know what to, but I would have used our standard code for that, and that would have used our "normal naming convention" - but ... I no longer have an record of what I did that that time, so it might be that I didn't use our "normal names"), so that restore overwrote the database that I had created using SSMS (in fact I kicked myself as to why I bothered with the create DB in SSMS as I could have just done the RESTORE instead ...)

At that point I took a backup (which is the one I restored from later)

The only logical names I would have queried are the ones IN the Backup File.

Yup that's what I expected to happen - but it doesn't appear to be what actually happened.

RESTORE FILELISTONLY on the backup file showed logical names MyDB_DEV_dat / MyDB_DEV_lo

I did a Restore with NORECOVERY and then the logical names where MyDB_DEV_dat / MyDB_DEV_lo (as expected).

I did a RESTORE RECOVERY so that I could then do an ALTER to change the logical names and at THAT point (i.e. BEFORE I attempted an ALTER) the logical names had already changed to MyDB_DEV_data / MyDB_DEV_log

My understanding is that at that point that should have been the same as the ones in the Backup file (i.e. MyDB_DEV_dat / MyDB_DEV_lo)

Hopefully that clear as mud! but if not do please ask for any explanation / tests that you need. I've probably still got the backup file, so I could run it again for any test etc.