Varun Dhawan's Blog

Hands-on with SQL Server

Backup and Restore SQL Server Database to a network shared drive

Backup and Restore SQL Server Database to a network shared drive

  • Comments 3

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”

STEPS TO BACKUP DATABASE TO NETWORK MAPPED DRIVE:

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>'

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 <drivename> <share name>'

Step 2.  Set Procedure Options

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

Additionally…..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:

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.

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
  • here's another way of doing it...

    USE [master]

    GO

    -- drop the backup device from last time

    EXEC master.dbo.sp_dropdevice  @logicalname = N'Network_Share_Device'

    GO

    -- generate a backup device and file name

    DECLARE @backupfile_name VARCHAR(50)

    SET @backupfile_name = '\\servername\Backup$\dbname_'

       + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'

    -- create the backup device / filename

    EXEC master.dbo.sp_addumpdevice @devtype = N'disk',

       @logicalname = N'Network_Share_Device', @physicalname = @backupfile_name

    GO

    -- backup the database to the newly created backup device / filename

    BACKUP DATABASE [dbname] TO [Network_Share_Device] WITH DESCRIPTION =

       N'Backing up DBNAME to a network share drive', NOFORMAT, INIT, NAME =

       N'Backup_DB_BkUp_to_Network_Share', SKIP, NOREWIND, NOUNLOAD,

       STATS = 10, CHECKSUM

    GO

    Additionally on the remote server, set up a bat file job to delete old .bak files, with the following:

    Forfiles /p “f:\data\backups” /m “filename_*.bak” /c “cmd /c del /Q ~path” /d -30

  • Thanks Steven,

    Yes! I Tried it. It's a good and better option.

  • Thanks for the code Steven.

    One change to the forfiles command.

    Forfiles /p “f:\data\backups” /m “filename_*.bak” /c “cmd /c del /Q @path” /d -30

Page 1 of 1 (3 items)