Varun Dhawan's Blog

Backup SQL Server Database to a network shared drive

Hello All,

You have a situation wherein you want to backup SQL Server database to a network mapped drive. So all you thought of doing is mapping the drive from Operating System with a Drive Letter. Now you tried taking the backup from SSMS backup GUI, however you cannot view the network mapped drive.

As per SQL Server Books-On-Line:

“For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running”

So to backup your database to a network mapped drive, you need to follow below steps:

Prerequisite: The steps are applicable on machines running under “Domain Account”.

Step # 1. Map the network drive:
EXEC xp_cmdshell 'net use <drivename> <share name>'

Ex: EXEC xp_cmdshell 'net use H: \\machinename\sharename'

Step # 2. Verify drive mapping:
EXEC xp_cmdshell 'Dir H:'

Step # 3. Delete the network map drive
EXEC xp_cmdshell 'net use H: /delete'

Once done, you will be able to view the network mapped drive from Backup GUI.

How to make 'network drive mapping' permanent across SQL Server reboot:

Option 1. – Using Backup Device

- After completing Step # 1. and Step # 2. Create a “Backup Device” for above network mapped drive. For details refer >> How to: Define a Logical Backup Device for a Disk File

- Once “Backup Device” is created, network mapped drive will be visible across SQL Server reboot.

Option 2. – Using “start-up” Stored Procedure

Step 1. Create a Procedure

CREATE PROC map_drive_satrtup
As 
EXEC xp_cmdshell 'net use <drivename> <share name>' 

Step 2.  Set Procedure Options

sp_procoption  @ProcName = 'map_drive_satrtup' 
, @OptionName = 'startup' 
, @OptionValue = 'on' 

Getting ERROR????

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure.

Indicates that you need to enabled xp_cmdshell. Use below command:

sp_configure 'xp_cmdshell',1; 
Go 
RECONFIGURE WITH OVERRIDE; 
Go

Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.