2

Need help in resolving the issue with backing up a DB using Ola Hallengren's stored procedure to a shared folder.

I have a procedure that is executed at SQL Server startup. It runs Ola Hallengren's DatabaseBackup procedure. The SQL Server service runs under NT Service.

The share has permissions for the machine where SQL runs.

Now, if execute DatabaseBackup manually it goes fine. When it executes automatically at startup it fails with the following error:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 786 [Batch Start Line 0] The directory \\*** does not exist

If I alter the procedure with an Execute As DBO clause, it fails with the same error.

I also tried to Alter the SP with Execute As Self clause - the same error again.

So the question is:

What should I do to make it work?

  • The file path is provided as a UNC path (e.g. \\machine2\backup\)
  • This is SQL Express, so I don't have access to SQL Agent jobs.
  • Therefore, this is a stored procedure configured to run when the SQL Server instance starts up, not a SQL job set to run when SQL Agent starts.
5

Community wiki answer:

Use a domain account with access rights on the share, then this should work. NT_Service will not be able to reach it.

If using a domain account is not possible for some reason, you can try granting the machine account permission to the share. The machine account follows the following format: DOMAIN\MachineName$. The $ is needed to denote it's a computer and not a user.

2

As it turned out, there is a known issue with the undocumented command xp_fileexist that is used inside Ola Hallengren's DatabaseBackup stored procedure when executed under sa and checks a shared folder.

There are several solutions of the issue. But I preferred to comment the block where the stored procedure is used. Now it works fine. Special thanks to sql.ru forum.

  • Can you please show me what part of the script you commented out? – dgonza Feb 20 '18 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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