Mapping network drive to SSMS

Question

Mapping network drive to SSMS

pols

SSC Eights!

Points: 935
More actions
May 18, 2022 at 4:27 pm
Go to Answer
#4035077

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 <DIR> 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 perticular 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
Ed B

Hall of Fame

Points: 3669
More actions
May 18, 2022 at 5:03 pm
Answer
#4035120
Edit: What they said.
It may be that the drive is mapped for your user, but not the SQL Service account, or whatever account the GUI uses.
Have you tried writing a script rather than using the GUI? either using the mapped drive or the UNC Path.
```
BACKUP DATABASE DBName TO DISK = '\\SERVER\PATH\dbname.bak'
BACKUP DATABASE DBName TO DISK = 'Z:\backups\dbname\dnname.bak'
```
Do you need to backup the database to two drives? or create multiple files split between the two drives?
```
BACKUP DATABASE DBName TO DISK = 'Z:\backups\dnname1.bak', 'Y:\backups\dbname2.bak'
```
This is an example from Adventure works with some of the options I did not include.
```
BACKUP DATABASE [AdventureWorks2017] TO  
DISK = N'C:\Backups\AdventureWorks2017_Backup_1.bak',  
DISK = N'C:\Backups\AdventureWorks2017_Backup_2.bak',  
DISK = N'C:\Backups\AdventureWorks2017_Backup_3.bak'
WITH NOFORMAT, NOINIT,  
NAME = N'AdventureWorks2017-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
```
- This reply was modified 2 years, 7 months ago by Ed B.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WHug Ten Centuries Points: 1248 More actions · Answer 1

As a hunch, the session created by xp_cmdshell is not persistent. If you want to use mapped drives, create those directly in Windows. Instead of mapping a drive, try using the full path in the backup statement. The service account or machine account if a VSA (myserver$) needs permission on the Backupserver folder to succeed.

Backup database MyDB to disk '\\Backupserver\myDB.bak'

--Will

pols SSC Eights! Points: 935 More actions · Answer 2

Hi Ed B,

Thanks Allot. As per your suggestion it worked with command.

Somehow I missed the small logic to try with command. 🙂