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.
This time I will explain why you are getting this error while taking backup on a mapped drive. Here is the situation:
- SQL Server Service runs under a domain account.
- You have already mapped the drive in Windows and can see that drive in Windows Explorer.
- 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)
- 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)
17 Comments. Leave new
— unMap the drive via T-SQL
EXEC xp_cmdshell ‘net use Z: /D’
GO
great point kaczorski. /D is for deleting/unmapping mapped drive.
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
Perfect. Thanks for the wishes Alex.
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!).
Great. thanks for sharing Nakul.
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.
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.
Thank you so much !
It really helped
Great! Thanks for letting me know Tanya.
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
Can you please explain little more Rishi?
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
Thank you. Very helpful
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
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,
I tried but its shows Access Denied
Access is denied.
NULL