Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have few databases on a SQL Server 2005/WS 2003 platform, and I have a curious situation that has arisen.

I have been manually backing up the databases to a USB hard disk until about a week ago (this is not a question on the rights or wrongs of using a USB device, please). I then decided to implement a proper maintenance plan for backing up the data. No real problems with creating the plan, but something has changed and it is preventing me from writing to the USB device from one of the databases.

So...

The error I am now getting is: System.Data.SqlClient.SqlError: Write on "F:\External_Storage\mybackup.bak" failed: 27(error not found) (Microsoft.SqlServer.Smo)

This error is only happening on one database - either by running the Plan, an SQL Agent Job or right-clicking the database and choosing backup. If I select an alternative database it backs up fine.

I have checked out the error and it suggests that the disk itself might be at fault. I have run chkdsk with no errors, and like I said, only the backup for one database is failing.

I thought it might be file size as the main db to back up is 2.5GB - the alternate is a few MB by comparison, but the drive is NTFS, this should be fine right?

I have compared database properties and can see no significant differences. The only real change is the actual preparation of the maintenance plan; though, I fail to understand how this will have changed things.

I am stumped and hope that I am merely having a Homer moment and someone somewhere will make me say 'Doh!'.

share|improve this question
1  
Error 27: The drive cannot find the sector requested. That seems pretty damning of the disk. Am I to understand that performing a backup to the same path of the same disk via T-SQL does not fail? If only the maintenance plan fails, consider capturing a ProcMon capture and see what the actual error being returned is. –  Mitch Oct 6 '12 at 23:36
    
It does seem damning, but the disk is fine. I can back up to that folder, just not this particular database. I will consider ProcMon capture +1. Thanks. –  iWeasel Oct 8 '12 at 21:24
    
Is that to say that you cannot back up the database using T-SQL? If T-SQL fails - or if the backup fails even when using another target disk, consider running DBCC CHECKDB(DatabaseName). –  Mitch Oct 8 '12 at 21:32
add comment

1 Answer

Check to make sure you can actually back the database up to another location on the C:drive or any other attached storage and make sure the actual backup task works. If the backup succeeds then it is not a problem with the database. If you are using the SQL Server Maintenance plans make sure you separate out jobs by database, and have only one database per job. Don't just have one backup job that backs up all user/system databases, that could be part of the problem.

share|improve this answer
    
Thanks @Cougar9000. This was a while back now and I got around the issue by sending the back up to an internal drive and then using a sql cmd to copy the files to my USB drive and clean up old ones from the internal drive. It has been working well for a number of months now. I do not remember whether I tried your first point, however, I fully agree and practice your second point about only one DB per job. Wise words. –  iWeasel May 23 '13 at 21:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.