INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Backup To UNC / Mapped Drive

Backup To UNC / Mapped Drive

(OP)
Bit of an old chestnut I know but I can't solve this one.

I can issue a BACKUP command from the query window to the local C drive and it backs up the database.
I cannot backup to a mapped network drive.

The SQL Server Agent account is 'LocalSystem' which I assume will not have the authorities to write to a mapped drive.
However I am running the backup from a query window in SQL Server Management Studio from an account that is windows authenticated sysadmin.
The windows account can access the mapped drive in my windows session and copy files with windows explorer but for some reason my
SQL Server Management Studio will not do it.

I figured I need to run the backup command as a T-SQL step in an SQL agent job - with a 'Run AS' value of a proxy account.
I have set up a credential and a proxy to the same domain windows account that can manipulate data on the mapped drive.
However the T-SQL step won't let me select the proxy account to run the step!
At first I though I had not set the credential and proxy up properly but I can select it on steps for an SSIS package, just not a Y-SQL step.
I don't understand why.

So my questions are:-
Why Can't I run the backup command from a query window to a UNC location even when I have the windows authorities to the mapped drive location?
Why do T-SQL steps not allow you to select a 'Run As' proxy?
Is there anything else I can do to achieve a backup to a mapped drive from inside SQL Server without changing the Agent Service account?





Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

RE: Backup To UNC / Mapped Drive

correct thing is to give the sql server agent user permissions to the network drive you need to write the backups to.

if your DBA allows cmdshell(which I would on a very restrictive way) you may be able to do it through a cmd file - but this will require a few other things and a good DBA will not allow it.

others may reply to you on the other questions

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Backup To UNC / Mapped Drive

The reason you can not do the backup is that while domain\user is issuing the command to SQL Server, it is localsystem that is trying to carry out that command. Think of it in terms of office delegation. You may have rights to the server room, but the file clerk does not. If you tell the file clerk to do something in the server room, he will not be able to comply. The same thing is happening here.

RE: Backup To UNC / Mapped Drive

(OP)
OK thanks chaps.

I will change the agent account and assign permissions.


Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close