DataBase restore Logfile path Error message

Question

Post reply

DataBase restore Logfile path Error message

d-229148

SSC Enthusiast

Points: 109
More actions
May 11, 2007 at 2:13 pm

#172373
I am having difficulty restoring a database (DB_1) with 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
{Note: I am writing the rest of the restore code where I am restoring datafiles}
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.

Viewing 4 posts - 1 through 3 (of 3 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

The WITH MOVE is only used as part of the RESTORE DATABASE command.

From the BOL:

D. Restore a database and move files

This example restores a full database and transaction log and moves the restored database into the C:\Program Files\Microsoft SQL Server\MSSQL\Data directory.

RESTORE DATABASE MyNwind

FROM MyNwind_1

WITH NORECOVERY,

MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.mdf',

MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'

RESTORE LOG MyNwind

FROM MyNwindLog1

WITH RECOVERY

-SQLBill

d-229148 SSC Enthusiast Points: 109 More actions · Answer 2

{The WITH MOVE is only used as part of the RESTORE DATABASE command.}

I am not sure I am following your statement. Move command could also be used with Restore Log command...from the BOL

RESTORE LOG { database_name | @database_name_var }

[ FROM < backup_device > [ ,...n ] ]

[ WITH

[ RESTRICTED_USER ]

[ [ , ] FILE = { file_number | @file_number } ]

[ [ , ] PASSWORD = { password | @password_variable } ]

[ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ]

[ ,...n ]

Am I missing something?

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Yes, but when you restore the Database you are restoring both the data and the log files. You need to tell it then where the log file will go. After that, you need to use the move in any additional restores also.

-SQLBill