Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restoring database from another server


Restoring database from another server

Author
Message
ramadesai108
ramadesai108
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 690
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.
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4498 Visits: 4530
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
ramadesai108
ramadesai108
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 690
Thank you, that worked.
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4498 Visits: 4530
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
SQL ORACLE
SQL ORACLE
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1725 Visits: 1314
Did you try to add MOVE option in your RESTORE clause?
Or did you try to create necessary folders for your restore?
extremenovice
extremenovice
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 1140
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
homebrew01
homebrew01
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3401 Visits: 9084
Instead of resurrecting a 2+ year old thread, I think it would be to everyone's benefit to start a new thread.



John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4498 Visits: 4530
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
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42340 Visits: 18876
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
My Blog: www.voiceofthedba.com
extremenovice
extremenovice
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 1140
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search