Sql-server – SQL Server remote backup query fails “cannot open the backup device”

sql serversql-server-2008workgroup

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?

Best Answer

It could be that the SQL Server process doesn't know about the drive mapping. Did you map the drive as the SQL Service account?

I would suggest changing the drive letter path to the UNC path in your backup statement.

Related Topic