I had very interesting and frustrating experience. Recently I was attempting to backup one of my database and I end up on following error.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘D:\Backup\SQLAuthority.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.
Solution:
Go to your drive and create the missing folder. In my case I went to Drive D and created Backup Folder there.
Additional Story:
If you read the first line of the blog post you will read there that I mentioned that I had frustrating experience. It was not frustrating that I do not know the solution – it was totally different reason. When I encountered the above error I quickly went and created the folder in my machine. Once I created the folder, I still keep on getting the error. After a while I realize what I was doing. I was creating the folder in the machine where I had installed SQL Server Management Studio. To resolve this error one has to create the folder in the machine where SQL Server is installed. Once I created the folder to the location where I was attempting to take backup the issue was resolved.
How many of you have been in the same situation as me as mentioned in additional story?
Reference : Pinal Dave (https://blog.sqlauthority.com)
28 Comments. Leave new
In our environment we are getting this error regularly…
-Jana
Not exactly the same scenario but I have faced this error when I tried to bcp out a table to a file. It was throwing bcp error again and again. Had struggled for 2 hours and then finally found out the mistake that I was just creating the folder in the client side rather than server side. Then finally put the same command in the command prompt and found out that it was searching for a folder in server side :) and it worked
I have done something similar in the past with a SQL Agent job step trying to point to an SSIS file system package… it took me a minute to realize that the error about an invalid XML file format was because SSMS was looking for the file on my local machine and not on the server where SQL and the dtsx package was located.
I would say that almost everyone who has ever restored a database from a remote Management Studio instance has encountered this error.
While we now have this in our blood, I see a lot of newcomers walking down to my desk saying they can’t restore a DB – most often it’s either that they do not have a folder or that they forget that the SSMS UI shows them the remote server’s disks.
Hi pinal,
Must say its a very inspirational site for SQL SERVER DBA’s.
Coming to my issue,getting a similar error code,while taking the backup to the network drive.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘\\ IP address \abc’. Operating system error 1326(Logon failure: unknown user name or bad password.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Below are the few solutions applied for the error:
1. Folder has the full rights.(administartor login)
2. Using the same Login as SQL SERVER AGENT.
3.Mapped the network drive.
Still struggling for the solution.
Waiting for your reply.
Hi Vikas, have got solution now..??? if yes, can you pls let me know how you processed. Thanks in advance.
I have had these kind of issues before especially accessing drives on local folder vs folder on the server. Especially when it comes to these kind of issues i guess it would be best to work it out on the server rather than the client.
If we found this type of error then, we need to one step, we can run from required path from cmdshell.
xp_cmdshell ‘dir D:\Backup\’
So we come to is this folder is there or not.
I had very interesting and frustrating experience. Recently I was attempting to backup one of my database and I end up on following error.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘D:becommon.bak’. Operating system error 2(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
please i need help sir, i hope this issues ….
You need to read this post
add Windows credential in Control Panel–>Credential Manager
solved in my case
I just can say, Thank you so much!
add Windows credential in Control Panel–>Credential Manager
solved in my case
I also get same error but i m not using any folder, i m giving just D:\ SQLAuthority.bak in plce of D:\Backup\SQLAuthority.bak then if any one have this solution Plzz give me ,,,
Thanks in advance
My query Is
BACKUP DATABASE Misaldb TO DISK = ‘D:\Misaldb.BAK’ with INIT
Try without INIT. The INIT might be the problem. Anyway, it is bad practice to overwrite back up files. Hope that helps
I was facing same error in one of my database. I am taking backup on remote location. Out of 23 database only one database backup is giving me same error. I found out that there is space in the database name at the end. I remove that space and now backup job is running without error.
Hi Pinal and all,
I would like to add comment on this OS error3 topic. I experienced this issue recently. It was driving me nut and unable to sleep as things were set up perfectly, all permission applied but still SQL agent job which was creating backup was failing. The SQL agent job was not able to create full or transaction backups for one database. All other databases were backing up as expected.
Finally, I found solution. The database name had problem. Name of the database contained space after name like (‘SQL ‘ instead ‘SQL’). Also, I was able to creating backups for this databases through script but not scheduled maintenance job. Now I will schedule to rename this database and include this database into regular backups schedule.
I was having this error because name of the databases was containing space after name like ‘SQL ‘ instead of ‘SQL’. Now will schedule to rename this database name and include this database to regular maintenance plan.
I ran into this situation today. Thanks for the blog post – saved me a lot of trouble!
C:\Program Files\Microsoft SQL Server\110\Tools\Binn
I provide above path to store remote server backup . It shows backup completed but not getting backup on that location.
whatever path you give it would be local to server.
I have a situation where i have to backup on to network shared drive (in my case this is the network shared folder : ‘ \\ \\sdhqem***** \SQL****up’ ) and this drive is password protected (it has a login and password to that drive ). How do i specify the destination while full backup to this network drive(of different server) which has a login and password?
Thanks in advance
Hello Dave, please helpme, I need to make a backup from sql server to bucket s3 automatically and I use rclone-v1.58.0-windows-amd64 and winfsp-1.10.22006, It does not work.
I’m working with a IAM User and I configured in rclone
I’m mounted the windows drive as follows:
./rclone -vv –allow-other –allow-non-empty mount test:bucket3/ Y: –vfs-cache-mode off –config=”C:\rclone\rclone.conf”
I see the drive in the windows explorer but I use Managment Studio don’t see the drive and I excecute the sentence BACKUP DATABASE databasename TO DISK = ‘Y:\databasename.bak’;
return Msg 3201, Level 16, State 1, Line 3
No se puede abrir el dispositivo de copia de seguridad ‘Y:\databasename.bak’. Error de sistema operativo 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
Fin anómalo de BACKUP DATABASE.
I’m testing with –volumenprefix (rclone) but without any result