Hi guys,
I am having a little trouble with a SQL Server 2000 database and I would like some help. After failure and recovery of a hard disk my database is marked as suspect and I cannot get it to work again. I have tryed a few things such as copying the database and the log files to another computer and then running the attach command which returns the following "Error 9003: The LSN(12:32:1) passed to log scan in database 'dbName' is invalid.".
Upon getting this error I have tryed with failure attaching a single file, I have also tryed to create a new database with the same name in the same location as the original, then replacing the new files with my old ones, restarting the SQL Server and getting the database into Emergency Mode hopping to extract the data through DTS or BCP Utility, but I cannot access the objects nor the data.
I have also tryed to create a new database with the same name and replace only the ".mdf" file with my old one, but failed.
I would be gratefull for any help.
Manuel.pereira@.ricmade.ptit looks quite nasty, I wonder if it's possible to survive severe drive failure without performing full restore of db. But couple of thoughts: How many log files were originally in your db?, all other databases (system) are ok after drive recovery? By setting your db into emergency mode, you mean setting status column master..sysdatabases to -32768? Didn't work? After drive recovery, what were error messages in SQL Server log after startup concerning db in question? mojza|||Hi Mojza,
I only have one log file on my database, unfortunately I have another 2 databases with the same error. When I say that I set the database into emergency mode I mean setting the status column of master.sysdatabases to -32768, but this did not work, so I suspect that the .mdf file is damaged.
After I started up SQL I did get the following error messages.
Starting up database 'rjp'.
Error: 9003, Severity: 20, State: 6
The LSN (18611:16:1) passed to log scan in database 'rjp' is invalid.|||According to MS documentation, 9003 error should mean that your mdf and ldf files are out of syn. And the solution is usually attaching single file using sp_attach_db or sp_attach_single_file_db Sps to let SQL Server create a new log file for you. But you've said it didn't work. If it's not possible to put db into emergency mode, I guess there's corruption somewhere in data file header (just my opinion) and you have only two options left: go back to your last good backups or try to contact MS Support with uncertain results.
mojza
just for the sake of completness if you run dbcc dbrecover ('your_db')
command, the result is probably the same as during server startup?|||I get the same error message when I run the command dbcc dbrecover. Also when I try to attach a single file mdf, I get the error message 1813: Could not open new database 'db_name'. Create Database is Aborted. Device activation error, the physical file name 'log path' may be incorrect.
In general you are able to execute this comand and a log file will be created for you, but not with this database.|||The net is that one should not rely on SQL Server providing a silver bullet to recover from hard disk failures. It is a risky proposition.
Instead, plan for the failures by implementing RAID, Backups, Clusters, Log Shipping, or any other tools you are provided.
Expect and be prepared for failures. Understand your options, propose at least three options to the business unit, and let them decide. DO NOT MAKE THAT CHOICE FOR THEM or you will be chasing your tail for a long, long time.
In a land and time far, far away, where I claim client-server was invented, we had business users giving us servers for which we were accountable for providing health and welfare. Before the next budget cycle, I proposed to our GM, who got it approved by The Man, to have the business units budget for MB of data vs. hardware - we buy the hardware and we'll ensure recovery based on the following:
The cocktail weenies option was a database for which you can afford to lose a day's worth of data and accept a day of downtime - it cost $1 per MB.
To get no more than an hour loss of data and to be up w/in 4 hours of failure, that costs $2 per MB and was called the shrimp option.
And the caviar option was no more than 5 minutes data loss and you're up and running w/in 20 minutes. That'll be $10 per MB, thank you very much.
I let them choose, they'd transfer their budgets, and everyone lived happily ever after. For example, the DBAs on my team who had to go through the trouble of spending weekends ensuring the caviar option worked would receive a weekend, all expenses paid vacation, for them and their significant other following the harrowing experience. We spent the cavair cash in a way that made our users happier.
This also gave us the opportunity to consolidate our servers by about 50%. So, we bought less hardware and had happier customers.
Many stories to tell, some of them true, like running our most intense application on Beta NT and Beta SQL Server (their ship dates were synchronized) and delivering for our caviar clients. We hid beer under the floor tiles in the data center as a means to get us through some of our worse nights. Nothing like doing a Delete after sipping on a few Red Hooks.
The End.|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:
1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go
2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go
3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D|||Do you think it's possible that .mdf file is ok and attach db or attach single file doesn't work? Have you come across this situation? I just try to collect experience from more knowledgable ones. mojza|||I'll assume that .MDF files are OK, so if that's the case, - there are 3 ways that accomplish the same thing (BTW, it does not matter how many LDF files there were originally, sp_attach_single_file_db will create only 1 new LDF file). For all three you need to delete or rename the original LDF files before you proceed (I recommend to move them to a safe location if none of the three methods will work). All three methods require the database to be in suspect mode, so don't mix the steps, and if you already changed the status, - make sure you pick up from there:
1. sp_configure 'allow', 1
reconfigure with override
go
update master.dbo.sysdatabases set status = status | 32768 where name = '<your_db>'
go
dbcc rebuild_log('<your_db>','c:\<your_db_log>.ldf')
go
alter database <your_db> set multi_user
go
sp_configure 'allow', 0
reconfigure with override
go
2. dbcc detachdb(<your_db>)
go
create database [<your_db>] on (filename='c:\<your_db>.mdf') for attach
go
if exists (select 1 from master.dbo.sysobjects where name = 'sp_removedbreplication') exec sp_removedbreplication '<your_db>'
go
3. ...Well, after all this - the third is the lazy man approach. SQL is actually trying to be nice to you, even if you put it to run on something that you shouldn't have ;) In short, after you renamed/deleted/moved the log devices while the databases were showing to be in Suspect mode, - just restart the service, and enjoy the show :D
I have tryed all three methods but it still does not work. I get the following error when I run the command dbcc rebuild_log
Server: Msg 3624, Level 20, State 1, Line 1
Location: filemgr.cpp:1890
Expression: fcb->GetSize () < fileSize
SPID: 52
Process ID: 3472
Connection Broken
I believe that the mdf file is damaged and that is why I cannot attach it as a single file. Do you know if there is any tool to export the data from the mdf file without being attached to SQL?
Showing posts with label failure. Show all posts
Showing posts with label failure. Show all posts
Tuesday, March 27, 2012
Monday, March 26, 2012
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
[vbcol=seagreen]
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> 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:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/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...[vbcol=seagreen]
> 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:
>|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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/F5B...8DDB08B0C1.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/F5B...8DDB08B0C1.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',
'Frken', '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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
[vbcol=seagreen]
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> 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:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/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...[vbcol=seagreen]
> 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:
>|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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/F5B...8DDB08B0C1.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/F5B...8DDB08B0C1.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',
'Frken', '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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> 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:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/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:
>|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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/F5B...8DDB08B0C1.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/F5B...8DDB08B0C1.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',
'Frken', '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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
> Your clock is ahead ahain! It's 14:30 over here, and I'm on +0200.
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Leila (Leilas@.hotpop.com) writes:
> I really don't know what's wrong with my clock :-(
As we say up here "Time goes fast, when you're having fun!". :-)
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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:
>I really don't know what's wrong with my clock :-(
>
>
>Thanks indeed!
>
>
>"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
>news:Xns97AE9408A75F1Yazorman@.127.0.0.1...
>
>
>|||> 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:
> As we say up here "Time goes fast, when you're having fun!". :-)
>
> 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/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/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:
>|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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/F5B...8DDB08B0C1.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/F5B...8DDB08B0C1.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',
'Frken', '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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql
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
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
Subscribe to:
Posts (Atom)