Cannot open backup device. Operating System error

问题:

Below is the query that I am using to backup (create a .bak) my database.

However, whenever I run it, I always get this error message:

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\Users\Me\Desktop\Backup\MyDB.Bak'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This is my query:

BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';

Thanks in advance.

回答1:

Yeah I just scored this one.

Look in Windows Services. Start > Administration > Services

Find the Service in the list called: SQL Server (MSSQLSERVER) look for the "Log On As" column (need to add it if it doesn't exist in the list).

This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)

NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.

Apply and wait for the permissions to propogate, try the backup again.

NOTE 2: if you are backing up across the network and your SQL is running as "Local Service" then you are in trouble ... you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).

NOTE 3: If you're running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.

回答2:

Go to the SQL server folder in start menu and click configuration tools Select SQL Server configuration manager On SQL server services, on the desired instance change the (Log On as) to local system

回答3:

The SQL Server service account does not have permissions to write to the folder C:\Users\Kimpoy\Desktop\Backup\

回答4:

I had this issue recently as well, however I was running the backup job from server A but the database being backed up was on server B to a file share on server C. When the agent on server A tells server B to run a backup t-sql command, its actually the service account that sql is running under on SERVER B that attempts to write the backup to server C.

Just remember, its the service account of the sql server performing the actual BACKUP DATABASE command is what needs privileges on the file system, not the agent.

回答5:

I was just going through this myself. I had ensured that my MSSQLSERVER login user had full access but it was still causing issues. It only worked once I moved the destination to the root of C. More importantly out of a user folder (even though I had a share with full permissions - even tried "Everyone" as a test).

I don't know if i consider my issue "fixed", however it is "working".

Just a FYI for any other users that come across this thread.

回答6:

I face the same problem with SQL Express 2014 SP1 on Windows 10.

Solution which work

1. Open Service by typing Services
2. Locate and open the SQL Server (SQLExpress)
3. Go to the LogOn Tab
4. Choose Local System Account ( Also Check for Allow Services to interact with desktop )
5. Click OK . Stop the service . Restart the service.
6. Problem solved

回答7:

I had a similar issue. I added write permissions to the .bak file itself, and my folder that I was writing the backup to for the NETWORK SERVICE user. To add permissions just right-click what file/directory you want to alter, select the security tab, and add the appropriate users/permissions there.

回答8:

Here is what I did to by-pass the issue.

1) Go to backup

2) Remove the destination file-path to disk

3) Click on Add

4) In the File name: check box manually type in the backup name after ..\backup like below where Yourdb.bak is the database backup name

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Yourdb.bak

5) Click on OK

Hope this helps!

回答9:

I had the same issue and the url below really helped me.

It might help you as well.

http://blog.sqlauthority.com/2011/04/13/sql-server-fix-error-msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/

回答10:

I know it is not an exact solution but using external drive paths solves this problem.

BACKUP DATABASE AcinsoftDB
TO DISK = 'E:\MyDB.Bak'
WITH FORMAT,
MEDIANAME = 'C_SQLServerBackups',
NAME = 'Full Backup of MyDB';

回答11:

Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'C:\Backup\Adventure_20120720_1024AM.trn'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating abnormally.

I verified backup folder on C drive, Is new service account is having full control access permission or not?, I realized that "Test\Kiran" service account is not having Full control security permission.

Please follow the below steps to give full control to service account:

1. Go to C drive, Right click on Backup folder.
2. Select Security tab.
3. Click on Edit button, new window will open.
4. Click on Add button and enter Test\Kiran user account and click check name button, this will validate you entered user is existing or not, if it is existing it will show the user on window, select OK.
5. Select you entered user name and select Full Control check box under allow.

回答12:

Please check the access to drives.First create one folder and go to folder properties ,

You may find the security tab ,click on that check whether your user id having the access or not.

if couldn't find the your id,please click the add buttion and give user name with full access.

回答13:

Share this folder and use UNC path, by example: \pc\backups\mydb.bak

Then You can stop share.

Not very elegant, but it resolves all permissions problems (You need to give permissions to share as well, as mentioned above)

回答14:

I experienced this problem when the .BAK file was temporarily stored in a folder encrypted with BitLocker. It retained the encryption after it was moved to a different folder.

The NETWORK SERVICE account was unable to decrypt the file and gave this thoroughly informative error message.

Removing BitLocker encryption (by unchecking "Encrypt contents to secure data" in the file properties) on the .BAK file resolved the issue.

回答15:

I have the same error. Following changes helped me to fix this.

I had to check Server Manager->Tool->Services and find the user ("Log On As" column) for service: SQL Server (SQLEXPRESS).

I went to the local folder (C:\Users\Me\Desktop\Backup) and added "NT Service\MSSQL$SQLEXPRESS" as the user to give Write permissions.

回答16:

Hi you need to change the query from:

BACKUP DATABASE AcinsoftDB
TO DISK = 'C:\Users\Me\Desktop\Backup\MyDB.Bak'

to

BACKUP DATABASE AcinsoftDB
TO DISK = N'C:\Users\Me\Desktop\Backup\MyDB.Bak'

You have to add a N in front of the path works for me.

回答17:

In order to find out which user you need to give permission to do the restore process, you can follow the following steps:

You need to go to your server where SQL Server is installed. Find SQL Server Configuration Manager

Next, you need to go to "SQL Server Services"

Under your SQL Server (MSSQLSERVER) instance there will be an account with column "Logon As", in my case it is NT Service\MSSQLSERVER.

That is the account which you need to add under Security tab of your source .bak location and give that user the "Read" permissions so that the backup file can be read.

Let's say your backup file is present at "D:\Shared" folder, then you need to give permissions like this: