'Cannot restore SQL Server 2008R2 database

I took a backup of the database on a disk from a server that I do not have online access to. Trying to restore the database on my local machine using:

RESTORE DATABASE MYDB FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WXVMSSQL\MSSQL\Backup\mydb' WITH REPLACE, RECOVERY

I get the following error:

Msg 5133, Level 16, State 1, Line 1 Directory lookup for the file "D:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.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 'LOGICAL_DEV_Data' cannot be restored to 'D:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.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 "D:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.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 'LOGICAL_DEV_Log' cannot be restored to 'D:\MSSQL2008\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydb.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.

It seems to be looking at the D: drive which was the server drive I copied the backup. I tried the same command above

WITH MOVE 'C:\.....mdf' and MOVE 'C:\.....ldf'

I get the following:

Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (64000:1024) in database "mydb" as read from the backup set. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

It is also hanging with the status Restoring ... MS server management studio, I also tried CONTINUE_AFTER_ERROR still to no avail. any help is greatly appreciated

sql-serversql-server-2008


Solution 1:[1]

On the server, this database resided on a drive D:; the file paths are part of the backup. (The intention is that the most usual case is probably to restore a database on the same server it was backuped from).

This explains the 'WITH MOVE'-clause, you are requested to add.

  • Have you checked, if the paths given are valid on your system?

  • Do you run the same or a later release of MS SQL Server than the system the backup was taken on? You can find out with

    select @@version;

Solution 2:[2]

You need check drive using xp_fixeddrives

You have to see file list

RESTORE FILELISTONLY 
FROM DISK = 'D:\logshipping\DEMO.bak' WITH FILE = 1

You have to use move option for restoration.

RESTORE DATABASE DEMO_
FROM DISK = 'D:\logshipping\DEMO.bak'
WITH
MOVE 'Demo' TO 'D:\MSSQL_DATA\DEMO\DEMO_Winmedicare.mdf',
MOVE 'Demo_log' TO 'E:\MSSQL_LOG\DEMO_Winmedicare\DEMO_log.ldf', norecovery ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Larnu

Related Questions