Mapping network drive to SSMS

  • 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

     

  • 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

  • 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 1 year, 10 months ago by  Ed B.
  • Hi Ed B,

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

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

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

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