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
Best Answer
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 NetworkLocationUsing 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 NetworkLocationThe files are in exactly the same place and you've not compromised your security so all is good.