I have a server running SQL 2008. I'd like to back up my primary database to a folder that is somewhere else on the network. After having tried a few different ways, I always get the same error.
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'Z:\My_Backup_Wednesday.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
However, the Z:\ drive is mapped properly & I can read from or write to it.
What is the accepted practice on this? If I had more free room I would export to a temp folder & move the file with a batch script, but I need ~100gb and don't have it to spare.
You could specify the UNC of the target drive instead of mapping a drive letter. You would still need to make sure that the SQL Server service account had access to the location, as Bitsmed has pointed out. The fact that your account can read and write to the location is irrelevant. All backups are done under the security context of the SQL Server service account.
it might be easier if you have shared the folder out where the backup will go, and ensure SQL service account have write access to it, then backup providing the full UNC path, it should be easier to manage then map drives.
As others have stated, the Z: drive is only available to your login. It doesn't even exist so far as SQL Server goes because neither the login for the SQL Server service nor the SQL Server Agent service has such a mapping. You need to use the full UNC path to do this and the service logins for SQL Server need to have read/write/modify privs there.
You CAN trick it, though. If you use xp_CmdShell to map the drive, THEN SQL Server will be able to use it as the Z: drive or whatever drive you pick.
I've changed the command to the UNC, but still I'm getting the same issue. Looking at my server, the instance is running as "Local System", which I am told should have the same permissions as the file explorer which can access the files.
On the opposite end, the backup computer has zero security. It allows full control to any user that connects to it. Which is pretty dumb, but it makes the point for testing.
If you are running under "Local System" then wouldn't that account be restricted to local resources? How would the external resource be able to authenticate and authorize your request? Would you be able to run the SQL Service under an account that had access to the external drive?
