This blog is in continuing to use the default values. Earlier this week I wrote a blog which talked about the failure of Create Database Command. Let see in this blog post how we can solve the error 3201.
While working on the reproduction of the issue, I changed the parameters and later realized that the backup database command was also failing. If you rely on the default values for folders that don’t exist, this will raise an error message as shown below. Here is the simple command which I have run:
BACKUP DATABASE MASTER TO DISK = 'master.bak'
The command was failing with below error.
Here is the text of the message shown in image:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘E:\InvalidPath\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 you notice the error message is pretty clear. The backup is going to path “E:\InvalidPath” which is an invalid folder on my system. Important point to note here is that I have NOT given any file path in the command, I have just given file name master.bak.
The question is, from where the path is picked? If you have read my previous blog, it’s picked from “BackupDirectory” value in registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer
The highlighted portion might change based on SQL Version and Instance ID. For me it’s MSSQL12 because this is SQL Server 2014 instance. And my instance name is SQL2014:
| SQL Version | Folder Name |
| SQL Server 2008 | MSSQL10 |
| SQL Server 2008 R2 | MSSQL10_50 |
| SQL Server 2012 | MSSQL11 |
| SQL Server 2014 | MSSQL12 |
For example, if you have SQL Server 2012 default instance, it would be MSSQL11.MSSQLServer.
Here is the registry value
We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.
If you want to use T-SQL then here is the script.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, N'E:\InvalidPath' GO
NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.
Now that we have seen a couple of examples where using the default values have got us into trouble. As readers, I would like to know if you have got into any errors because of using default values in your coding stints. Let me know via your comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
I am getting this error, while taking the backup of logs.
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘D:\backup\NKIOSK_MOBILY.bak’. Operating system error 21(The device is not ready.).
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Message is clear – D drive is not ready.
Msg 7694, Level 16, State 1, Line 1
Failed to pause catalog for backup. Backup was aborted.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
how to resolve this error.
Any full text catalog which is getting populated? Check ERRORLOG and FullText Logs.
Hi Pinal, Thanks for posting. here we have issue, SQL 2014 SP1 version. backup failing due to Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally. I have verified default path. when we restart the SQL backups getting success. please suggest permanent fix
Share complete message. What do you see in ERRORLOG? What is the command?
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device ‘D:\XXXXXDatabase_FullBackup_7.6.2017’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
make sure the source instance build is not greater than the destination instance.
msg 3201,level 16,state 2,line 2
cannot open backup device ‘G:\db_backup_samples\db_mirroring.bak’ . operating system error 2( the system cannot find specified).
msg 3013, level 16, state1, line 2
restore database is terminating abnormally…..
how i can solve the issue sir
This one freaks me out… It’s not related to this article or covered by your blog or I can’t seem to find it in the search. Here is the error message I am getting:
Msg 3201, Level 16, State 2, Line 9
Cannot open backup device ‘https://******.blob.core.windows.net/*******/Full/XXXXXX_PART01.bak’. Operating system error 13(The data is invalid.).
The data is invalid.
SELECT Count(Id) FROM WarmFuzzies;
Rows Returned: 0;
:(
Any chance you might be able to clue me in on this? TY
Hi Pinal, your blogs on SQL are amazing and it helps in enhancing my knowledge. I am facing issue while building log shipping on Windows server 2008, below is the error I received. tried with multiple possibilities and ensure the permission given to shared folders on primary and secondary instances but still error continue while restoring backup to secondary database. Appreciate your help in advance.
Below is the exact error received on SQL:
SQL Server Management Studio could not backup database ‘test’ (Microsoft SQL Server Management Studio)
TITLE: Microsoft SQL Server Management Studio
——————————
SQL Server Management Studio restore database ‘test’
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (SqlManagerUI)
——————————
Cannot open backup device ‘Backup file path xyz’. Operating system error 1396(Logon Failure: The target account name is incorrect.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Operating system error 31(A device attached to the system is not functioning.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013) Cannot open backup device ‘D:\Backups\Full\xyz.BAK’. Operating system error 31(A device attached to the system is not functioning.).