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

share|improve this question

migrated from stackoverflow.com Sep 19 '09 at 23:34

This question came from our site for professional and enthusiast programmers.

add comment

4 Answers 4

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

share|improve this answer
add comment

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?
share|improve this answer
    
Yes USB Drive is Working Smoothly. –  Anonymous Sep 19 '09 at 14:43
    
It could be that the database is corrupt but still running fine, as in this case forums.databasejournal.com/showthread.php?t=35148 –  Shiraz Bhaiji Sep 19 '09 at 14:52
add comment

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.

share|improve this answer
    
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. –  Anonymous 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. –  Anonymous 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
add comment

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.

share|improve this answer
    
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. –  Anonymous 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. –  Anonymous Sep 19 '09 at 15:27
    
To move the database execute the following command within SQL Server: USE master; GO ALTER DATABASE <database Name> MODIFY FILE (NAME = <logical file name in SQL Server>, FILENAME = '<new path to file>\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. –  Russ960 Sep 19 '09 at 17:54
add comment

Your Answer

 
discard

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