Not able to see network drives in SSMS

SQL Server Administration
1

Hi,

I need to perform backup and save it in network drive. I have given all permissions to the backup drives and mapped in source server as well. I successfully executed below command in sql.

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'xp_cmdshell',1

GO

RECONFIGURE

GO

EXEC xp_cmdshell 'net use Z: \Backupserver\l'

EXEC xp_cmdshell 'net use Y: \Backupserver\m'

Also if I run below command i can see the output as well.

EXEC xp_cmdshell 'Dir Y:' (Means without any error i can see the disk details in output)

output

Volume in drive Y is Data2

Volume Serial Number is 902A-F0FE

NULL

Directory of Y:\

NULL

12/01/2021 09:42 PM Program Files (x86)

0 File(s) 0 bytes

1 Dir(s) 664,076,779,520 bytes free

NULL

I Refreshed the sql instance and tried backup the database in network drive. Though above all commands executed successfully why i am not able to see Z,Y disks while backup in SSMS.

I disconnected and re mapped disks, and tried with other letters. All possibilities I tried but not able to see network disks in GUI Backup window. As it is a Prod server i can not restart the instance.

Any particular reason why i am able to see disk details in output window by executing EXEC xp_cmdshell 'Dir Y:' but not visible in backup GUI ?

Please help me . I have only 5 hrs time to initiate the backup.

Thanks,

Jo

2

try running this

use master
go
BACKUP DATABASE db_one TO DISK = 'Y:\\db_one.bak'
go

I think you can only see drives attached to the server via SSMS and not mapped UNC paths

3

Instead of even trying to use a mapped drive - just use the UNC naming convention. If you have granted permissions to the share to the service account running SQL Server - then it will be able to access that share.

2 Likes
4

Hello,

I Have the same problem.
I use the SQL Server 2008 R2 and I mapped the drive in SQL with success, but when I tried to do a backup, the drive letter not appear.
You solved the problem?

Thanks

5

See @jeffw8713 's solution. Instead of using mapped drive letters, use the UNC path.