SQL SERVER – FIX – Error 3201 – Cannot open backup device. Operating system error 3

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.

SQL SERVER - FIX - Error 3201 - Cannot open backup device. Operating system error 3 bkp-default-01

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 VersionFolder Name
SQL Server 2008MSSQL10
SQL Server 2008 R2MSSQL10_50
SQL Server 2012MSSQL11
SQL Server 2014MSSQL12

For example, if you have SQL Server 2012 default instance, it would be MSSQL11.MSSQLServer.

SQL SERVER - FIX - Error 3201 - Cannot open backup device. Operating system error 3 bkp-default-02

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.

SQL SERVER - FIX - Error 3201 - Cannot open backup device. Operating system error 3 bkp-default-03

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)

, ,
Previous Post
Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 3 of 3)
Next Post
SQL SERVER – 8 Performance Related Articles on Logical Reads

Related Posts

10 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • make sure the source instance build is not greater than the destination instance.

    Reply
  • 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

    Reply
  • Michael Mahon
    July 23, 2019 1:00 am

    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

    Reply

Leave a Reply

Menu