February 6, 2014 at 4:09 pm
Hello all,
I am having trouble switching backups from local disk to a network share. I know this is not an ideal situation, but I am running out of local disk space and admins are offering up this space.
I have mapped the network share on my server as Z:\backup. When remoting into my database server with the service account I have running SQL Agent I can create, edit and save a file on the backup share. When I run a backup from SSMS, BACKUP DATABASE Test TO DISK = 'Z:\backup\Test.bak', it fails with the error, "Cannot open backup device 'Z:\backup\Test.bak'. Operating system error 3(The system cannot find the path specified.)."
I've logged off and back on. The mapped drive shows up, but I still get the error. Do I need to restart SQL here?
Thanks,
Tim
February 6, 2014 at 7:40 pm
I can't recall off hand if it's a bug within SQL or not, but it will work if you use the fully qualified domain name that the share is mapped to. Instead of "Z" use the underlying network path: Ex: \\Server\share\folder or \\Server\d$\share\folder
You could also be getting this error due to incorrect permissions. Sounds like the service account has access to that location, but you don't.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 6, 2014 at 9:12 pm
I agree with the change to use the UNC instead of a mapped drive.
Are you running SSMS as the agent service account (i see you RDP'd to the box using the service account for SQL Agent)?
Can you browse to the directory create and delete a test file (outside of ssms)?
Can you delete the test backup that was created there?
Can you create a backup with a different name instead of trying to overwrite the one that is there?
If you can create a new backup, but not overwrite or delete one - then you have a permissions issue or the file is locked by another process (san dedupe or AV).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 7, 2014 at 12:11 pm
Thanks for the replies and suggestions.
I tried changing it to UNC, but the backup still failed. The error messages are slightly different
to disk = 'Z:\backup\...' Operating system error 3(The system cannot find the path specified.)
to disk = \\server\backup...' Operating system error 5(Access is denied.)
to disk = '\\server\Z$\backup...' Operating system error 65(Network access is denied.)
Yes, I'm running SSMS as the Agent Service account.
Yes, I can browse to the directory and create, edit and delete a file outside of SSMS while logged into the server as the Service account.
I was not able to create any backup from SSMS, so there is no test backup to delete.
If I change the path to a local disk, the backup works just fine.
This really isn't making sense to me. The service account has permissions and can create/edit files outside of SSMS, but not within.
Thanks,
Tim
February 7, 2014 at 12:48 pm
T_Peters (2/7/2014)
Thanks for the replies and suggestions.I tried changing it to UNC, but the backup still failed. The error messages are slightly different
to disk = 'Z:\backup\...' Operating system error 3(The system cannot find the path specified.)
to disk = \\server\backup...' Operating system error 5(Access is denied.)
to disk = '\\server\Z$\backup...' Operating system error 65(Network access is denied.)
Yes, I'm running SSMS as the Agent Service account.
Yes, I can browse to the directory and create, edit and delete a file outside of SSMS while logged into the server as the Service account.
I was not able to create any backup from SSMS, so there is no test backup to delete.
If I change the path to a local disk, the backup works just fine.
This really isn't making sense to me. The service account has permissions and can create/edit files outside of SSMS, but not within.
Thanks,
Tim
Just a few thoughts, in all likelyhood you already know everything in the text that I'm about to type, but I'm in a typing mood anyways!
If you are browsing to the server you want to back up to, are you browsing by first selecting the share in your "computer" folder / pane in explorer? If so, then you might be using a drive map, which is most likely not visible from the service account during its non interactive login. If you are opening an explorer window by typing \\server\actual_share_name in the location thing, then you are getting there correctly and you can disregard my post!
It could be that you aren't specifying the correct path in your backup command. You won't see the Z: from ssms backup command because your service account may not have a drive mapped in its noninteractive environment. You won't backup successfully (in my estimation) to \\server\z$\anything because typically z$ is shared only to administrators (if that, z$ is a share name, its just conveniently created by default to equal the drive letter minus the colon plus the dollar and by default, only shared to administrators which your service account will most likely NOT be one).
edit: (corrections and additions)
you can check out the share names of the target computer by typing at a command prompt:
net view \\servername
This will NOT display share names terminated with the dollar sign ($).
February 7, 2014 at 1:11 pm
Is the service account a member of the local admin group on the server its running on?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2014 at 1:30 pm
When I'm browsing to the backup destination I open an explorer window and type \\server\backup\...
When I ran net view \\server from a command prompt it returned (in a somewhat different format)
Share name: backup
Type: Disk
Used as: Z:
Yes, my service account is in the Administrators group on my SQL server.
Thanks!
Tim
February 7, 2014 at 2:02 pm
Something you might consider, make a directory on that share while logged in with that agent userid, then try to backup to a file within that directory.
Sometimes I like to try something a bit different in the hopes of uncovering more info.
edit: also just to be sure, check to make sure that your sql server account and your sql server agent acount are the same account, and that they're truly network / ad accounts and not accounts just local to that server, I'm mentioning this because I have seen computers where the service accounts were just local system accounts with no network access.
February 7, 2014 at 3:12 pm
Thanks everyone, I think I have this worked out. The SQL Agent was running under a service account that had permissions, but the engine wasn't. When I changed this on a Dev server it worked with the UNC path just fine.
Tim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply