SQL SERVER – Backup on mapped drive failing with error – Error 3201, Level 16, State 1

We are so used to taking backups and it can sometimes give us new experiences. One of the common messages I see are around taking backups on a share. If you have found this blog while searching for error message, then you may want to look at below blogs as well if you are NOT dealing with mapped drive. Let us learn about Error 3201.

SQL SERVER – Error: Fix: Msg 5133, Level 16, State 1, Line 2 Directory lookup for the file failed with the operating system error 2(The system cannot find the file specified.) – Part 2

This time I will explain why you are getting this error while taking backup on a mapped drive. Here is the situation:

  1. SQL Server Service runs under a domain account.
  2. You have already mapped the drive in Windows and can see that drive in Windows Explorer.
  3. You are not able to see the drive which is mapped. Below is the screenshot of the screen where you choose a backup file name. If you try to take a backup from SQL Server Management Studio (SSMS) you are not able to see the drive. As we can see, SSMS is only showing local drive (C and D)
  1. Backup command is failing with below error.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘Z:\master.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

If we try to get details about the drives visible from SQL Server by Windows “dir” command then we see below.

xp_cmdshell 'dir Z:'

Output
———————————————–
The system cannot find the path specified.
NULL
(2 row(s) affected)

Above confirms that SQL is not able to see Z drive, even though it is mapped on Windows. In case you get below error

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 72
The configuration option ‘xp_cmdshell’ does not exist, or it may be an advanced option.

Then 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)

EXEC sp_configure 'advanced', 1
RECONFIGURE WITH override
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH override
GO

We are seeing “The system cannot find the path specified.” because SQL Server instance as it is running as a service. To fix the error, we need to map the drive via SQL Server. Here are the steps.

-- Map the drive via T-SQL so that SQL can see it.
EXEC xp_cmdshell 'net use Z: \\BigPinal\SharedFolder'
GO

 

Now we can check mapped drive via T-SQL.

xp_cmdshell 'dir Z:'

You may want to add more parameters for net use. Refer Net use documentation

Once this is done, we can see that drive in SSMS as shown below

… And as expected, backup command would not fail.

Have you ever faced this situation such weird situations? What were your resolution steps? Let me know as we can learn from each other.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Marking Filegroup as ReadOnly with SQL Server
Next Post
SQL SERVER – Adding a Master Data Services to your Enterprise – Notes from the Field #072

Related Posts

16 Comments. Leave new

  • — unMap the drive via T-SQL
    EXEC xp_cmdshell ‘net use Z: /D’
    GO

    Reply
  • Hi Pinal,
    tank you for the great article
    In my Company drive mappings not allowed on Server.
    Because of this i do the SQL Backup directly on the unc path.
    Like : \\Servername\sqlbackup\
    and it works.

    we build one Fileserver for all our SQL Server backups.

    Many wishes from Germany
    Alex

    Reply
  • nakulvachhrajani
    March 19, 2015 12:21 am

    Yes, in fact this is a quite common scenario in development and QA environments where backups are frequently restored and/or taken (similarly for production environments, we would need to store the backups on a remote share).

    What I have found to be useful for us is to directly use the UNC path and assign it to a dump device (via sp_addumpdevice) – it’s the same thing as you propose with the difference that xp_cmdshell works on the OS whereas the mapping created by sp_addumpdevice is local to SQL Server (with the added advantage that the device name can be much more descriptive!).

    Reply
  • Chris Harmon
    March 19, 2015 6:10 am

    Mapping a network drive is a terrible solution for backups in my opinion, for two reasons:

    1) It’s an overengineering of the backup solution. A UNC path will do just fine, and avoids my next reason…

    2) It defeats any effort to create self-documenting processes. The next guy will have no idea what drive Z: is, and will have to go through some pains to determine this. Worse, if your server melts and you’re caught in a reinstall-and-restore scenario, you might not know what drive Z: was and therefore not be able to find your backups quickly when it counts.

    Reply
    • Your points are valid Chris. But in a few cases, I have seen that the backup program would take backup to the fix drive letter and underlying shared location might change. One need not modify many maintenance plans and job and just change the mapping.

      Reply
  • Thank you so much !
    It really helped

    Reply
  • Rishi Trivedi
    March 25, 2015 1:54 am

    Hey Pinal ,
    This is such a useful resource , thank you for posting all the good stuff. I need some clarification on how to use the Number Table along with a Cross Apply . It would be great if you could show us the magic in your way …

    Thanks

    Rishi

    Reply
  • Deiby Marcos
    July 22, 2015 3:20 am

    I’m actually dealing with this error
    Msg 3202, Level 16, State 1, Line 1
    Write on “\198.110.6.134biztalkqa_bkMarcosMarcos_test.bak” failed: 4(The system cannot open the file.)
    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.

    I dont want to map the drive and I have already granted all the permissions to the account to that folder but still not working. I wonder if it is a windows issue or if you have found something like this? I accessed manually the path with the service account and create files and it works but it doesn’t work via SQL

    Reply
  • Thank you. Very helpful

    Reply
  • Kshitij Yelkar
    July 30, 2015 4:10 pm

    Dear Pinal

    I have executive below steps

    EXEC xp_cmdshell ‘net use Z: \192.168.2.1eSQL Backup’
    GO

    yet getting error as

    output
    System error 1312 has occurred.
    NULL
    A specified logon session does not exist. It may already have been terminated.
    NULL
    NULL

    Please update what action needs to be taken on same

    Reply
  • Gents,

    Its a very good post Pinal. I would definitely need help though.
    Any help would be much appreciated.
    I am (also) getting the below error:

    System error 1312 has occurred.
    NULL
    A specified logon session does not exist. It may already have been terminated.
    NULL
    NULL

    Thank you., God bless.
    Regards,

    Reply

Leave a Reply

Menu
Exit mobile version