CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.

Knowledge Base

Breadrumbs:
>>Development (Web and Desktop) >> Database >> Microsoft SQL Server Management Studio
sqlagent cannot open backup device \\network-share\database.bak

Share With Friends ^{(Updated 6-8-2010)}

Article ID:
232
Date Created
Tuesday, November 24, 2020
This Article Has been Viewed
602 times
Short Desc
Using SQL Server Agent Jobs to create a backup scheduler to a network drive without the proper permissions will result in the "Operating system error 5(Access is denied.)"
Details
If the network folder you want to use for a backup location does not have adequate rights, you will receive the following error.

11/23/2020 23:53:55,Backup,Error,,ServerName-01\SQL2019,Backup,,,The job failed. The Job was invoked by User YourUsername. The last step to run was step 1 (Database).,00:00:01,0,0,,,,0
11/23/2020 23:53:55,Backup,Error,1,ServerName-01\SQL2019,Backup,Database,,Executed as user: Your-Domain\sqlagent. Cannot open backup device '\\Network-Path\SQLBackup\11-23-2020\11-23-2020-DatabaseName.BAK'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)
Recreate Issue
Not properly setting permissions for the SQL Agent user to access the network share folder resource location.
Resolve Issue
Right-click on the network folder.
Step #1:
Click the [Security] Tab
Click [Edit]
Click [Add]
Type in the SQL Agent account name and choose [OK]
Give [Full Control]
[Click [Apply] then [OK]

Step #2:
Choose the [Sharing] tab.
Click on [Share...]
Select the SQL Agent User.
Under [Permission Level] choose [Read/Write]
Click on [Share]

All Topics