Cannot open backup device. Operating System error 5

Question

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';

Well, the error message is pretty clear, isn't it? What user is the program running as? Does the bak file exist? Can you access it manually? — Pekka, Oct 18 '10 at 14:41
Should we assume this is for SQL Server from the file path, since you didn't put the product in the tags or title? — Powerlord, Oct 18 '10 at 14:42
I was able to run this query before I reformatted my laptop if it does any help. This is SQL Server 2008 and I am running as an administrator. The bak file does not exist since I am on the process of creating it with this query. — Smiley, Oct 18 '10 at 14:45
@Smiley Face: You have to read the SQL file through your local, not network. So it means you will have to move the SQL file from the network to your local drive first, and restore it. — mrjimoy_05, Apr 08 '13 at 08:13
https://sqlbak.com/blog/msg-3201-level-16-cannot-open-backup-device-operating-system-error-5access-is-denied/ — Channa, May 07 '19 at 07:17
@Pekka Please explain what is PRETTY CLEAR about Access Denied when running as Administrator with FULL ACCESS to all files. Did you mean to stay pretty stupid OS — Paul McCarthy, Apr 21 '23 at 02:34

score 292 · Accepted Answer · edited Nov 20 '15 at 08:48

292

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$.

edited Nov 20 '15 at 08:48

Luke Girvin

13,221
9
64
84

answered Oct 24 '11 at 02:18

Robin Vessey

4,329
1
23
21

25

The permissions/add/advanced did not find the user in the list, but I pasted in "NT Service\MSSQLSERVER" and it worked like a champ. – Mark A May 30 '12 at 20:01
4

Does adding 'everyone' permission to a folder cover this? – DevDave May 28 '13 at 14:30
3

Depends, if it is the truely public unauthenticated one then yes. If it is the "any authenticated" then LOCAL SERVICE on a remote machine will not usually qualify ... but do you really want to give everyone access, for us that is a near sackable offense. – Robin Vessey May 28 '13 at 23:03
1

Didn't work for me, and I'm just backing up to C:\temp\. Folder Permissions screen just wouldn't recognise the Logon used by the service, **NT Service\MSSQLSERVER**, even when pasting as @Mark A suggested. Is there a good alternative account for that service to use? – MGOwen Dec 01 '14 at 12:12
8

Worked for me, great solution! I am using SQL Server Express 2014 so you would need to give write permission to 'NT Service\MSSQL$SQLEXPRESS'. – mikhail-t Jan 15 '15 at 15:35
For what it's worth, I gave Everyone access to the folder and it was fine. I'm not overly worried, since a. nobody unauthenticated should have access to the machine, and b. as soon as I'm done importing the backup, I'm moving it out of the temp folder. :p – neminem Jun 25 '15 at 21:49
I've got to provide "Full access" to "Everyone" to get it worked. My SQL server 2014 works under "Network service". – Roman Sinyakov Jun 10 '16 at 09:27
For NT Service\MSSQLSERVER, if you have the "Select Users or Groups" dialog up, you might want to make the location your local machine name, and make sure the "Object Types..." button includes service account. – Derek Jun 24 '16 at 10:35
There is a default backup location that the MSSQL account usually has write permissions to upon installation. I found it here for SQLEXPRESS: `C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup`. However I wasn't able to find the command that wrote the backup to the default location directly. The `BACKUP DATABASE...` query seems to always require an absolute path. I wonder if there's a way to automatically just backup to that location in `sqlcmd`. – CMCDragonkai Feb 01 '17 at 06:30
try run as administrator – Fares Ayyad Mar 28 '18 at 10:25
This is beyond weird, but even though the SQL Agent Job logs said it was using User-A (the user setup to run the SQL Server Agent service), it was actually still using User-B (the user setup to run the main SQL Server service), who did not have permissions. Changing the main SQL Server Service to the permissioned User-A fixed the issue for me. Thank you! – Overhed Apr 13 '20 at 18:44

score 23 · Answer 2 · answered Mar 21 '12 at 17:34

23

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

answered Mar 21 '12 at 17:34

Abuleen

453
4
8

score 12 · Answer 3 · answered Oct 06 '18 at 04:48

12

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:

answered Oct 06 '18 at 04:48

Raghav

8,772
6
82
106

Work for me. In my case the server is in a AD Domain, but the user MSSQLSERVER is a local machine user, can be necessary to change the "From this location:" in "Select User or Group" window. – David Lopes Oct 12 '21 at 08:28
Thank you for your clear directions. Though, if you want to create a backup, as the proponent says, there is to add also the Write permission. Just tested. – Nicola Mingotti Aug 25 '22 at 21:00
Thanks for the directions with screenshots! Right, like what Nicola said, you would need a write if you going to backup files in it. – Yoshiaki Jul 17 '23 at 02:10

score 8 · Answer 4 · answered Jul 03 '19 at 06:43

One of the reason why this happens is you are running your MSSQLSERVER Service not using a local system. To fix this issue, use the following steps.

Open run using Windows + R
Type services.msc and a services dialog will open
Find SQL Server (MSSQLSERVER)
Right click and click on properties.
Go to Log on tab
Select Local System account and click on "Apply" and "OK"
Click on Stop link on the left panel by selecting the "SQL Server (MSSQLSERVER)" and Start it again once completely stopped.
Enjoy your backup.

Hope it helps you well, as it did to me. Cheers!

score 7 · Answer 5 · answered Oct 18 '10 at 15:04

7

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

answered Oct 18 '10 at 15:04

Martin Smith

438,706
87
741
845

28

how do I give the SQL Server Service Account permissions to access this folder? :) – Smiley Oct 18 '10 at 15:12

