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?