8

I have a SQL Server (2000, 2005 and 2008) and I'd like to use SQL Agent (or even a simple backup database 'xxx' to disk = 'yyy'), to backup to remote drives.

I.e: i have a mapped drive in the SQL machine, for example: "M:" which maps to \\otherbox\someshare

By default SQL Server won't allow you to backup to such drives, but I think that there's a way to enable that. Can somebody point me to the docs?

Thanks in advance.

Martin Marconcini
  • 307
  • 1
  • 5
  • 13
  • 1
    You can simply use a third-party tool, like SQLBackupAndFTP to backup your SQL Server remotely? https://sqlbackupandftp.com/blog/how-to-backup-remote-sql-server-database – Nick Taylor Mar 13 '18 at 13:58

6 Answers6

8

Use the UNC path when specifying the destination-- the SQL Agent doesn't have a concept of "mapped" "drives".

Also, SQL Agent typically runs as "Local Service" or "Local System" and, as such, doesn't have rights to remote shares on other computers.

You have a couple of choices:

  • Run SQL Agent as a role account in the domain. Grant that account permission to write to the directory / share where you'd like the backups stored.

  • Run SQL Agent as "Network Service". It will authenticate to the sharing server with the domain computer account of the machine the service is running on. Grant that account permission to write to the directory / share where you'd like the backup stored.

  • If you don't have a domain, create an account with the same username and password on both the machine hosting SQL Agent and the machine hosting the backup files. Change SQL Agent to run as this "role" account, and grant that account permission to write to the directory / share where you'd like the backup stored. (The "poor man's domain"...)

Evan Anderson
  • 141,071
  • 19
  • 191
  • 328
  • In addition to mentioning the UNC paths, I'd recommend the user actually CONNECT to the UNC path. The quick/easy way to do this is to just pop open a web browser and type it in. You'll be prompted for a username/password and can usually say you want to REMEMBER that user/pass for the future. I've seen too many people who were 99% of the way there and couldn't figure out why things didn't work. TEST YOUR CONNECTION! LOL! :-) – KPWINC Jul 13 '09 at 15:49
  • That's what I was looking for (The "poor man's domain" part) ;) Thanks! – Martin Marconcini Jul 13 '09 at 16:24
4

I totally agree with both answers about UNC path.

I would also like to add that even with mapped drives you have a simple workaround. You can execute a backup to any of normal drives of your server. And then you can add

xp_cmdshell 'XCOPY [source] [destination] \flags'

SQL command to the job or SQL script you run.

With xp_cmdshell you can do even more - for example run an external archive command line tool, like 7z to compress the file before you will copy it to the mapped drive (when remote connection is too slow...)

P.S.: Forgot to mention that xp_cmdshell can be enabled and disabled by using the Surface Area Configuration tool and by executing sp_configure (and by default it is disabled)

splattne
  • 28,348
  • 19
  • 97
  • 147
Bogdan_Ch
  • 483
  • 1
  • 3
  • 12
  • The ROBOCOPY utility from the Win2003 Resource Kit provides some extra options over XCOPY, that are useful in this type of operation. In my case, the ability to no overwrite backups previously copied, or mirror two backup directories is easily implemented without extra scripting. – kermatt Dec 29 '09 at 17:48
2

If SQL Server doesn't run under a domain account, you can map a network drive for sqlserver account(not your account) as per described in this stackoverflow answer

First you need to enable xp_cmdshell

-- allow changes to advanced options 
EXEC sp_configure 'show advanced options', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO
-- To enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Update currently configured values for advanced options.
RECONFIGURE
GO

Then you you can map a drive using:

EXEC xp_cmdshell 'NET USE Z: \\Srv\Path password1 /USER:Domain\UserName'

Finally you can backup to that mapped drive:

BACKUP DATABASE myDB TO DISK = 'z:\file.bak'
Yepeekai
  • 121
  • 2
1

Your agent needs access to the network shares. They don't need to be mapped beforehand.

You do it like this:

BACKUP DATABASE myDB TO DISK = '\\machine\share\dir\file.bak'

I believe that if the user that owns the job is a sql sysadmin, it runs under the agent, otherwise it runs as the non-sysadmin user.

Sam
  • 1,990
  • 1
  • 14
  • 21
0

The most easy way is to create a .vhd drive on a network share through disk management and assign a drive letter to it. SQL can access this drive without any modification.

Just add a diskpart.exe script to scheduler on boot in order to auto attach on restart.

example select vdisk file="\{ip address/server}{networkshare}{filenamep.vhd}" attach vdisk // remembers the last drive letter assigned to.

Kimon
  • 1
0

Keep in mind here that SQL Server is very intolerant of network delays. If they occur, and they tend to, the backup will fail. I do not recommend this practice at all for production environments.

Better to back up locally and then copy.

Steve Jones
  • 795
  • 5
  • 8