Q

Resolve errors received when restoring a database in SQL Server

See how to restore a database after receiving a server error message by first running this command SQL Server as advised by our expert.

I am trying to restore a database, but I keep getting the following error:

Server: Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'D:mssqlbackupMYPalma.bak'. Device error

or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

The log file displays the following error:

2005-12-11 14:19:58.26 spid102 BackupDiskFile::OpenMedia: Backup device 'd:radhapalmaMYPalma.bak' failed to open. Operating system error = 2(The system cannot find the file specified.).
2005-12-11 14:21:30.31 spid102 BackupDiskFile::OpenMedia: Backup device 'c:palmaMYPalma.bak' failed to open. Operating system error = 2(The system cannot find the file specified.).
2005-12-11 14:36:19.37 spid102 BackupDiskFile::OpenMedia: Backup device 'd:radhaMYPalma.bak' failed to open. Operating system error = 2(The system cannot find the file specified.).

However, I have restored many databases and I recall something like this working just fine:

restore database Try_Palma from disk = 'D:mssqlbackupMYPalma.bak'

Now, it's just not working. What could be the problem?

Make sure you have the path correct and that the SQL Server service account has the proper privileges to open the file you are specifying. A simple task would be to run the following command to make sure you can see the file in the operating system:

Xp_cmdshell 'dir D:mssqlbackupMYPalma.bak'

This should return the results if the file exists. Also, the D: and C: parameters without the backslash will probably have you searching in the system folders on the server. So just check to make sure you have the correct path for the backup files.

This was first published in December 2005

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close