restore same db w/ diff logical name to new server

Question

restore same db w/ diff logical name to new server

cross

SSCommitted

Points: 1735
More actions
July 3, 2003 at 9:55 am

#105774

I would like to backup DatabaseA and restore it as DatabaseB on a different server.
Through EM I change the logical names in the restore utility but get an error "Logical file DatabaseB is not part of DatabaseB..."
Previously I've tried issuing a TSQL to do the restore but get the same error.
Surely it is possible to do this. What am I doing wrong?

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

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

mybid2003 SSCertifiable Points: 6620 More actions · Answer 1

You could use "RESTORE DATABASE" command with "REPLACE" and "MOVE" options. Usage of which can be found in Books Online.

.

cross SSCommitted Points: 1735 More actions · Answer 2

I looked up the command in Books Online and executed it. I backedup DatabaseA and tried to restore as DatabaseB. In EM, the database shows up as DatabaseB but when I view properties for it, the logical name of the didn't change. The file name is still DatabaseA_data, not DatabaseB_data. Shouldn't the file name change to DatabaseB_data?

This is the command I used from BOL (with different names of course)

RESTORE FILELISTONLY

FROM MyNwind_2

-- Restore the files for MyNwind2_Test.

RESTORE DATABASE MyNwind2_Test

FROM MyNwind_2

WITH RECOVERY,

MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',

MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'

GO

mybid2003 SSCertifiable Points: 6620 More actions · Answer 3

I have had problems doing the same from the EM, since the Drive and machine is different. So I have executed from QA, with REPLACE & MOVE option in RESTORE DATABASE command. e.g. as follows:

 

RESTORE FILELISTONLY FROM DISK='C:\DBA\SERVER1\DB1_db_200304272151.BAK'



RESTORE DATABASE DB2 

FROM DISK='C:\DBA\SERVER1\DB1_db_200304272151.BAK' 

WITH REPLACE,

MOVE 'DB1_Data' 

  TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2_Data.mdf',

MOVE 'LegacyOTG_TEMP_Log' 

  TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\DB2_log.mdf'

.

cross SSCommitted Points: 1735 More actions · Answer 4

I tried your script and it ran great, thanks.

The only thing is....still while the physical file names are correct, the logical file name for the new DB remains the same as the name of the backup.

So the physical filenames and logical filenames are still not matching up.

Am I missing something? Why is that?

Thanks!

skrilla99 SSCertifiable Points: 5795 More actions · Answer 5

Try this:

******************************

ALTER DATABASE DB1

MODIFY FILE (NAME=DB1_Data, NEWNAME=DB2_Data)

******************************

Hope that works,

- Dan