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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4655 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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23968 Visits: 4605
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
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4655 Visits: 690
Thank you, that worked.
John Rowan
John Rowan
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23968 Visits: 4605
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12243 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 Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22416 Visits: 9222
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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23968 Visits: 4605
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 (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)SSC Guru (251K reputation)

Group: Administrators
Points: 251892 Visits: 19818
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 Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

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