5

I have set up Ola Hallengren's SQL Server Maintenance Solution on several SQL Express servers (2008 to 2012 R2) in the past few years. I recently have started having issues with the network backup component on all of them a new one. I've had this working on several servers in the past, so I know it can work, but I can't figure out what's preventing it from working now. As a point of interest, I'm not a DBA, and know next to nothing about SQL, which is why I'm here.

The Problem

On one server in particular, I set up a maintenance schedule about a year and a half ago. It was doing backups nightly to another local server using a UNC Path (and several other commands). The code for the script is as follows:

sqlcmd -E -S SERVER\INSTANCE -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = '\\techstore1.domain.local\Backups', @BackupType = 'FULL', @Verify = 'Y', @CheckSum = 'Y', @CleanupTime = 14" -b

This worked fine for quite a while, but stopped working about a month ago. I set it to backup locally, then added a string to xcopy it to the remote location, and a script on the remote server to cleanup old backups. Not ideal.

I have tried running it in command line as myself and a super permissions account. This is the error I receive in all cases:

Msg 50000, Level 16, State 1, Server SERVER\INSTANCE, Procedure DatabaseBackup, Line 384
The directory \\techstore1.domain.local\Backups does not exist.

Msg 50000, Level 16, State 1, Server SERVER\INSTANCE, Procedure DatabaseBackup, Line 611
The documentation is available at http://ola.hallengren.com/sql-server-backup.html.

What I've Done

Obviously, SQL thinks the network location doesn't exist, so I've tried what I can to check that all the network side stuff is in order. I pulled a new copy of the script and recreated all the objects and jobs. I have checked that the other scripts (integrity checks, statistic updates, etc) work. I created a script that uses the same credentials as the backup script to run the xcopy of the local backups to the target server, so I have the proper share/NTFS credentials. That account is a domain account (AD) specifically created for SQL backups. I can backup locally (with that account), so I have database permissions. I can navigate to the share as the backup account using Windows Explorer. I can manually copy the files to the remote location using Windows Explorer using the backup account.

I get this same issue on several other networks as well, which is what turned me to SF. I'm in 2008 R2 and 2012 domains, all servers are domain members without any relevant errors. The servers are 2008 R2 and 2012 R2 Standard machines. I feel like something must have changed on the SQL side that I can't troubleshoot for this to be happening on 3 different networks, and several servers. I've used super basic commands- leaving verification and cleanup off the job- and get the same error. I've leveraged the example commands on Ola's site as a test as well, with the same results. I've tried it on a brand new SQL Server with a basic test database, no love. I've used my black belt in Google for several days with very disappointing results (maybe I don't know what to look for?).

What I Hope to Get

I would really appreciate a way to test connections to network shares inside of SQL Command line, or some materials to read that would get me there. I don't mind reading; I'm a competent systems administrator who's simply outside his depth on this issue. I've read everything on Ola's site, and I'm practically using the example commands verbatim anyway (and they worked for several months!?). I'll be working on this off and on this weekend, and any help or direction that anyone can provide would be appreciated massively.

Dave M
  • 4,494
  • 21
  • 30
  • 30
MagnaVis
  • 388
  • 1
  • 4
  • 13

5 Answers5

3

I was diagnosing a similar issue implementing Ola's scripts. It would work on some servers and not others. I would get:

"Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 395 XXX The directory does not exist."

In my case the problem was that although SQL Agent was running under a domain acct with sufficient privileges SQL Server itself was not. In some cases it was running as a Local Service.

Once I changed SQL to run as a domain acct with privileges it worked.

I hope this is of use to someone else.

Sir Swears-a-lot
  • 139
  • 4
  • My discovery was that the MSSQLSERVER account as well as the SQL Agent service account should have permissions ot the folder. – Asher Jun 05 '20 at 07:35
2

