Hi!

We have to restore a SQL Server 2000 backup taken on one server to another server. The backup taken is in a file.

The Database on both server are of same ID, i.e. T01 hence the datafiles are named t01data1 to t01data3 on both servers.

While trying to restore using Restore -> from Device -> filename, it gave an error

Device Activation Error. The physical file name g:\TO1DATA1\T01DATA1.mdf may be incorrect. File 'ATADATA1' can not be restored to g:\TO1DATA1\T01DATA1.mdf. Use WITH MOVE to identify a valid location of file.

The same message of all 3 datafiles and log file.

When analysed, i found that in target database, the logical file name is TA1data1 And so on, while in source database the logical file name was ATAData1 and so on..

Problem is, i can not change logical file name in either of system.

Now , how to proceed. Please help.

With Regards Amit

-- Anonymous, March 10, 2004

Answers

Amit,

You will need to use Transact SQL to do this, using RESTORE DATABASE with the WITH MOVE option. There is a lucid explanation of this, with examples, under the Books Online topic titled, "How to restore files to a new location (Transact-SQL)".

Hope this helps,

Eric

-- Anonymous, March 18, 2004

Amit,

If you really want to change the LOGICAL FILE NAME (with move does not do this) here is how to do it. Keep in mind you can't do the renaming of a logical file DURING a restore, must be done afterwards.

From query analyzer: use MODIFY file (NAME= '', NEWNAME = '') go

-- Anonymous, April 15, 2004

Sorry something wrong with that post: Goes like this USE MODIFY FILE (NAME= '' , NEWNAME = '') GO

-- Anonymous, April 15, 2004

use

MODIFY file (NAME= 'ATADATA1', NEWNAME = 'ATADATA1')

go

-- Anonymous, April 15, 2004

You have to do each logical file name one at a time, you can't use to modify file statements with only one Go.

use yourdatabasename

ALTER DATABASE yourdatabasename

Modify file (name= 'CurrentlogicalName' , newname = 'Preferredlogical name')

go

use yourdatabasename

ALTER DATABASE yourdatabasename

Modify file (name= 'CurrentlogicalNameLogfile' , newname = 'Preferredlogical name')

go

PS - sorry for all the junk on these posts, I kept trying to enclose things in brackets which just blanks it out.

-- Anonymous, April 15, 2004