Ola Hallengren's SQL Backup to Network Location not working
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.
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.
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.)
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.