2

My SQL Server 2008 R2 has a mapped network drive (called Y:/) which reconnects after a reboot (I just checked this).

When I try to backup a Database (TASKS -> BACKUP). The network drive Y: is not listed as a possible destination. When I try to define the Backup TO: Disk destination location.

Can someone suggest why this is? What other solutions might I have?

Joe
  • 853
  • 1
  • 7
  • 20
Pure.Krome
  • 6,508
  • 18
  • 73
  • 87

2 Answers2

7

You should use a UNC to the directory to which you want to backup. ex: \\servername\share

Since mapped drives are only created on an interactive logon, if you schedule a backup to a mapped drive, the service account will not have access to it and fail. I can only presume this is why Microsoft excludes them from this dialog.

Joe
  • 853
  • 1
  • 7
  • 20
  • A very common problem with server processes and networked drives, permissions. Comes up more often when people are trying to setup FTP servers to some NAS, same issue though. – Orbling Apr 07 '11 at 01:42
  • AHHH - good point. What about iSCSI? – Pure.Krome Apr 07 '11 at 02:18
  • backups work great using the UNC path - I've had it running like a champ in the past. It's a PITA to get it working, but it does work. – tsykoduk Apr 07 '11 at 06:37
  • iSCSI LUNs (aka drive letters) do not depend on interactive logons and are available when the system is booted, before anyone logs in. – jftuga Apr 07 '11 at 14:30
  • am getting "Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.Sdk.Sfc)" error when I try the UNC path. What am i supposed to do to rectify this. The computers are not on a domain. – Shadrack Orina Aug 24 '12 at 09:08
1

You SHOULD use UNC path when possible, but if you need to authenticate with different credentials (You still don't need to assign a drive letter though) or maybe use some third party network share that does not use UNC paths, you can use xp_cmdshell to issue NET USE commands or whatever the third party share uses. Then the share will be available to the SQL Server process.

Be aware that xp_cmdshell is commonly frowned upon in security audits and the like.

Michael Eklöf
  • 519
  • 4
  • 6