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 (http://blog.sqlauthority.com)
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:\be\common.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
http://beyondrelational.com/modules/2/blogs/70/posts/15256/file-paths-in-sql-server.aspx
I just can say, Thank you so much!