Can't Restore Database to New Server

Hi

I am attempting to transfer a database from my server to my customers
server. I took the backup file on CD however when I try to do a restore with
it I get the following error

Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error:The physical file name
 'd:\sqldata\mssql\da
ta\sa.mdf' may be incorrect.
File 'SA-V1_dat' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa.mdf'. Use
WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
 'd:\sqlData\MSSQL\da
ta\sa_log.ldf' may be incorrect.
File 'SA-V1_log' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa_log.ldf'.
Use WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.

Suggestions how to overcome this??

Regards
Meir

Do what the error message say. Read about the RESTORE command in Books Onlin
e and use the MOVE
option to specify desired physical files names for your database files.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/


"mrrcomp" <mrrcomp@discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5- 77E593B6C75D@microso
ft.com...
> Hi
>
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore wi
th
> it I get the following error
>
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
>  'd:\sqldata\mssql\da
ta\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
>  'd:\sqlData\MSSQL\da
ta\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
>
> Suggestions how to overcome this??
>
> Regards
> Meir
>

As error says to you, use MOVE TO option of the RESTORE command to move
physical files to a new location. If you use EM, check the Options tab when
restoring, there you can specify a new location.

--
Dejan Sarka, SQL Server MVP
Mentor
www. SolidQualityLearning
.com

"mrrcomp" <mrrcomp@discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5- 77E593B6C75D@microso
ft.com...
> Hi
>
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
>
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
>  'd:\sqldata\mssql\da
ta\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
>  'd:\sqlData\MSSQL\da
ta\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
>
> Suggestions how to overcome this??
>
> Regards
> Meir
>

Hi,

To add on:


Try the below steps:-

From Query Analyzer:-

1. Using Restore filelistonly command identify the logical file names of the
database backup file


RESTORE FILELISTONLY from disk='c:\x.bak'


2. With the output of the above query use RESTORE database


RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move  'logical_mdf_filenam
e' to 'new physical name with path',
move  'logical_ldf_filenam
e' to 'new physical log name with
Path'

After the restore refer  sp_change_users_logi
n to syncronize the Logins.


Thanks
Hari
SQL Server MVP


"mrrcomp" <mrrcomp@discussions.microsoft.com> wrote in message
news:41F751D3-42E2-4292-B0F5- 77E593B6C75D@microso
ft.com...
> Hi
>
> I am attempting to transfer a database from my server to my customers
> server. I took the backup file on CD however when I try to do a restore
> with
> it I get the following error
>
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error:The physical file name
>  'd:\sqldata\mssql\da
ta\sa.mdf' may be incorrect.
> File 'SA-V1_dat' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa.mdf'. Use
> WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
>  'd:\sqlData\MSSQL\da
ta\sa_log.ldf' may be incorrect.
> File 'SA-V1_log' cannot be restored to  'd:\sqlData\MSSQL\da
ta\sa_log.ldf'.
> Use WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
>
> Suggestions how to overcome this??
>
> Regards
> Meir
>