I've tried to do a backup of some databases from SQL Server 2000 on network share on File server under MS Windows Server 2003. I've changed the account for MS SQL Server Agent to be a domain user account, added the network device with sp_addumpdevice stored proc., put it in built-in Sysadmin group, gave all necessary permissions according to BOL and MSDN and got nothing at all!
Does anybody know the solution for this problem?
I used a mapped network drive from File Server as drive F: on SQL Server machine, then added that mapped drive F: as Backup network device using stored procedure sp_addumpdevice. After that, from Enterprise Manager I tried to backup database Master using backup device 'F':.
I've got error message MS SQL-DMO (ODBC SQL State: 42000) - Cannot open backup device 'asnetwork'. Device error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormaly.
In error log I've found next:
BackupDiskFile::Create Media:Backup device 'F:\asbckp.bak' failed to create. Operating system error = 5 (error not fpund).
I've also tried to manage without network mappings, using simple file path to the network share on File Server as a backup device, but with similar results.
Try using the UNC path instead as the F mapped drive will not be visible to SQL Agent only the logged in user at the time the map is created.
I know it's possible because someone ask me for the same thing in a long time.
With SLQ server 2000 I've enabled a specific option...but I'm sorry I don't remember the syntax of the command.
I think it was something like : "trace flag" + a specific number.
Did you look in Microsoft KB ? I've found that :KB827452