Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
what build number is the your instance at? I have seen a few bugs with SQL 2005 previous to SP3 when working with maintenance plans. –  Shawn Melton Oct 26 '11 at 12:06
    
You've got it: Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 6.0 (Build 6002: Service Pack 2) –  Lynx Kepler Oct 27 '11 at 12:41
    
You might try to do a RESTORE FILELISTONLY FROM DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' to see if the backup file is at least readable by the instance of SQL. –  Shawn Melton Oct 27 '11 at 15:27
    
The first part of the UNC path has to be the server name, which it seems you've removed for privacy. Are you sure the actual UNC path is pointing off the server? –  Jon Seigel May 11 '13 at 12:40

1 Answer 1

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.

share|improve this answer
    
The permissions are alright, the strange, it seems, is the error: "Cannot open backup device 'C:\Program Files(...)". –  Lynx Kepler Oct 27 '11 at 12:43
    
Just curious on how this turned out, please update! –  RMacy Dec 7 '11 at 1:57
    
I gave up trying to use maintenance plan jobs to run backups and instead the company adopted a microsoft backup tool. Probably the C:\Program Files needs permissions that the user didn't/couldn't have. –  Lynx Kepler May 13 '13 at 17:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.