0

I cannot run backup jobs or commands when using folders OTHER than the ones specified in SQL Server Properties, here: SQL Server Properties Only those the backup can be saved to.

For example:

BACKUP DATABASE myDB
TO DISK = @name
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of myDB';

We have a new installation of SQL server 2019 on same machine (previously version 2016).

I have tried various suggestions from dba.stackexchange (e.g. this one) or internet:

  1. multiple user accounts on SQL server agent (all of them have access to any location tried)
  2. create credentials for the backup job failing on "Cannot open backup device"

I am desperate, any suggestion on how to enable saving a backup to any local disk/network location will be appreciated very much

Improve this question
11
  • 1
    multiple user accounts on SQL server agent (all of them have access to any location tried) Have you tried the user account on the SQL Server Engine, which creates the backup. Not the Agent account.
    – Peter
    Commented Jan 13, 2023 at 8:59
  • @Peter which one from these is the Engine?
    – myro
    Commented Jan 13, 2023 at 10:28
  • 1
    The one in blue
    – Peter
    Commented Jan 13, 2023 at 11:51
  • 1
    Backups are done by the Engine, not Agent. Agent may act as a job scheduler, but it connects to the engine and runs the T-SQL needed. You can run XE or trace to see this, you don't need an official document.
    – Sean Gallardy
    Commented Jan 13, 2023 at 14:04
  • 1
    And if you set the ACLs for the SQL Service [NT Service\MSSQLSERVER] for a default instance, and still are having issues, you can use procmon to observe all the file system calls, along with the identity making the calls to see what fails. learn.microsoft.com/en-us/sysinternals/downloads/procmon
    – David Browne - Microsoft
    Commented Jan 13, 2023 at 15:38

1 Answer 1

Reset to default
0

You might want to grant write and modify access on the folder of interest to NT Service\MSSQLSERVER. Basically, right click the folder go to properties, security, click on edit the click on add. Enter the service name as shown below and click ok. Cross check to ensure read and modify permissions are marked for NT Service\MSSQLSERVER

sql permission

Improve this answer
4
  • I cannot grant it on a network folder though and that is crucial for me. It used to work before on a different installation I do not have an access to anymore.
    – myro
    Commented Jan 16, 2023 at 16:20
  • For a network folder grant access to the computer account eg YourDomain\ServerName$, or use a Domain Account to run SQL Server and grant the permssions to that. Also drive letter mappings are per-user. When backing up to the network use a UNC, eg \\servername\sharename\folder\file.bak
    – David Browne - Microsoft
    Commented Jan 16, 2023 at 20:26
  • @DavidBrowne-Microsoft this computer account YourDomain\ServerName$ does not exist. The network folder is actually a Synology, so the user has to be from domain. I will have to change the account running the sql service, but I am afraid of some unknown consequences
    – myro
    Commented Jan 17, 2023 at 8:04
  • I changed the account to a domain one that had required permissions and it works. Thanks
    – myro
    Commented Jan 18, 2023 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.