0

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!'.

3
  • 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, 2012 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, 2012 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, 2012 at 21:32

1 Answer 1

1

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.

1
  • 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, 2013 at 21:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .