April 24, 2008 at 8:58 am
Hi All,
I have a backup from another server and I want to restore the database using that backup on a local server. I have copied the file to the local server. When I try to do that I get an error "Directory look up for the file FILENAME failed with the operating system error. " It cannot find the path to that file, which shows D drive on that backup. Is there a SQLCMD that I can use to change the path?
Thank you.
April 24, 2008 at 9:30 am
This is a simple task if you use the RESTORE DATABASE T-SQL command.
First, get the file info from the backup file:
RESTORE FILELISTONLY
FROM DISK='backup file path and name'
Then, restore WITH MOVE:
RESTORE DATABASE Your New DB Name HERE
FROM DISK='backup file path and name'
WITH MOVE 'logical file name from filelist' TO 'your new location for that file',
MOVE 'logical file name from filelist' TO 'your new location for that file',
STATS,
REPLACE --if overwriting an existing DB
April 24, 2008 at 11:07 am
Thank you, that worked.
April 24, 2008 at 1:10 pm
April 24, 2008 at 2:19 pm
Did you try to add MOVE option in your RESTORE clause?
Or did you try to create necessary folders for your restore?
September 9, 2010 at 9:19 am
Hi, i have been trying to do a similar task. I am hoping to restore the backup file from serverA to serverB.
When i run the script the following error message comes back:
Msg 3234, Level 16, State 2, Line 1
Logical file 'testdb_Data.mdf' is not part of database 'testdb'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I have done the RESTORE FILELISTONLY to see what the exact logical file names are, yet still not successful.
Am i supposed to create a testdb on serverB beforehand?
Your help would be appreciated, it may be something really simple, i've been looking at this all day now and cannot see what the problem is.
Here is the script for the restore that i am running on serverB.
RESTORE DATABASE [testdb]
FROM DISK = N'\\serverA\c$\sqlbackups\testdb.bak'
WITH FILE = 1,
MOVE N'testdb_Data.mdf' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Data.mdf',
MOVE N'testdb_Log.ldf' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Log.ldf',
NOUNLOAD, STATS = 10
September 9, 2010 at 9:23 am
Instead of resurrecting a 2+ year old thread, I think it would be to everyone's benefit to start a new thread.
September 9, 2010 at 9:24 am
September 9, 2010 at 9:43 am
Please start a new thread with your message and include the results of FILELISTONLY.
September 9, 2010 at 9:43 am
Ys that probably would have been a good idea, how can i do that?
My results for the following statement:
RESTORE FILELISTONLY
from disk = 'c:\sqlbackups\testdb.bak'
testdb_DataC:\Program Files\Microsoft SQL Server\MSSQL\data\testdb_Data.MDFDPRIMARY104857635184372080640
testdb_LogC:\Program Files\Microsoft SQL Server\MSSQL\data\testdb_Log.LDFLNULL104857635184372080640
September 9, 2010 at 9:48 am
Yes, as Steve stated, please start a new thread. Then update this thread with the link to the new one and I'll hop over there......I think I see your problem.
September 9, 2010 at 10:02 am
Click the forum title at the top (Administering), then click "New Topic"
September 9, 2010 at 10:11 am
Thanks guys i have now posted a new thread. I'd be thankful i you could tell me how to close this one off now please?:-D
April 28, 2011 at 3:17 am
you can run this command without creating new database
RESTORE DATABASE testdb
FROM DISK=' 'c:\sqlbackups\testdb.bak'
WITH MOVE 'testdb_Data'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testdb.mdf',
MOVE 'testdb_Log '
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\testdb_log.ldf'
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply