Backup SQL Server DB to a remote drive

Question

I am trying to backup a SQL Server database to another drive letter located on another network on another domain. The location has already been mapped to Z:\ and corresponds to \\10.98.76.6\Backups\. This is the code I have so far in an SQL Query:

BACKUP DATABASE [LabResultDb] TO  
    DISK = Z'\\LabResultDb_backup_12-09-2011.bak' 
    WITH NOFORMAT, NOINIT,  NAME = Z'LabResultDb-Full Database Backup', 
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10

But I get an error message...

Error msg

Cannot open backup device '\10.98.76.6\Backups'. Operating system error 1326(failed to retrieve text for this error. Reason: 15105).

Solution

Z'\LabResultDb_backup_12-09-2011.bak' - it's not a valid path anyway.

BACKUP DATABASE [LabResultDb] TO
DISK = '\10.98.76.6\Backups\LabResultDb_backup_12-09-2011.bak'

should work. Make sure SQL Server (SQL Server Agent) started with user account (not 'Local System') which have an access to \10.98.76.6\Backups\ share.

Comments

[duenni]

What if you use `\\10.98.76.6\Backups` in your query? I guess the drive is only mapped when a user is logged in, so the backup won't happen when no one is logged in...

[JFB]

The drive is mapped because I can see it in the file manager under computer. I have added an edit with the error message

[Sergey]

Operating system error 1326 = ERROR_LOGON_FAILURE (Logon failure: unknown user name or bad password). By default, the Microsoft SQL Server Service is running under the LocalSystem (or NetworkService account). Such accounts are unable to access the network share in question, therefore the backup task to remote location will fail.