Who is the job running as? Is it running as a domain user who has access to the shares in question? Or is it running as SA? If it's running as SA, it's running as your SQL Server Agent account. Make sure that the user running the job is able to access the network share in question.

Also, the command in particular should be SERVER\INSTANCE, not SERVER\DATABASE:

sqlcmd -E -S SERVER\INSTANCE -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = '\\techstore1.domain.local\Backups', @BackupType = 'FULL', @Verify = 'Y', @CheckSum = 'Y', @CleanupTime = 14" -b

(I don't think that would cause the error you're getting, but still.)

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
  • Sorry, I thought that was clear above, I'll edit to make sure it is. The task is running as a Domain account that has permissions (both NTFS and Share) to the backup location. And, you're correct, it is '/INSTANCE', which is how I'm running it. I'll change that as well. I gave the account the task is running as permission to log into the server, and was able to copy the files directly to the target location, as well as setting up an xcopy script to do that with the same user account. Thanks for your help, I was playing with this more today, hopefully I'll figure something out! – MagnaVis Oct 15 '14 at 00:39
  • If you run the command as that domain account, does anything different/useful happen? – Katherine Villyard Oct 15 '14 at 00:41
  • Unfortunately, no. That's the account I have it scheduled as. All accounts I've tried as fail with the same error message. I ran the script as my account, and created a super-permissions account to run it, all with the same result. I can xcopy and drag-and-drop copy with all the same accounts. – MagnaVis Oct 15 '14 at 00:45
  • 1
    I just tried re-creating the share locations, because you seemed to think that was the issue too. It worked after the targets were re-created. Who knows why. Thank you again for your help and time! – MagnaVis Oct 15 '14 at 01:08
2

The issue has been resolved. I created a new network share location, giving it very open permissions (everyone:Full control), and the backup works to that location. Whatever the issue is, it must be isolated to NTFS/Share permissions on the target. I dont' know why several of my clients were all having the same issue, but this fix worked on all of them. I've essentially re-created all my backup locations, and shared them out again. We may never know what went wrong in the first place.

Thank you to everyone who took the time to read and think about this issue.

MagnaVis
  • 388
  • 1
  • 4
  • 13
  • 1
    Indeed, it has to do with NTFS permissions, both MSSQLSERVER & Agent services should be able to reach and should be able to logon at the fileshare. Afterwards SQL should be able to write files. – JKoopman Oct 14 '16 at 14:20
  • Thanks for the comment. This was resolved 2 years ago, and the problem has not resurfaced. – MagnaVis Oct 16 '16 at 22:14
0

Solution: Restart your SQL agent service Only - it works for us.

I have faced the same issue in recent days, the stored procedure dbo.DatabaseBackup is not able to identify the correct file\folder attributes which is why it raised an error that your backup location is not able to be accessed by the domain user on which the SQL Agent services are running.

Code snippet of stored procedure dbo.DatabaseBackup:

If object_ID(N'tempdb.dbo.#File_Results') is not NULL
    Drop table #File_Results;

CREATE TABLE #File_Results (
File_Exists int,
File_is_a_Directory int,
Parent_Directory_Exists int
)

DECLARE @FileName varchar(255)
SET @FileName='\\DBLIVEBACKUPS\Dump$$'

INSERT INTO #File_Results
EXEC Master.dbo.xp_fileexist @FileName

SELECT * FROM #File_Results

We are getting correct parameters for different network locations on the same server.

Glorfindel
  • 1,213
  • 3
  • 15
  • 22
Jignesh
  • 1
  • 1
    The servers I was running the script on were Express instances, so they weren't running the SQL Agent service. Can't hurt to have another answer for people who may be looking and not have the same problem exactly that I did, but this wouldn't have resolved my issue. – MagnaVis Jan 26 '17 at 07:58
-1

The following should fix this issue. Set the SQL Server's user AND the SQL Server Agent on both the Share and the folders as Change for the share and Modify for the Security.

Rick
  • 1