Error 823: I/O error (torn page) detected during read at offset...

Hi,
HDD failure caused to move the data to a new disk. We are no longer able to
attach the MDF file. I searched a lot but all of solutions work only when
the database is running under SQL Server and is suspected, not for me
(detached database).
I get this error when trying to attach the MDF file:
Error 823: I/O error (torn page) detected during read at offset
(0x0000036cba0000 in file 'D:\Data\Data_file)
Unfortunately the backup is 2 hours behind from the failure!
Any help would be greatly appreciated.
LeilaLeila (Leilas@.hotpop.com) writes:
> Date: Sun, 23 Apr 2006 20:49:51 +0430
Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database).
> I get this error when trying to attach the MDF file:
> Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
Well, at least there is a backup!
I have no idea how to handle the situation, and the only advice would
be to open a case with Microsoft, but I realise that this may not be
easy where you are located.
I'll inquire with Microsoft, but I would have too much hope.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
I really don't know what's wrong with my clock :-(
> I'll inquire with Microsoft, but I would have too much hope.
Thanks indeed!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Date: Sun, 23 Apr 2006 20:49:51 +0430
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>> HDD failure caused to move the data to a new disk. We are no longer able
>> to attach the MDF file. I searched a lot but all of solutions work only
>> when the database is running under SQL Server and is suspected, not for
>> me (detached database).
>> I get this error when trying to attach the MDF file:
>> Error 823: I/O error (torn page) detected during read at offset
>> (0x0000036cba0000 in file 'D:\Data\Data_file)
>> Unfortunately the backup is 2 hours behind from the failure!
>> Any help would be greatly appreciated.
> Well, at least there is a backup!
> I have no idea how to handle the situation, and the only advice would
> be to open a case with Microsoft, but I realise that this may not be
> easy where you are located.
> I'll inquire with Microsoft, but I would have too much hope.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
>> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
>> I'll inquire with Microsoft, but I would have too much hope.
One suggestion I got from a fellow MVP was:
How about stopping SS, renaming the corrupted database files, starting
SS, create new database with same filenames as corrupted db had
originally, stopping SS, removing new database files and then renaming
corrupted db backup to the original names? SQL Server will possibly
recover the database fine...
If I were to do this operation, I would make sure that I have a copy of
the bad database files, to keep a starting point.
I'm a little skeptic that the above actually works. I guess that whar
you are really after is the transaction log, so you can back it up, and
apply it to the backup, so you don't lose those two hours of work.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila,
If you can read Russian, or fill in the gaps with
some online translation, this might help:
http://www.sql.ru/faq/faq_topic.aspx?fid=123
Steve Kass
Drew University
Leila wrote:
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>I really don't know what's wrong with my clock :-(
>
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>>Leila (Leilas@.hotpop.com) writes:
>>
>>Date: Sun, 23 Apr 2006 20:49:51 +0430
>>
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>>HDD failure caused to move the data to a new disk. We are no longer able
>>to attach the MDF file. I searched a lot but all of solutions work only
>>when the database is running under SQL Server and is suspected, not for
>>me (detached database).
>>I get this error when trying to attach the MDF file:
>>Error 823: I/O error (torn page) detected during read at offset
>>(0x0000036cba0000 in file 'D:\Data\Data_file)
>>Unfortunately the backup is 2 hours behind from the failure!
>>Any help would be greatly appreciated.
>>
>>Well, at least there is a backup!
>>I have no idea how to handle the situation, and the only advice would
>>be to open a case with Microsoft, but I realise that this may not be
>>easy where you are located.
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>|||> As we say up here "Time goes fast, when you're having fun!". :-)
lol! We say exactly the same thing here! But it seems my notebook is having
fun, not me!
> How about stopping SS, renaming the corrupted database files, starting...
oops! Really tricky! I must try it...
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AECB33EE3F0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
>> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>> I really don't know what's wrong with my clock :-(
> As we say up here "Time goes fast, when you're having fun!". :-)
>
>> I'll inquire with Microsoft, but I would have too much hope.
> One suggestion I got from a fellow MVP was:
> How about stopping SS, renaming the corrupted database files, starting
> SS, create new database with same filenames as corrupted db had
> originally, stopping SS, removing new database files and then renaming
> corrupted db backup to the original names? SQL Server will possibly
> recover the database fine...
> If I were to do this operation, I would make sure that I have a copy of
> the bad database files, to keep a starting point.
> I'm a little skeptic that the above actually works. I guess that whar
> you are really after is the transaction log, so you can back it up, and
> apply it to the backup, so you don't lose those two hours of work.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Thanks Steve!
Unfortunately I cannot read Russian but the TSQL seems that you must at
least
have your database attached to SQL Server before using that instruction?
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23dKWuUwZGHA.3532@.TK2MSFTNGP05.phx.gbl...
> Leila,
> If you can read Russian, or fill in the gaps with
> some online translation, this might help:
> http://www.sql.ru/faq/faq_topic.aspx?fid=123
> Steve Kass
> Drew University
> Leila wrote:
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>I really don't know what's wrong with my clock :-(
>>
>>I'll inquire with Microsoft, but I would have too much hope.
>>Thanks indeed!
>>
>>
>>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>>Leila (Leilas@.hotpop.com) writes:
>>Date: Sun, 23 Apr 2006 20:49:51 +0430
>>Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>>
>>HDD failure caused to move the data to a new disk. We are no longer able
>>to attach the MDF file. I searched a lot but all of solutions work only
>>when the database is running under SQL Server and is suspected, not for
>>me (detached database).
>>I get this error when trying to attach the MDF file:
>>Error 823: I/O error (torn page) detected during read at offset
>>(0x0000036cba0000 in file 'D:\Data\Data_file)
>>Unfortunately the backup is 2 hours behind from the failure!
>>Any help would be greatly appreciated.
>>Well, at least there is a backup!
>>I have no idea how to handle the situation, and the only advice would
>>be to open a case with Microsoft, but I realise that this may not be
>>easy where you are located.
>>I'll inquire with Microsoft, but I would have too much hope.
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>Books Online for SQL Server 2005 at
>>http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>>Books Online for SQL Server 2000 at
>>http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>|||Leila (Leilas@.hotpop.com) writes:
> Unfortunately I cannot read Russian but the TSQL seems that you must at
> least
> have your database attached to SQL Server before using that instruction?
Which can be solved by creating a database, and the throwing the files
for that away, and then slapping the files for the broken database in
place.
However, I doubt that this is the solution to your problem. That script
seems to create an empty log file, and if I understand correctly, the
log file is what you want to be able to access?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> HDD failure caused to move the data to a new disk. We are no longer able
> to attach the MDF file. I searched a lot but all of solutions work only
> when the database is running under SQL Server and is suspected, not for
> me (detached database). I get this error when trying to attach the MDF
> file: Error 823: I/O error (torn page) detected during read at offset
> (0x0000036cba0000 in file 'D:\Data\Data_file)
> Unfortunately the backup is 2 hours behind from the failure!
> Any help would be greatly appreciated.
I guess that you by now already have restored that two-hour old backup.
Unfortunately, it took some time to find out the correct procedure.
It is described on
http://www.sqljunkies.com/HowTo/F5B99949-4D67-4B27-B752-AA8DDB08B0C1.scuk
With quite some help of some MVP colleagues I developed the demo script
below, to illustrate the procedure.
It assumes that you know the name and location of your log files, but
with a full backup available, it should not be difficult to work out.
-- See also
-- http://www.sqljunkies.com/HowTo/F5B99949-4D67-4B27-B752-AA8DDB08B0C1.scuk
-- First take a copy of Northwind that we can mess up a bit.
BACKUP DATABASE Northwind to disk = 'c:\temp\Northwind.bak'
go
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\temp\northbreeze.mdf',
MOVE 'Northwind_log' TO 'C:\temp\northbreeze.ldf',
REPLACE
go
-- And make sure that it has full recovery.
ALTER DATABASE northbreeze SET RECOVERY FULL
go
USE northbreeze
go
-- Let's add a new customer to northbreeze.
INSERT Customers (CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode,
Country, Phone, Fax)
VALUES ('GARBO', 'Greta Garbos Deliktatesshandel', 'Frk. Gustafsson',
'Fröken', 'Mosebacke Torg 12', 'Stockholm', NULL, '11223',
'Sweden', '+46-8-90510', NULL)
go
-- Make sure that this database is backed up on its own. Backup the log
-- as well.
BACKUP DATABASE northbreeze to disk = 'c:\temp\Northbreeze.bak'
BACKUP LOG northbreeze to disk = 'c:\temp\Northbreeze.logbak'
go
-- Perform further updates in the database.
DELETE Customers
FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)
AND C.CustomerID <> 'GARBO'
go
-- Go to master
USE master
go
-- And detach database
EXEC sp_detach_db northbreeze
go
-- Assume that something bad happens to the MDF file while it is detached.
-- First step is to rename the files. (Although we could toss the bsd MDF
-- file.)
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.mdf c:\temp\northbreeze-
crashed.mdf'
EXEC xp_cmdshell 'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-
crashed.ldf'
go
-- Create a new empty database were we want the real thing to be. Make sure
-- that we have full recovery-
CREATE DATABASE northbreeze
ON (NAME = 'Northwind', FILENAME = 'C:\temp\northbreeze.mdf')
LOG ON (NAME = 'Northwind.log', FILENAME = 'C:\temp\northbreeze.ldf')
ALTER DATABASE northbreeze SET RECOVERY FULL
go
-- Shut down SQL Server.
SHUTDOWN
go
-- In file system do this:
-- 1) move northbreeze.mdf to northbreeze-dummy.mdf (or delete it)
-- 1) move northbreeze.ldf to northbreeze-dummy.ldf (or delete it)
-- 2) move northbreeze-crashed.ldf to northbreeze.ldf
-- Start server again
go
-- The northbreeze database does at this point consist of the log file only!
-- (Or SQL Server created a new MDF; I did not check.)
-- We can now back up the tail of the log. Note NO_TRUNCATE!
BACKUP LOG northbreeze TO DISK = 'C:\temp\northbreeze-tail.logbak'
WITH NO_TRUNCATE
go
-- Drop the database
DROP DATABASE northbreeze
go
-- The log file may still be there, just move it out of the way.
EXEC xp_cmdshell
'MOVE c:\temp\northbreeze.ldf c:\temp\northbreeze-crashed.ldf'
go
-- Restore the database, use no NORECOVER, as we will apply logs as well.
RESTORE DATABASE northbreeze from disk = 'c:\temp\Northbreeze.bak'
WITH NORECOVERY
-- Restore the fist log dump.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze.logbak'
WITH NORECOVERY
-- Apply the tail log.
RESTORE LOG northbreeze from disk = 'C:\temp\northbreeze-tail.logbak'
go
-- Verify that northbreeze != Northwind.
SELECT a.CustomerID, b.CustomerID
FROM Northwind..Customers a
FULL JOIN northbreeze..Customers b ON a.CustomerID = b.CustomerID
WHERE a.CustomerID IS NULL OR
b.CustomerID IS NULL
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx