Backup SQL Server Database to a network shared drive

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

Step # 1. Map the network drive: EXEC xp_cmdshell 'net use '

Example: EXEC XP_CMDSHELL 'net use H: \\machinename\sharename'

Step # 2. Verify drive mapping:

Example: EXEC XP_CMDSHELL 'Dir H:'

Once done, you will be able to view the network mapped drive from Backup/Restore GUI.
Step # 3 (Optional). Delete the network map drive

Example: EXEC XP_CMDSHELL 'net use H: /delete'

NOTE: Only flipside is that this network drive mapping will remain till next SQL Server Service restart. So, To make above 'Network Drive mapping’ Permanent follow either of below options
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  ' 

Step 2.  Set Procedure Options

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

 

While using XP_CMDSHELL option, if you get below 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.

The above error clearly indicates that you need to ENABLE xp_cmdshell. Use below command:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO