current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 3 down vote favorite

Another simple SQL backup question from me....

I've got a SQL Server database running on SSMS 2008 and am trying to configure some backup jobs. I am now seemingly fine with backing up and restoring the databases but have been asked to backup from one of our servers to another's C$, though am having trouble doing so. I've got the line:

backup database MyDB to disk = '\\NetworkLocation\C$\Backups\backup.bak'

And am being given the error:

Cannot open backup device '\\NetworkLocation\C$\Backups\backup.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105).

Which (if I'm correct) is an access denied sort of error.

Anyways.... I was told to 'just set permissions to C$' but haven't been able to find out how to, or even if that's possible. I have got it working such that the server will back up to a shared folder on the second machine if one's set up but the boss has asked, specifically, if it can be done straight onto C$. Am I just going about it the wrong way? Many thanks

share|improve this question
5  
"the boss has asked, specifically, if it can be done straight onto C$" -- say no, and back up to a network share. This is such a bad idea, I don't even know where to start. –  Jon Seigel Nov 19 '12 at 14:57
add comment

2 Answers

up vote 11 down vote accepted

Technically you can do it directly to \\NetworkLocation\C$ like your boss wants, but the c$ share is one of the windows Adminstrative Shares ( http://en.wikipedia.org/wiki/Administrative_share ) and is only available to users in the Admistrators group. So to use it in this case you'd need to add the user account running your SQL Agent to the Administrators group on NetworkLocation

Using these shares like this is a really bad idea. It would be much better for example to share c:\Backups\ as \\NetworkLocation\Backups$ (the $ here indicates a hidden share, so it doesn't show up in the list when a user looks at the machine). As it's not an administrative share you can set the permissions however you want for any account, without giving them Administrator on NetworkLocation

The files are in exactly the same place and you've not compromised your security so all is good.

share|improve this answer
    
Thank you very much for an informed and concise answer. I currently am sharing the Backups folder, as you're suggesting, and using that to back up to (and am hoping I can convince the boss to stick with it). Again, many thanks –  cprlkleg Nov 19 '12 at 16:14
    
Indeed, especially since the error number, 5, means access denied. (Run 'net helpmsg 5' without the quotes to verify this.) –  Mark Allen Nov 19 '12 at 23:48
add comment
up vote 2 down vote

The account performing the backup on SQLServer has to have Administrator privileges on the FileServer to be able to access C$.

So if you login with the account YourDomain/YourAccount on the SQLServer then that account has to have Admin privileges on FileServer.

If you want to automate the task as an SQL Job, then please ensure that the SQLServerAgent Service is running with a domain account that also is Admin on both machines.

The following AdminShares are only available to Administrators of a local system (these can be domain accounts that are added to the Administrators group of a server):

  • IPC$
  • C$
  • Admin$

These automatic shares can be turned off with a registry setting.

I wouldn't recommend using these shares for backups, because if somebody turns of AdminShares in the Registry or via GPO then you've had it.

share|improve this answer
    
Thank youv very much for such a useful and clear answer, I'm still hoping to bring him around to using standard network shares. –  cprlkleg Nov 19 '12 at 15:29
add comment

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.