Problem having to restore tlogs WITH MOVE

I have a database i am m oving to another server, during the process I am
moving the data and log files to another drive.(Which I have done countless
times before with no problems)
The problem I am having is after I restore the database using the following
statement :
RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
WITH STANDBY = 'D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Data.mdf'
,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Databasename_Log.ndf'
I get these errors when trying to restore transaction logs :
[SQLSTATE 42000] (Error 3156) Device activation error. The physical fil
e
name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'
may be incorrect.
[SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be restore
d to
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. Use
WITH MOVE to identify a valid location for the file.
Has anyone encountered the same problem? This has me stumped, although
restoring the tlog with move, and standby works...this is not how it should
happen.
--
Senior SQL Server DBAHi,
What is the process u are following to move.
I mean to say that do u move the database (mdf and ldf) with move
option or u just copy it from one location to the other.
Ur logfile may be corrept while copiying .
Use with move option to move the files.
from
Doller
Clint Pugh wrote:
> I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA|||Hi,
If u are moving the tlog also then u can use something like this
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
GO
-- Apply the first transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH NORECOVERY
GO
-- Apply the last transaction log backup.
RESTORE LOG MyNwind
FROM MyNwind_log2
WITH RECOVERY
GO
TO know more pls read move database in BOL
hope this helps u
from
Doller|||The files are being copied using SCP, this is required because of firewall
rules.
The TRN files are the restored to the DR Database.
This is working fine for 2 other databases on the same server.
--
Senior SQL Server DBA
"doller" wrote:

> Hi,
> What is the process u are following to move.
> I mean to say that do u move the database (mdf and ldf) with move
> option or u just copy it from one location to the other.
> Ur logfile may be corrept while copiying .
> Use with move option to move the files.
> from
> Doller
>
> Clint Pugh wrote:
>|||Seems like SQL Server cannot create the database file names you have specifi
ed. Perhaps the service
account don't have permissions, or that the file names are already in use by
some other database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Clint Pugh" <clintp@.datacom.co.nz> wrote in message
news:E8BAE427-D016-4117-B562-566171F0EE2F@.microsoft.com...
>I have a database i am m oving to another server, during the process I am
> moving the data and log files to another drive.(Which I have done countles
s
> times before with no problems)
> The problem I am having is after I restore the database using the followin
g
> statement :
> RESTORE DATABASE CMAMSPROD FROM DISK = 'C:\Databasename.BAK'
> WITH STANDBY = 'D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Databasename\databas
ename.STANDBY'
> ,MOVE 'Databasename_Data' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Data.mdf'
> ,MOVE 'Databasename_Log' TO 'D:\Program Files\Microsoft SQL
> Server\MSSQL\Data\Databasename_Log.ndf'
> I get these errors when trying to restore transaction logs :
> [SQLSTATE 42000] (Error 3156) Device activation error. The physical f
ile
> name 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ld
f'
> may be incorrect.
> [SQLSTATE 42000] (Error 5105) File 'Databasename_Log' cannot be resto
red to
> 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Databasename_log.ldf'. U
se
> WITH MOVE to identify a valid location for the file.
> Has anyone encountered the same problem? This has me stumped, although
> restoring the tlog with move, and standby works...this is not how it shou
ld
> happen.
> --
> Senior SQL Server DBA