How to backup SQL Server databases to a mapped drive

Issue
When executing a backup on a SQL Server database where the resulting backup file is directed onto a mapped drive you receive the following error message:
"The system cannot find the path specified."

Product Line
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Cause
A network share that is mapped using a local drive letter will not be visible to a SQL Server instance as it is running as a service.
The SQL Server service runs in the environment of the local console with the security of the startup account of SQL Server applied.
Mapped drives are specific to a session and not visible to a service started on the local console.

Resolution
*Warning: The actions below should be performed by qualified personnel. Take backups of any files prior to modifying them. Ensure the backups are in a location that will not be overwritten (i.e. the Desktop).*

To backup a SQL Server database to a mapped drive using a local drive letter:

1. Run the following command from a query window  EXEC xp_cmdshell 'net use <drivename> <share name>'
Where:  <drive name>: Letter used to map the drive
<share name>: UNC path to the share

If xp_cmdshell is not enabled, you'll get a system error. To enable xp_cmdshell, run the following query:

-- 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', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO


If the mapping needs a user login, modify the netuse.  Open up command prompt window and use the following command:
net use <drivename> <share name> /user:username password

Example:  net use z: \\servername\folder /user:username password

2. You should be able to backup using the mapped drive letter, as the Management Studio Object Explorer drive window should now list the above mapped drive

Note:

A drawback of this resolution is that once the SQL Server service is restarted, the mapped drive will no longer be visible as it will become unmapped.
If you want to retain the mapped drive information then you need to create a startup procedure for executing the script in Step 1.
One simple method would be to create a backup device using the UNC path of the remote share that you would like to take the database backups on.
It is important to keep in mind that the SQL Server startup account needs to have full permissions on the remote share.

Publikováno pro:Schneider Electric Česká republika