Restore Log looking for Log path...

[paulfl]

I am having difficulty restoring a database (DB_1) consists of 2 datafiles and one log file, DB Structure is as following:

Filegroup PRIMARY with file name 'fnm_data' with physical file name and location D:\db\file_1.mdf,
Filegroup 'FG1' with file name 'fgnm1_data1' with physical file name and location F:\db\file_FG1.mdf,
One log file with file name 'fnm_log' with physical file name and location (E:\log\log_db.ldf)

{Note} Logfile resides on E drive whereas Datafiles reside on several other drives.

I need to restore the DB on another machine. And I need to move the log file to a drive other than E.In this case it is K drive. Hence I have used the following code,

restore log DB_1 from disk='M:\bkupc\cciclog.txt'

WITH RESTRICTED_USER , MOVE 'fnm_log'

TO 'K:\DB\log_db.ldf', recovery, replace,RESTART

go


When I try to restore from backup files, I keep error message saying "Physical file name E:\log\log_db.ldf may be incorrect." 'fnm_log' cannot be restored. Use Move command to identify a valid location for the file.

Even though I am using Move command to move the log file.


Then I found that if I create a drive with E: and a folder named 'log' then restore program runs alright. That is, when E:\log exists then code does run smoothly. All the restore code is looking for the presence of path of the log file from where it is been backed up. In this case it is E:\log folder. Once the database is restored I could kill that directory and nothing happens. Also log file has been restored on K:\DB only.

Why the restore code is looking for the initial drive letter and path even though I have used Move command? Is there an issue in my code?

Any help will be highly appreciated.

[skhanal]

You have to put move statement for data files as well.

restore log DB_1 from disk='M:\bkupc\cciclog.txt'
WITH RESTRICTED_USER ,
MOVE 'fnm_data' TO 'D:\db\file_1.mdf',
MOVE 'fnm_data' TO 'F:\db\file_FG1.mdf',
MOVE 'fnm_log' TO 'K:\DB\log_db.ldf',
recovery, replace,RESTART

go

[paulfl]

Move command for the data files are present, I did not write in my mail to reduce number of words....
Any other thoughts? this thing is killing me? Anyone from Microsoft? Is the path of the log file need to be present to restore data files and log files even though I am trying to move the files?

[rmiao]

You mean the db you want to restore to doesn't have log file? Is e:\ a local drive on the server?

[paulfl]

Maybe If I write in details that might help:
Every month we take a backup of one DB ( i.e. DB_1). Then we restore that DB in 3 other machines in different geographic locations.
Source machine from where I am taking backup consists of 2 data files residing on two drives (D and F) and log file is residing on E drive. Backup is done is using this script onto a drive H;

backp database DB_1
file='fgnm1_data1',
filegroup = 'Fg1'
to disk = 'H:\bkupc\bk1.txt'
with restart
go

backp database DB_1
file='fnm_data' ,
filegroup = 'PRIMARY'
to disk = 'H:\bkupc\bk2.txt'
with restart
go

backup log DB_1
to disk = 'H:\bkupc\DB_1log.txt'
with restart
go


I am using following script on the other two machines.

RESTORE DATABASE DB_1
file ='fnm_data',
filegroup='PRIMARY'
FROM disk= 'M:\bkupc\bk2.txt'
WITH RESTRICTED_USER , MOVE 'fnm_data' TO 'D:\database\file_1.mdf',NORECOVERY ,replace,RESTART
go

RESTORE DATABASE DB_1
file ='fgnm1_data1',
filegroup='FG1'
FROM disk= 'M:\bkupc\bk1.txt'
WITH RESTRICTED_USER , MOVE 'fnm_data' TO 'D:\database\file_FG1.mdf',NORECOVERY ,replace,RESTART
go

restore log DB_1
from disk='M:\bkupc\DB_1log.txt'
WITH RESTRICTED_USER , MOVE 'fnm_log' TO 'K:\log\log_db.ldf', replace,RESTART
go

When I try to restore from backup files, I keep error message saying "Physical file name E:\log\log_db.ldf may be incorrect." 'fnm_log' cannot be restored. Use Move command to identify a valid location for the file.

Even though I am using Move command to move the log file.
If I create a path with E:\log on the machines where I am trying to restore DB_1, everything runs smoothly. Otherwise I get the error message. Funny thing is same code does run on one of the machines but would not run on two other machines... ANY THOUGHTS will be highly appreciated????

[nosepicker]

Try eliminating the "REPLACE" command?

[rmiao]

All target servers have k:\ drive? Sql service account has permission on it? All of them have same version and sp level?

[paulfl]

Tried running the code removing Replace part, still get the error msg.
Target servers do have K drive and SQL does have permission to write on it. Server do have the same SP and pathches...REally really puzzled...

[rmiao]

Enough free space on it? Check space required with 'restore filelistonly'.