Sql-server – Maintenance Plan fails but the query it generates Runs

backupmaintenancesql serversql-server-2005

I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error:

backup MYSERVER (MYSERVER)
Backup Database on MYSERVER
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Differential
Append existing
Task start: 2011-10-18T00:10:09.
Task end: 2011-10-18T00:10:09.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Cannot open backup device 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Arca\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But the query that this maintenance plan generates:

BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10

runs normally.

even stranger is the error message: "Cannot open backup device 'C:\Program Files(…)" maybe there's a difference between how SSIS and SSMS/SS Agent handles the backslash?

The user for SQL Agent and the user with which I ran this query successfully in all these cases was a domain user "ABC.MyDomainUser" that has permissions to access the network mapping \myNetworkDrive. I even used MSTSC to log in the server that runs SQL Server and ran the query locally, it runs fine, only fails when in the maintenance plan.
Is this a bug? What am I missing here? What is the elegant way to backup to a network location?
Thanks in advance,
Lynx Kepler

Best Answer

The SQL Server Agent account has both share and local security permissions right?

I would also ensure that you have given the SQL Server Agent appropriate network permissions -- it really sounds like a permission issue.

Related Question