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