0

We need to backup some database to an unc path, the problem it's that it's a SQL Server instance that has to execute with SQL Server autentication (sa user).

How can we configure the plan task in SQL Server to make it able to have access to the UNC folder? I attach the state of our SQL Server Services in SQL Server Configuration Manager.

Thanks in advance and regards,

Pedro Sánchez.

enter image description here

Improve this question
4
  • Are you using UNC path to copy the backups over Network on to other server? If yes does that server has SQL installed?
    – KASQLDBA
    Commented Mar 4, 2015 at 15:35
  • Hi Kapil, the source server, evidently have installed the SQL, in fact it's when we have the BBDD that have to be copied. And really, the remote destination it's a NAS where we have prepare a shared folder. Thanks and regards.
    – Pedro Sánchez
    Commented Mar 4, 2015 at 15:44
  • 2
    Can you not just use the full network path? Why does it have to be mapped to a drive? We do something similar here but i just backup to \\backups\prod and it's fine.
    – Kris Gruttemeyer
    Commented Mar 4, 2015 at 17:43
  • Hi Kris, we've tried using the entire UNC path but the incident persists.
    – Pedro Sánchez
    Commented Mar 5, 2015 at 15:17

1 Answer 1

Reset to default
3

You can map a local drive letter to your UNC folder using xp_cmdshell like: exec xp_cmdshell 'net use X: \MyServer\MySharedFolder\'

Then you can issue your backup statement to use the X: drive as if it was local.

You may have to run sp_configure to enable xp_cmdshell.

Improve this answer
4
  • 1
    Just adding to the already correct answer, here's a step by step of how to do just that: mssqltips.com/sqlservertip/3499/…
    – Kris Gruttemeyer
    Commented Mar 4, 2015 at 17:34
  • 2
    Also worth mentioning that enabling xp_cmdshell poses a security risk so use caution.
    – Kris Gruttemeyer
    Commented Mar 4, 2015 at 17:42
  • Hi Queue, as Kris mentioned it's a high risk for the server security enable the xp_cmdshell :(
    – Pedro Sánchez
    Commented Mar 5, 2015 at 15:19
  • Yes it is. The Surface Area Configuration will usually have it disabled by default. That sort of ties into the whole sa account because whoever has sysadmin privileges on your instance has, in effect, the same domain privileges as the Windows account that the SQL Server service runs under.
    – Queue Mann
    Commented Mar 6, 2015 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.