Access denied when attempting to restore database backup
I have run a cuple of times in the past with an access denied error when attempting to restore an SQL Server 2005 / 2008 database backup. As I always forget the solution, I figured out it is time to post it here in case it happens again.
The error I am getting is something like this:
"Msg 3201, Level 16, State 1, Server , Line 1 Cannot open backup device 'c:\backup\master.bak'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Server , Line 1 BACKUP DATABASE is terminating abnormally."
This happens even if I am connected to SQL Server with a user that has restore privileges (ie. db_owner), and even if such user has access rights to the file.
The problem occurs because the user that has to have permissions over the file is not the one which is connected, but the user which SQL Server service is using. By default in SQL Server 2005/2008 it is Network Service. So you have two options:
1) Give Network Service read permissions over the backup file.
2) Change the account that the service uses from Network Service to Local System. This is less secure than option 1), but in development I prefer this since security is not an issue, and I will avoid having this problem again and again with every backup I want to make.
Hope this post helps someone other than me and my fragile memory!
Regards !