This has to be the most popular error in taking database backups. There are many reasons for this error and there are different ways to resolve it. I recently faced this error and for a while I was taking backup and found a very interesting solution to the problem. The error was as mentioned below:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘d:\Log\aw.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Personally, I know this error happens when
- There is no directory with Log
- There is not enough space on drive d:\
- There is not enough permissions on d:\Log drive
In my case all of the above were not the reason for above error. The error was there because of the file was read only. There was already another backup file which existed in the folder with the name aw.bak and it was marked as read only. Due to the same reason, my backup was failing. Once I removed the checkbox for reading only, the backup was successful.
Here are few other blog posts related to this error, which I have written earlier.
- SQL SERVER – FIX – ERROR : Msg 3201, Level 16 Cannot open backup device . Operating system error 5(Access is denied.)
- SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)
- SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )
- SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database – SSMS
Reference: Pinal Dave (https://blog.sqlauthority.com)
20 Comments. Leave new
To expand just a bit on the inadequate permissions bullet point: It seems like I see this sort of thing when someone tries to give capability to backup and restore to a SQL Server Login, for obvious reasons. It doesn’t have file system access.
Hi Pinal, thanks for these quick possible fixes for this issue. I experienced similar issue few weeks before and it was the permissions issue in my case. It was easy to solve as it has access word in the error.
Unable to open the physical file “d:\sql_files\ctbse.mdf”. Operating system error 5: “5(Access is denied.)”.
An attempt to attach an auto-named database for file d:\sql_files\ctbse.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Check the service account by using SQL Server Configuration Manager. Then verify that account has permission on file “d:sql_filesctbse.mdf”
Hi Pinal,
I have win2k12 server having sql server when i take backup my data it will show a error like”cannot open backup device operating system error 5(error not found) backup databese is terminating abnormally” so help me to resolve the issue….
for me, adding serverusers account to my backup folder did the job. even though my sql login and my sqlserver service account had all the rights to modify the files in that location, adding users accounts with read access did the job.
Indeed, this did the job.
Thank you.
Sir I am trying to taking backing on network shared drive using ms sql server 2005
and query is
Backup Database [Data Sync] To Disk =’\\rnd-3\Share\Tese.bak’ With FORMAT
and rnd-3\share is network path having rights everyone read/write
but when i execute showing error
”
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘\\rnd-3\Share\Tese.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
“
Is that a share of machine? As per message, service account is not having permission. Check the service account by using SQL Server Configuration Manager.
Hi sir ,
Window Server 2008
Sql Server 2012
I am executing the SQL :
BACKUP DATABASE [XYZ] TO DISK = N’C:backup-SQLXYZ.bak’
WITH NOFORMAT, NOINIT, NAME = N’XYZ-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I my case this is not working and still the following error :
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:backup-SQLXYZ.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Hi Pinal,
I am having issue while taking backup to URL
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘https://XXXX.blob.core.windows.net/uxxxc01/xxxxxdb_backup_2017_04_05_094308.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
I have scheduled job was running fine till yesterday but it is failing now i checked the access but seems like it have permission.
The request is not supported.. what is the storage type? SAS token?
Blob storage in Azure cloud
I need details about storage..
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
What to do, I tried it from log , application log but didnt get any useful information.
whenever i m trying to do backup for any database it will show this type of error massage.
FYI… in the above explanation you are taking the backup ,, i am wondering how you given the permission to backup file,,, we are not restoring backup file, could please tell me how we give permission to the future backup file.,,
can you explain me difference between backup and restore
This did not solve my problem. I was finally able to solve this by following the below steps
1. Navigate to Start/Services
2. Look for the service SQL Server (MSSQLServer) and see the username under ‘Log On As’
3. Add this user to have full control of permissions on the directory of back up by navigating to the folder / properties/ under secirity tab / Edit and give the same username and click on Check and Ad by giving full control.
4. Save
Now running the query of backup created the file.