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

  • Are you using UNC path to copy the backups over Network on to other server? If yes does that server has SQL installed? – KASQLDBA Mar 4 '15 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 Mar 4 '15 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 Mar 4 '15 at 17:43
  • Hi Kris, we've tried using the entire UNC path but the incident persists. – Pedro Sánchez Mar 5 '15 at 15:17
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 | |
  • 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 Mar 4 '15 at 17:34
  • 2
    Also worth mentioning that enabling xp_cmdshell poses a security risk so use caution. – Kris Gruttemeyer Mar 4 '15 at 17:42
  • Hi Queue, as Kris mentioned it's a high risk for the server security enable the xp_cmdshell :( – Pedro Sánchez Mar 5 '15 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 Mar 6 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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