-1

Following a SAN issue a SQL database was marked Suspect. Due to the extent of the inconsistencies recovery was from a valid backup & log backups. No other system or other user databases had issues and CHECKDBs succeeded. The recovered database also had a successful CHECKDB and the application was re-enabled.

However the daily backup have been failing on the problem database. CHECKDB continues to succeed with no errors. Full, Copy_Only backups produce the same error (have also tried continue_after_error) 

Msg 3203, Level 16, State 1, Line 3
Read on "mydb.mdf" failed: 
23(failed to retrieve text for this error. Reason 15105)
Msg 3203, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

I also see in the System Event log

The device, \Device\Harddisk2\DR2, has a bad block.

The server itself has since been restarted and SQL Server came back online with no errors. CHECKDB continues to report no errors for any of the databases - but the position is worsening with no valid backup now for over a week.

Other forums suggest this error may be due to file access/permissions or not enough disk space for the backup to complete but this is not the case, having tried backing up to several different locations under different credentials with the same outcome.

I’m putting together a process to export all the DB objects and bulk copy all the data out into a clean database. Another option I’ve considered is detaching/stopping sql and copying the mdf,ndf, ldf files to another server, but reluctant to stop SQL Server at the moment without securing the data first.

Would welcome any thoughts, further checks I might be able to perform whilst the DB is online to establish what the bad block might relate to.

Screenshot 1 - shows running the backup gets 70% through.1

Unsy
  • 1
  • 1

1 Answers1

0

Just to say that we have concluded the mdf file is beyond repair. To share the scenario again

With the bad sector in the mdf file

  • the T-Log backups succeeded
  • the Database was still accessible/functioning
  • and CHECKDB were appearing good

However * Full & Diff backups failed * the MDF file could not be copied when the DB was detached * the DB could still be be reattached in situ

Due to some poor file management & the delayed identification of this whole issue

  • the log chain become broken (due to limited log backups retention)
  • the only solution was to restoring an old backup and painful copy out of the data
Unsy
  • 1
  • 1