Scenario
You ar trying to backup a sqlserver db on mapped network drive. You have already mapped the drive in Windows and you can see that drive in Windows Explorer. You are not able to see the drive which is mapped when you open the backup procedure using SqlServer Managment studio.
Solution
First of all you not need the previous mapped network drive created by Explorer. You have to create this drive using SqlServer, and you’ll not be able to see it using Exploer.
Enable xp_cmdshell
You need to execute below to enable xp_cmdshell as its disabled by default due to security reasons. (Please turn off again once you done with the work)
Using SSMS execute thesse commands :
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO
EXEC sp_configure 'xp_cmdshell',1 GO RECONFIGURE GO
After this you ‘ll have a positive reaction a this command that using SSMS you should use for testing previous operations :
EXEC XP_CMDSHELL 'Dir C:'
Map Network Drive
To map network drive you have to use the same command that you should use over Windows ysig command prompt :
'net use Z: \\networkShare\Test'
So, using SSMS you have to run the command
EXEC XP_CMDSHELL 'net use Z: \\networkShare\Test'
Now you should test this connection with the command
EXEC XP_CMDSHELL 'Dir Z:'
but, the most important goal, is tha you’ll be able to see teh drive Z during backup proceure over SSMS
Map Network Drive cmd – net use user password
The above command will work and completes successfully without asking the user to provide a username/password if the user has authorized access to this network share. If not, But the easy way is to use the “net use” command on the command prompt line explained above.
net use Z: \\networkShare\Test /u:domainname\username password
So, using SSMS you have to run the command
EXEC XP_CMDSHELL 'net use Z: \\networkShare\Test /u:domainname\username password'
