Restoring a backup file from serverA to serverB

Question

Post reply

Restoring a backup file from serverA to serverB

extremenovice

SSCrazy

Points: 2118
More actions
September 9, 2010 at 10:07 am

#140418

Hi, 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
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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

from memory you use the logical filename in move

RESTORE DATABASE [testdb]

FROM DISK = N'\\serverA\c$\sqlbackups\testdb.bak'

WITH FILE = 1,

MOVE N'testdb_Data' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Data.mdf',

MOVE N'testdb_Log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Log.ldf',

NOUNLOAD, STATS = 10

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 2

Steve is right. You need to use logical name without the .mdf and .ldf extension within the MOVE clause.

Thank You,

Best Regards,

SQLBuddy

extremenovice SSCrazy Points: 2118 More actions · Answer 3

Thank you!.... it now looks like the account i am using does not have enough permissions on the account to access the directory.

Can you confirm this please?

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Data.mdf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).

Msg 3156, Level 16, State 3, Line 1

File 'testdb_Data' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Data.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 5133, Level 16, State 1, Line 1

Directory lookup for the file "E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Log.ldf" failed with the operating system error 3(failed to retrieve text for this error. Reason: 15105).

Msg 3156, Level 16, State 3, Line 1

File 'testdb_Log' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\testdb_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SkyBVI SSCoach Points: 15128 More actions · Answer 4

Try this:-

RESTORE FILELISTONLY

FROM DISK = 'E:\xyz.bak' /*this shld be location of ur bak file*/

GO

whatever result you get from above statement in that...the logical file name(mdf and ldf part)

that you should replace with the logical_file.mdf and logical_file.ldf below mentioned

----Restore Database

RESTORE DATABASE xyz

FROM DISK = 'E:\xyz.bak'

WITH MOVE 'logical_file.mdf' TO 'E:\SQLDatabases\xyz.mdf', /*(new location where u want to move)*/

MOVE 'logical_file.ldf' TO 'E:\SQLLogs\xyz.ldf', /* (new location where u want to move)*/

STATS=10

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

It dosen't seem to be a permission issue. Check whether the Path you provided for the move is correct.

Also you can try to do the restore using SSMS GUI option.

Also check if you already have the same file in that directory.

You can even try WITH REPLACE clause to overwrite the existing file.. But be careful with this option.

Thank You,

Best Regards,

SQLBuddy

extremenovice SSCrazy Points: 2118 More actions · Answer 6

Thanks for all your posts, SKYBVI i am running the restore script on serverb - the server i want the database to be restored to not where it is backed up. Is this right? Also do i need to create an emply database on serverb called testdb before the restore?

Thanks in advance

steveb. SSC-Forever Points: 46733 More actions · Answer 7

You shoud run the script on serverB (restore server)

and you wont need to create the database beforehand

you just need to ensure that the file path

E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

exists on serverB

extremenovice SSCrazy Points: 2118 More actions · Answer 8

Hi steveb thanks for your reply it now works yay!!

Thank to everyone for your help!:-)