October 11, 2013 at 8:28 am
Howdy,
Here's my t-sql backup command and the error that I get back.
BACKUP DATABASE TestDB2
TO DISK = '\\FulUrlPath\TestDB2.bak'
WITH INIT;
Processed 6712 pages for database 'TestDB2', file 'TestDB' on file 1.
Processed 1 pages for database 'TestDB2', file 'TestDB_log' on file 1.
Msg 3202, Level 16, State 1, Line 2
Write on "\\FulUrlPath\TestDB2.bak" failed: 2(The system cannot find the file specified.)
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
***********************************************************
My situation is that I have a native SQL backup command that intermittently fails with a "cannot find the file specified". There are times when my backup command runs fine and times when it doesn't. When I get the error sometimes I can wait a couple of mins and re-run the command and it completes as it should. Other times it can take much longer before an attempt will succeed. I can't seem to find any pattern. I've come up empty doing lots of Googling and searching.
Any advice on how I can diagnose my problem is really appreciated.
I'm on a clustered SQL 2008 R2 x64 Enterprise Edition environment using SAN mount points for the drives. The backup directory is also a SAN object.
...thanks
October 11, 2013 at 9:12 am
Firstly do you run the backup via some job which runs on schedule time or you run the backup manually , very likely it should be a schedule one .
If yes , then please generate a script via native SQL gui and run that first .Check if its runs successfully or not like right click database -->task --> Backup
Now when above condition is true , then check with event logs if its something related to network or SAN stoarge issue which you may interested to check which SAN admin .
October 11, 2013 at 9:42 am
Thank you for taking the time to respond.
I do run a backup command via a scheduled job that different syntax and it seems to generate the same error messages.
The code that I pasted above is the command that I run in SSMS that generates a backup file when I need to "run a backup now". I did change my actual URL to FulUrlPath. Other than that, it's a copy and paste of the command I ran and the occasional error that I get.
I have looked in the windows event logs for those kind of issues before when I've had this problem and didn't see anything that made sense to me. Although I didn't in this case.
October 11, 2013 at 10:50 am
This could be a permission issue, where for some reason the service account's permission to write to the UNC path cannot be verified.
This could be caused by Active directory issues.
Another possibility is the SAN, where the path is occasionally not visible for some reason.
What happens when you do ls on the UNC path via powershell at the time of the failure?
Can you write the UNC path through other means around the time of the failure?
October 11, 2013 at 11:29 am
I do usually try and hit the URL via windows explorer and can do all the normal read write functions like add a file, drop a file, rename a file.
I have not tried to access the URL path through Powershell and I have always used my ID. I will try using Powershell the next time this occurs as well as trying that under the SQL Service account as well.
I will also try to find other ways of verifying the SAN object and well as AD authentication.
Thank you for the suggestions.
October 11, 2013 at 12:13 pm
Another possibility is temporary locking of the service account.
Perhaps a process or a user is occasionally trying to authenticate with the service account credentials and failing multiple times (incorrect pwd) in a short period. This could temporarily lock the service account and cause it to lose the permissions to write to the UNC path.
I have been bitten by this in the past.
October 11, 2013 at 1:01 pm
Have you tried writing it locally first, then copying?
Not that this would be a solution, but it would prove there's an issue with the UNC mapping - issues with mapped drives disconnecting isn't exactly unusual...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 11, 2013 at 1:29 pm
Thanks to both of you for your suggestions.
I'll take a look at the service account the next time this happens. I have a GUI AD viewer that I can use to see the lockout status.
I know I've also tried running the backup to one of the mount point locations that the clustered instance owns. with mixed results. It seems like sometimes that works better and sometimes it doesn't. I'll be more diligent about trying that again the next time it occurs.
October 11, 2013 at 1:32 pm
I've realized just now that I should probably also say that I know the issue is not blocking. I do look for blocking every time this problem comes up and it's not occurring.
October 11, 2013 at 1:38 pm
What (if anything) does your System/Application Event logs have to say?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 16, 2013 at 8:23 am
I've had another failure this morning and unfortunately the event was too brief to try most of the diagnosis actions suggested. I was running a script that ran a full back and then trans log backup right after. The full backup failed, and the trans log backup didn't.
I did find the following in the Application event log...
The operating system returned the error '67(The network name cannot be found.)' while attempting 'DeleteFile' on '\\UrlRoot\UrlDefaultBackupFileLocation\RestoreCheckpointDB7.CKP'.
BackupIoRequest::ReportIoError: write failure on backup device '\\UrlRoot\UrlFullPathForOneOffBackupFileLocation\DatabaseName.bak'. Operating system error 2(The system cannot find the file specified.).
BackupIoRequest::ReportIoError: write failure on backup device '\\UrlRoot\UrlFullPathForOneOffBackupFileLocation\DatabaseName.bak'. Operating system error 2(The system cannot find the file specified.).
BACKUP failed to complete the command BACKUP DATABASE DatabaseName. Check the backup application log for detailed messages.
The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'SetEndOfFile' on '\\UrlRoot\UrlFullPathForOneOffBackupFileLocation\DatabaseName.bak'.
The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'FlushFileBuffers' on '\\UrlRoot\UrlFullPathForOneOffBackupFileLocation\DatabaseName.bak'.
... I did notice that the location of the checkpoint file is different than where I was sending the backup file. The checkpoint is being written to the default backup location. The full backup (and trans log backup) files were being written to a different location. Both paths are on the same root location \\UrlRoot\UrlDefaultBackupFileLocation versus \\UrlRoot\UrlFullPathForOneOffBackupFileLocation.
October 16, 2013 at 8:21 pm
Definitely sounds like an issue with the mapped drives (this happens) - don't suppose the external storage is a READYNAS or something similar?
While not sure it's the cause of your problem, I would probably work towards changing the UNDO file to match the location of the backup (but it really shouldn't matter)
Sorry I can't help further. I face a similar situation at our shop from time to time, when I get the error, I just wait a few minutes and kick the backup job back off...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
October 17, 2013 at 3:09 pm
Thanks for your input, it helps.
I'm working toward placing the checkpoint file at the same location as the backup file. We'll see if that reduced the frequency of these issues occurring. Our SAN vendor is NetApp.
Most times for me also I can just wait a couple of minutes and the backup will just run. Some times it seems like that takes a lot longer than I want. Especially when we are moving a new database into production and I have other people waiting on me to get the restore done.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply