How do I change the logical filename after a restore?

Question

How do I change the logical filename after a restore?

Isabelle2378

SSCertifiable

Points: 6174
More actions
January 23, 2006 at 2:49 pm

#167142

Hi,
I have two databases: DatabaseA and DatabaseB. I needed to refresh databaseB so I took the last backup of DatabaseA and did a restore to DatabaseB, over-writing existing data. I worked great and all the data is correct. But I noticed that the Logical filename of DatabaseB is now DatabaseA_data with the physical filename pointing to c:\data\DatabaseB.mdf. My questions are:
1) How come the logical filename stayed DatabaseA (did I miss something in the restore options?)
2) How can I change the logical filename for DatabaseB to DatabaseB_data?
3) Does this affect anything having two different databases with the same logical filename? What actually references the logical filename vs the physical filename?
Thanks!!!
Isabelle
Thanks!
Bea Isabelle

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

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

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 1

Hi,

I used the alter database <database name> modify file to change the logical filenames, so I already answered my question #2. Still would like to know the answers to my other questions.

Thanks!

Isabelle

Thanks!
Bea Isabelle

Paul Mu SSCertifiable Points: 5547 More actions · Answer 2

If you execute the following command in QA, it will give you information about the number and names of the files for that database abckup:

RESTORE FILELISTONLY FROM <Database Name>

The RESTORE DATABASE statement will only replace the physical files based on the logical file number, or you could use the MOVE ... TO option to map a logical name to a physical file name, e.g.

RESTORE DATABASE db2 FROM DISK = 'D:\SQLBACKUP\db1.bak'

WITH REPLACE,

MOVE 'db2_data' TO 'D:\SQLDATA\db1_Data.mdf',

MOVE 'db2_log' TO 'D:\SQLLOG\db1_Log.ldf'

As you can see, it does not alter the logical file names. You will need to use the ALTER DTABASE statement to do this, which you have already found out!

Having the same logical filenames in more than one database does not affect the databases, since each database have a distinct database id. You may decide to have a generic logical file names for a set of similar databases, and thus no need to change it.

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 3

Thanks Paul for the info. I didn't think it would be a problem since they are two different databases, just maybe to confuse myself later! I just think it's odd that it doesn't change the logical name along with the physical filename as part of the restore.

Isabelle

Thanks!
Bea Isabelle