SQL Restore problem

Question

SQL Restore problem

TRACEY-320982

SSChampion

Points: 13653
More actions
June 14, 2006 at 3:55 pm

#113383

I have one server one database called
DELTEK if i right click properties i have logical file name as M40BARE_Data.mdf
Log = M40BARE_Log.ldf
I have new server i called the database i created new database called
DELTEK.
if i right click properties i have logical file name as
DELTEK.mdf and DELTEK.ldf
When i do restore ....................it comes back as logical name of M40BARE_Data but i change this to DELTEK and i get errrors.
Do i have to make my new database DELTEK and the logical file name M40BARE_Data same as where im restoring from.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 1

When restoring though the GUI it will not allow you to change the logical file name. However, you can change this after the fact by issuing an ALTER DATABASE command. See book online for the exact syntax.

Hope this helps

Jason

Regards,

Jason P. Burnett
Senior DBA

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

I just been playing you cannot change the logical name....

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 3

I am positive that you can. I have done it many times. Below is the content from BOL:

Syntax

ALTER DATABASE database

{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]

| ADD LOG FILE < filespec > [ ,...n ]

| REMOVE FILE logical_file_name

| ADD FILEGROUP filegroup_name

| REMOVE FILEGROUP filegroup_name

| MODIFY FILE < filespec >

| MODIFY NAME = new_dbname

| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }

| SET < optionspec > [ ,...n ] [ WITH < termination > ]

| COLLATE < collation_name >

}

MODIFY FILE

Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.

To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.

Thus:

MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...).

For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

Regards,

Jason P. Burnett
Senior DBA

Jason P. Burnett SSCommitted Points: 1759 More actions · Answer 4

Also, I don't remember for sure but you might have to restart the instance to make the change take affect. You will just have to try and see.

Regards,

Jason P. Burnett
Senior DBA

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

Thanks for replying...you can't do it through EM when restoring...say you have TESTA logical mdf as TESTA and do restore from TESTB which gives TESTB logical name on EM you cannot change to TESTA you use TESTB then do the run alter command in query analyzer...

Any reason why people use different logical names......