1

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.

Improve this question

2 Answers 2

Reset to default
2

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.
Improve this answer
4
  • So, the files definitely exist. And running with VERIFYONLY produces the same error...
    – Luke
    Commented Jul 30, 2015 at 8:00
  • Accepting because I tried these suggestions and they eventually led to finding the real problem. Tried restoring from an older backup, and it completed without error. So, we concluded that the dump file was corrupted somehow. The newer file was in fact 10x smaller than the old one, which is a little suspicious now that we noticed it.
    – Luke
    Commented Jul 30, 2015 at 8:21
  • I did have one small question that came out of this, maybe you can answer here rather than creating another question. I checked the size of the MDF file and it is 10x the size of the backup file I restored from... 1.5GB vs 150GB (the corrupted file was 15MB)... how is this the case? Is the dump file compressed somehow? I'm sure a LZW could probably get that kind of ratio. Is it some other form? I assumed it was simply a copy/dump of the entire database file.
    – Luke
    Commented Jul 30, 2015 at 8:24
  • 1
    The backup is only going to be the size of the actual amount of data in the database and won't include the empty space. From what you are explaining to me it sounds like the MDF file was grown, manually, to include a large amount of free space so SQL Server would only use the auto grow in an emergency. Check the size of the database in the SSMS in the general page of the database properties. There you will be able to see the size of the database and the available free space. The difference between the two is probably the 10x you are seeing.
    – Aaron
    Commented Jul 30, 2015 at 13:52
-5

• Run DBCC CHECKDB: It checks & reports all the error message in error log, if there is any problem with the database. Try to analyze & understand the error message logged in the error log. Re-run DBCC CHECKDB with the recommended minimum repair option to repair the file.

Improve this answer
1
  • 3
    This question is not about MS Access
    – LowlyDBA - John M
    Commented Jul 27, 2016 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.