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

   

• WHug

  Ten Centuries

  Points: 1248

  More actions

  May 18, 2022 at 5:01 pm

  #4035119

  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

• Ed B

  Hall of Fame

  Points: 3685

  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 3 years, 1 month ago by Ed B.

• pols

  SSC Eights!

  Points: 935

  More actions

  May 18, 2022 at 5:28 pm

  #4035131

  Hi Ed B,

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

  Somehow I missed the small logic to try with command.