Scenario:
abc.bak
) on one machine G:\SQLDB\backup\master_copy.bak
T-SQL used:
RESTORE DATABASE New_DB
FROM DISK = 'G:\SQLDB\backup\master_copy.bak'
WITH
MOVE 'coop_test_dat' TO 'G:\SQLDB\livedb\new_db_data.mdf',
MOVE 'coop_test_log' TO 'G:\SQLDB\livedb\new_db_log.ldf',
REPLACE
Error generated:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\SQLDB\masterdb\master_blank.mdf" failed with the operating system error 3(The system cannot find the path specified.).Msg 3156, Level 16, State 3, Line 1
File 'coop_demo' cannot be restored to 'C:\SQLDB\masterdb\master_blank.mdf'. Use WITH MOVE to identify a valid location for the file.
Here given specified path is from old machine which doesn't exists in new machine.
How can I fix this?
what Jeroen suggested was, you might be missing some files..so to see all files use below command..
restore filelistonly from disk ='your .bak path' with file=1
The output of above command will show you all the files that were backed up,then you can use restore with replace
so if restore file list showed below as logical file names
logicalname
db_Data
db_Data1
db_log
now you can use
RESTORE DATABASE New_DB
FROM DISK = 'G:\SQLDB\backup\master_copy.bak' WITH
MOVE 'db_Data' TO 'G:\SQLDB\livedb\new_db_data.mdf',
MOVE 'db_Data1' TO 'G:\SQLDB\livedb\new_db_data1.ndf',
MOVE 'db_lof TO 'G:\SQLDB\livedb\new_db_log.log',
REPLACE