Bkup to Mapped Drive Fails
-
Hello gentlemen & ladies,
I have an SQL7.0 sp4 install on NT4 sp6a box. I have created a maintenance plan to write a backup to disk. The disk is a mapped drive pointed to a remote machine but the scheduled backups always fail. Heres the eventlog error;
18204 :
BackupDiskFile::CreateMedia: Backup device
'F:\SQLBkups\BAKs\master_tlog_200503241000.BAK' failed to create. Operating system error =3D 3(The system cannot find the path specified.).
I have almost the same setup running on a different box that never fails.
Now I can start the backup job from Enterprise Manager on the local box manually and it will work, it will write the backup file to the mapped drive. If I try the same thing on a remote Enterprise Manager, the job will fail with the above error.
Almost sounds like security but how? Or should I say why?
thx
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer -
Does the account that you are using for the scheduled backup (i.e. the userid that the job will RunAS) have rights to get to your F:\ drive?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
What user id is the backup job running under?
What user id is the the SQL Server Agent running under?
My thinking/things to check:
- The manually created backup job picked the first user in the list of user ids which is a generic user instead of the sa or domain admin user.
- The SQL Server Agent account is using LocalSystem as opposed to a domain user account.
- The SQL Server Agent domain user account is not a domain admin account, and therefore needs user/group permission to the mapped folder.
- The mapped drive is using a different userid than the SQL Server Agent user account
- From the query analyzer try EXEC master.dbo.xp_fixeddrives and see if the drive letter shows up.
Just some suggestions.
Good luck and let us know.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Thx for the suggestions, I'm afraid I may need more of them though...lol
The SQL agent uses a domain account I setup for just this kind of needed access. I know it has the required access because it is used by other services as well. And they are working perfectly.
Ok, the mapped drive does not show up using EXEC master.dbo.xp_fixeddrives
So what exactly does this mean? And how would I fix it? Your check will be in the mail in the morning...lol
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer -
Ok, the mapped drive does not show up using EXEC master.dbo.xp_fixeddrives
This means that the the SQL server service/agent isn't recognizing the the drives existence.
From a command prompt type NET USE. Does it show the mapped drive? You might want to delete the mapping and recreate it. You can do it from the command line using NET USE /?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Yes, the mapped drive shows up using NET USE and I have dropped & re-created this map a few times already. I even tried using different drive letters.
By the way, why should a mapped drive be reported by a command like xp_fixeddrives? Is there a xp_mappeddrives?
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer -
You are right about xp_fixeddrives. It's an undocumented xp. I thought it would show the drive. Dagnabit!
From a query analyzer window can you try
Backup database master
TO DISK = 'F:\SQLBkups\BAKs\master_test.BAK'
Note that none of the system databases (master, msdb, or tempdb) can do transaction log backups because they are in simple recovery mode (i.e. trunc. log on chkpt.) So you need to do separate maint plans unless all your databases have the "trunc. log on chkpt." feature set.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Yep!
Backup database master
TO DISK = 'F:\SQLBkups\BAKs\master_test.BAK'
this worked locally & remotely but the job is still failing.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer -
Note that none of the system databases (master, msdb, or tempdb) can do transaction log backups because they are in simple recovery mode (i.e. trunc. log on chkpt.) So you need to do separate maint plans unless all your databases have the "trunc. log on chkpt." feature set.
This "'F:\SQLBkups\BAKs\master_tlog_200503241000.BAK' failed to create." looks like it was doing a transaction log backup.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Just because the account you created has rights to the mapped drive, doesn't mean that the account can see the mapped drive. If you mapped the drive while logged in as you, the SQL account won't be able to access it. It must be mapped under the SQL id's account.
To avoid this problem, use the UNC path. From SQL 7.0 Books Online -
If the backup is to be performed over the network to a disk on a remote computer, use the universal naming convention name (UNC) in the form \\Servername\Sharename\Path\File to specify the location of the file. As with writing files to the local hard disk, the appropriate permissions needed to read or write to the file on the remote disk must be granted to the user account used by SQL Server.
Steve
-
Enthusiast don't sweat the "tlog" that was my fault I typed it in by mistake.
I've put the UNC path in the plan so I'll let you know how that goes.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer -
Drat! Too late seeing the message! UNC should take care of you. I found the same thing in previous versions. I don't think the OS/SQL "sees" mapped drives, it only sees local drives. Look at it this way: I'm at my workstation running Enterprise Manager and I tell it to back up. It goes to a drive that is local to the server. If I want it to go elsewhere, UNC is about the only way that I can see to tell it where to go.
I'd like to hear a more detailed explanation, but I think this is the reality.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
That did the trick guys! Thx for all your help!
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply