Backup and Restore SQL Server Database to a network shared drive

  • Článek

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.

Comments

  • Anonymous
    June 14, 2009
    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 = '\servernameBackup$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:databackups” /m “filename_*.bak” /c “cmd /c del /Q ~path” /d -30

  • Anonymous
    July 20, 2009
    Thanks Steven, Yes! I Tried it. It's a good and better option.

  • Anonymous
    September 10, 2009
    Thanks for the code Steven. One change to the forfiles command. Forfiles /p “f:databackups” /m “filename_*.bak” /c “cmd /c del /Q @path” /d -30