Sql-server – SQL Server restore database file access error

sql serversql-server-2008sql-server-2008-r2

I am trying to restore a database from a file on a remote machine. The script I am using is as follows:

RESTORE FILELISTONLY
   FROM disk = '\\server\path\to\file.dmp'
RESTORE DATABASE MyDB
   FROM disk = '\\server\path\to\file.dmp'
   WITH REPLACE,
   MOVE 'MyDB_Data' TO 'D:\path\to\file.mdf', 
   MOVE 'MyDB_Log' TO 'D:\path\to\log.ldf', 
   stats = 20
GO

I am using this script because the SQL Server Management wizard appear to dislike the use of a .dmp file instead of a .bak file, though my team has used .dmp files in the past without issue. However, whilst the RESTORE FILELISTONLY command appears to complete successfully, the RESTORE DATABASE command fails with the following error.

Msg 3203, Level 16, State 1, Line 3
Read on "\\server\path\to\file.dmp" failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

This seems unusual, as it appears to be an error with the .dmp file, but the previous RESTORE command did not complain at all. On top of this, the database in question is now stuck in a neverending "Restoring…" state which I cannot seem to get it out of. I tried RESTORE DATABASE MyDB WITH RECOVERY but that gave the same error, tried adding RECOVERY to the original script but just got the same error.

Any idea what's going on? I am new to DBA, myself, so I have limited experience to draw upon.

Best Answer

Double check that the paths you are trying to restore to exist on the D drive. SQL Server will not create the directory path if it doesn't exist.

If you haven't already, run the restore command with the verify only option MSDN.

If that is successful here are a few additional things you can try to isolate why it is failing

  1. If you have the space on the SQL Server that created the backup, try to restore the backup there under a different file path and different database name (again make sure the temporary restore path(s) exist.) This will confirm if the backup is valid and can be read by SQL Server. Be very careful here to change the database name to something different and to give the file names a different name to not cause a problem with the current database. (Example: change MyDB to Restored_MyDB, MyDB.mdf to Restored_MyDB.mdf and MyDB.ldf to Restored_MyDB.ldf in the restore statement.)
  2. Confirm the account that you are using to do the restore,if it's not the same, has the same permissions that the account that did the backup does.
  3. Copy the backup locally to the machine you wish to restore it to and restore from the local drives.
  4. Change the file extension from dmp to bak.
Related Question