I am having trouble trying to get MSSQL to backup to a network drive. This is the setup:
+ MSSQL on Windows Server 2003 logged in under Win Auth.
+ Shared drive on Vista desktop PC (although same issue happens on XP).
+ Server and PC are in the same workgroup (no domain only workgroup).
+ PC allows everyone full control to share.
+ Drive and folder can be navigated to through Windows Explorer without the need for a password ("Password protected sharing" is turned off).
The error I am getting is:
Cannot Open Backup Device 'path_to_file'. Operating system error 5(error not found).
Script being used:
BACKUP DATABASE [MyDB]
TO DISK = N'\\path_to_file\MyDBBackUp.BAK'
WITH NOFORMAT, INIT,
NAME = 'Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
As the network is not on a domain, I can not explicity give the server user permission. None the less, the drive can be viewed/modified through Windows Explorer.
I've just noticed that while typing this post that the "MSSQLSERVER" service is running as "Local system account". Is it therefore likely that this local account does not have access to external resources? Should I be running the service with a non local account, for example an account that has Admin permissions?