Thursday, 22 August 2013

SQL Server remote backup query fails "cannot open the backup device"

SQL Server remote backup query fails "cannot open the backup device"

I am trying to configure my scheduled backups of a MSSQL database to a
mapped network drive, which is on a fileserver running Server 2003
Standard. Query reads like this:
BACKUP DATABASE XXXX
TO DISK = N'Z:\mssqlbk\XXXX.bak'
WITH CHECKSUM, STATS
Error message that is returned:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'Z:\mssqlbk\xxxx.bak'. Operating system error
3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Most solutions I find are thinking a permissions error for the SQL Service
account (Mine uses an account named SQLBro). This is a small workgroup
network, no AD. I have tried the following:
Verified the user account on database server being used by the process
during backup with Procmon: SQLBro
Created local account on fileserver SQLBro, matching password with account
on db server
Added permissions for SQLBro account on fileserver to the root volume and
target folder for backup on fileserver.
Tried to add the long SQL user from the default backup location to the
fileserver, but the name was too long for standard add local user
interface (workaround?).
Tried running service on db server with local system account instead of
SQLBro.
Still same error each time. I am running out of ideas and looking for
workarounds now. Could backing up locally and then running a robocopy or
xcopy to the mapped drive be a kludgy way to do this?

No comments:

Post a Comment