2

I am trying to take backup of one DB Which is showing Continuosly I/O Error 21 (The device is not ready) deteceted during read at offset 0x000000000000000040 in file e:\test.ndf Error 823. The Step Failed.

The backup was running smoothly for last 2 years.

Database Configuration is Test.mdf--->d:\ Test.ldf----> D: Test.Ndf----> E:\ USB Drive

Database is running smoothly.

4 Answers4

2

This is not a backup error: it's a database error in one of your database files. Backup can't read it and eventually you'll get it in normal operation.

You mention error 823, It's nasty: a hard IO error

To verify, run DBCC CHECK DB and see Paul Randall's site for "823". he wrote DBCC CHECK DB

...it says that an I/O operation failed at the OS level and the I/O subsystem is causing corruption...

Could be a good time to check your backups...

gbn
  • 6,009
  • 1
  • 17
  • 21
1

Things to check are:

  • Is there a problem with the USB drive?
  • Is the USB drive connected?
  • Has the drive name of the USB drive changed?
  • Is the USB drive full?
Shiraz Bhaiji
  • 2,219
  • 8
  • 34
  • 47
0

Are you confident your backup is not corrupt? Can you try using a different backup to confirm it is not your file? Also try running DBCC CHECKDB and check for logical and physical database issues.

northpole
  • 191
  • 1
  • 2
  • 9
  • DBCC CheckDB Showing Continously Errors-- Page Errors On the GAM,SGAM,PFS pages don't alllow CHECKALLOC to verify DatabaseID.(MSg 8998,level 16) differential backup is not running while Log backup is running Smoothly. –  Sep 19 '09 at 14:45
  • Well, you may in some trouble here. From what I understand PFS errors can be a killer. At this point you need to find a backup that will work. From what I have read DBCC CheckDB will not fix PFS errors. If you don't have a backup that will work, you might want to extract all the data you can and start a new db. – northpole Sep 19 '09 at 15:01
  • Can i use Copy Database Wizard. –  Sep 19 '09 at 15:17
  • I have not tried that. I usually just write my own sql to extract the data. Try the wizard, see if it works for you. – northpole Sep 19 '09 at 15:24
0

Sounds like your USB drive is having issues. Can you access the drive? You could try relocating the file to another drive. If you can't copy the file over with SQL Server down then it might be a Physical storage issue.

Russ960
  • 233
  • 5
  • 13
  • Should i detach database and move ndf file to another location and then reattach. as i have last week backup so it will risk my bread and butter. –  Sep 19 '09 at 15:08
  • took Ctrl-c and Ctrl-v to another loaction without sql server Down. Sql server was running. but when i tried to move *.mdf to another location it failed. –  Sep 19 '09 at 15:27
  • To move the database execute the following command within SQL Server: USE master; GO ALTER DATABASE MODIFY FILE (NAME = , FILENAME = '\test.ndf'); Please note you need to replace the items I noted above in <>. Once this is executed shutdown SQL Server recopy the file and restart SQL Server. If the datafile is valid it should work. –  Sep 19 '09 at 17:54