score 6 · Answer 6 · answered Oct 03 '12 at 02:47

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.

score 6 · Answer 7 · answered Jul 26 '16 at 19:33

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

Solution which work

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

score 4 · Answer 8 · edited Apr 02 '14 at 18:36

4

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.

edited Apr 02 '14 at 18:36

hjavaher

2,589
3
30
52

answered Apr 02 '14 at 18:13

DNK

41
1

1

I think this is caused by enterprise domains that have user paths residing on network drives so they are accessible by the user transparently regardless of machine they log into. Such as this situation: http://superuser.com/a/730519 – Bon Mar 23 '16 at 15:28
@Bon Thanks! This sorted out the issue for me. The path on which I had the backup file was actually a network path. – JonM Apr 16 '19 at 10:49

Mehran Rasa · Answer 9 · 2023-04-10T20:10:04.577

3

In my case, I forgot to name the backup file and it kept giving me the same permission error :/

TO DISK N'{path}\FILENAME.bak'

edited Apr 10 '23 at 20:10

answered Feb 03 '20 at 10:58

Mehran Rasa

99
1
5

This solution worked for me. Simply adding the filename.bak works to solve the error. – Innocent_Kyalo Apr 01 '23 at 12:42

score 2 · Answer 10 · edited Aug 29 '16 at 07:01

2

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!

edited Aug 29 '16 at 07:01

Nikolay Mihaylov

3,868
8
27
32

answered Aug 29 '16 at 06:45

dpen82

244
1
2
13

score 2 · Answer 11 · answered Apr 19 '11 at 14:31

2

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.

answered Apr 19 '11 at 14:31

Eric Bernier

459
1
7
17

1

what do you mean by appropriate ? – Steam Mar 05 '14 at 18:28

score 2 · Answer 12 · answered Jul 24 '20 at 13:06

2

I solved the same problem with the following 3 steps:

I store my backup file in other folder path that's worked right.
View different of security tab two folders (as below image).
Edit permission in security tab folder that's not worked right.

answered Jul 24 '20 at 13:06

Mohsen Najafzadeh

411
1
6
12

score 1 · Answer 13 · answered Jul 07 '16 at 11:42

1

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';

answered Jul 07 '16 at 11:42

Cem Mutlu

1,969
1
24
24

score 1 · Answer 14 · answered Dec 12 '17 at 13:32

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.

score 1 · Answer 15 · answered Aug 28 '19 at 13:13

SQL Server is not able to access (write) the backup into the location specified.

First you need to verify the service account on which the Sql server is running. This can be done by using Configuration manager or Services.msc.

or

Use below query :

SELECT  DSS.servicename,
    DSS.startup_type_desc,
    DSS.status_desc,
    DSS.last_startup_time,
    DSS.service_account,
    DSS.is_clustered,
    DSS.cluster_nodename,
    DSS.filename,
    DSS.startup_type,
    DSS.status,
    DSS.process_id FROM    sys.dm_server_services AS DSS;

Now look at the column service_account and note it down.

Go to the location where you are trying to take the backup.In your case : C:\Users\Me\Desktop\Backup

Right click--> Properties --> Security -->

Add the service account and provide read/write permissions. This will resolve the issue.

score 0 · Answer 16 · answered Mar 28 '13 at 12:09

0

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/

answered Mar 28 '13 at 12:09

Jacques Kammeyer

37
1
6

score 0 · Answer 17 · edited Jun 09 '13 at 22: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:

Go to C drive, Right click on Backup folder.
Select Security tab.
Click on Edit button, new window will open.
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.
Select you entered user name and select Full Control check box under allow.

score 0 · Answer 18 · answered Aug 21 '13 at 12:23

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.

score 0 · Answer 19 · answered Sep 24 '16 at 09:16

0

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)

answered Sep 24 '16 at 09:16

Gennady G

996
2
11
28

score 0 · Answer 20 · answered Oct 25 '16 at 20:05

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.

score 0 · Answer 21 · edited Dec 18 '17 at 15:51

0

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.

edited Dec 18 '17 at 15:51

Bipul Roy

163
1
14

answered May 08 '17 at 02:42

Robert Peter Bronstein

166
1
8

score 0 · Answer 22 · answered Jan 26 '21 at 17:46

My issue was that the "File Ownership" was set to my company. I changed it to "Personal" and it worked. Right click the file and click the "File Ownership >" option and then change it to "Personal". I believe this happens with all files sent over Microsoft Teams.

score 0 · Answer 23 · answered Mar 23 '22 at 08:28

If the backup destination path resides on your local machine, change the account of 'SQL Server' service to 'Local System Account', then everything must be resolved, keep in mind that the 'SQL Server' instance service is responsible to access the backup destination so the account it is running under, must have access to the destination path of your backup.

score 0 · Answer 24 · answered Oct 27 '22 at 17:23

0

Make sure you are actually saving to a FILE and not a folder,

My problem was I was simply putting in the Folder path and not the File path

You want this

'F:\Database Backup\Pharmacy\data.bak';

You dont want this

'F:\Database Backup\Pharmacy';

answered Oct 27 '22 at 17:23

Jamisco

1,658
3
13
17

score 0 · Answer 25 · answered May 15 '23 at 11:58

LINUX users

Make sure the folder exists, in my case, I am taking a backup in folder SQL_bakcup

change the folder permission by executing the below command. chmod 777 SQL_bakcup

Now run the below query, make sure to put the correct path

USE [master];
BACKUP DATABASE [master]
TO DISK = 'PATH/SQL_bakcup/SQL_LOCAL_DB.bak' 
WITH NOFORMAT, NOINIT,
NAME = 'SQL_LOCAL_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;

Cannot open backup device. Operating System error 5

25 Answers25

Linked