SQL Clone
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1187 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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 4564
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1187 Visits: 690
Thank you, that worked.
John Rowan
John Rowan
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 4564
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3455 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 Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 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
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6457 Visits: 9177
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
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7806 Visits: 4564
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 Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: Administrators
Points: 84193 Visits: 19223
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 Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 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