Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restoring database from another server Expand / Collapse
Author
Message
Posted Thursday, April 24, 2008 8:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
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.
Post #490018
Posted Thursday, April 24, 2008 9:30 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 3,840, Visits: 3,848
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





John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #490040
Posted Thursday, April 24, 2008 11:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 3:52 PM
Points: 377, Visits: 685
Thank you, that worked.
Post #490131
Posted Thursday, April 24, 2008 1:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 3,840, Visits: 3,848
Great. Thanks for posting your results.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #490248
Posted Thursday, April 24, 2008 2:19 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
Did you try to add MOVE option in your RESTORE clause?
Or did you try to create necessary folders for your restore?
Post #490308
Posted Thursday, September 9, 2010 9:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:13 AM
Points: 98, Visits: 1,110
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
Post #983217
Posted Thursday, September 9, 2010 9:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:29 AM
Points: 2,832, Visits: 8,511
Instead of resurrecting a 2+ year old thread, I think it would be to everyone's benefit to start a new thread.


Post #983226
Posted Thursday, September 9, 2010 9:24 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 3,840, Visits: 3,848
Can you post the results of the RESTORE FILELISTONLY statement?



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #983230
Posted Thursday, September 9, 2010 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
Please start a new thread with your message and include the results of FILELISTONLY.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #983244
Posted Thursday, September 9, 2010 9:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:13 AM
Points: 98, Visits: 1,110
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_Data C:\Program Files\Microsoft SQL Server\MSSQL\data\testdb_Data.MDF D PRIMARY 1048576 35184372080640
testdb_Log C:\Program Files\Microsoft SQL Server\MSSQL\data\testdb_Log.LDF L NULL 1048576 35184372080640

Post #983245
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse