Consider the following scenario:
- You're trying to restore a database on the database server, but the server is running out of space.
- You place the backup file on a share/file server, but you run into the following error during database restore.
These are the errors on SQL Server 2008 when restoring the database using script or through Microsoft SQL Management Studio.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\myShareServer\test\test.bak'.
Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
The Database Engine service could not resolve the specified file location.
Either the location does not exist, or the current login account does not have access to it.
Verify that the file location exists, and that the login account has permissions on it.
These are the errors on SQL Server 2012 when restoring the database using script or through Microsoft SQL Management Studio.
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\myShareServer \test\test.bak'.
Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Cannot access the specified path or file on the server. Verify that
you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the
full path for the file in the File Name control in the Locate dialog box.
I also want to clarify that I'm the system administrator on both servers. That eliminates the philosophy the logon user access control issue. Let's go to the services, double click on the SQL Server to bring up the properties and click on the Log On tab. As indicated in Figure 1, on my development environment, the SQL server is running under the context of virtual service account of the database server. The error message did make sense now because the virtual account is foreign to the file server and there is no way to add those accounts to the ACL.
Figure 1
There are several options we can employ to tame the error.
- Set the SQL Server to run under a domain account. Depending on the Group the account is under, you might need to add the account to the file server ACL.
- Add the database server name to the file server ACL with read permission. Right click the share folder, click on security tab, Edit, Add, Click on Object Types…, check the computers
I hope someone will find this information useful and equip you for future opportunity. If you find any bugs or disagree with the contents or want to help improve this article, please drop me a line and I'll work with you to correct it.
