Power failure->Sharepoint DB with torn page

Hi guys!
I'd really appreciate some help and directions about a sql server issue: I
had failure on my server (the fan of a cpu's got broken so the system
ungracefully stopped due to the overheat protection of the CPU). It's runnin
g
SBS 2003 premium with sharepoint on SQL 2000.
I was able to get the system back online (had some trobules with the RAID
array but got it fixed) and I was able to have a snapshot of the files just
in case I should be in need to get back to the "original" situation.
Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106MB
sharepoint DB that I'd love to recover.
I already tried to ask for help in the italian sql server newsgroup but no
one was able to help me. What's more, I can't ask for microsoft online
support as they only support english platforms (perhaps I could give it a
try, I mean, the problem would still be exactly the same).
Now the situation is that I've got the mdf and ldf files (I can't use
backups as I need some recent data that still wasn't backed up) and the DB
has a torn page that I'm unable (don't know how) to recover from. Here's wha
t
I get in the event viewer:
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17055
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
18052 :
Errore: 823, gravit_: 24, stato: 2.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 84 46 00 00 10 00 00 00 ?F.....
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.46.51
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravit_: 24, stato: 2
I/O error (torn page) detected during read at offset 0x00000000318000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 0a 00 00 00 6f 00 .....o.
0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00 ..
Tipo evento: Errore
Origine evento: MSSQL$SHAREPOINT
Categoria evento: (2)
ID evento: 17052
Data: 11/06/2005
Ora: 12.48.06
Utente: TIBERTI\Administrator
Computer: TWINTIB
Descrizione:
Errore: 823, gravit_: 24, stato: 6
I/O error (torn page) detected during read at offset 0000000000000000 in
file 'F:\Programmi\Microsoft SQL
Server\MSSQL$SHAREPOINT\Data\STS_content
.mdf'.
Per ulteriori informazioni, consultare la Guida in linea e supporto tecnico
all'indirizzo http://go.microsoft.com/fwlink/events.asp.
Dati:
0000: 37 03 00 00 18 00 00 00 7......
0008: 13 00 00 00 54 00 57 00 ...T.W.
0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
0020: 41 00 52 00 45 00 50 00 A.R.E.P.
0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
0030: 00 00 07 00 00 00 6d 00 .....m.
0038: 61 00 73 00 74 00 65 00 a.s.t.e.
0040: 72 00 00 00 r...
I've already tried with tools to recover data from DBs (SQLRecovery) but it
obviusly wasn't able to recover the data saved from sharepoint...
What I'm looking for is: some directions (I am a system engineer even if I
don't work on databases but I can deal with that under correct guidance) on
how to fix this or how to obtain support from microsoft for an affordable
price (I'm a freelance consultant and MAPS subscriber and I already had to
spend much money for the RAID disks to get recovered and can't spend a
fortune on this).
thanks to you all!
AlessandroIt would be helfpul to know in more detail what you have done.
Is it really the master database which is corrupted or another
database?
Only the mdf file, not the ldf file right?
And you're unable to do any recovery of the file whatsoever?
Mark|||Hi Mark,
First of all thanks for answering. Coming to your questions to help me:
The master DB is not ok but that's not a problem. Once in the past I was
able to recover a sharepoint site from the DBs that conteined it just by
installing a new sql server with the same previous name, attaching the old
(good) content DB and make sharepoint point to it. And, I was able to
install a new instance of the SQL server, create a config and contents db
(those that are used by sharepoint), stop the sql server instance, replace
the "empty" files with the one I recovered and that's how I got into the
torn page error.
I have both the mdf and ldf files. I'm able to recover old copies of the DBs
(including the master) but in which the contents are not up to date.
If you should need further informations, please ask.
Greetings, and thank once again,
Alessandro
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||What you should have done it to do regular transaction log backups. If you'd
done that, you could
now just do a log backup, restore the most recent clean db backup and then a
ll log backup. Zero data
loss.
In your situation, you could consider exporting all tables and objects to a
new database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alessandro Tiberti" <Alessandro Tiberti@.discussions.microsoft.com> wrote in
message
news:A3A7DE8B-5FFE-4BAE-987C-9B634CF6E219@.microsoft.com...
> Hi guys!
> I'd really appreciate some help and directions about a sql server issue: I
> had failure on my server (the fan of a cpu's got broken so the system
> ungracefully stopped due to the overheat protection of the CPU). It's runn
ing
> SBS 2003 premium with sharepoint on SQL 2000.
> I was able to get the system back online (had some trobules with the RAID
> array but got it fixed) and I was able to have a snapshot of the files jus
t
> in case I should be in need to get back to the "original" situation.
> Due to the RAID unalignment, my DBs are someway corrupt and I've got a 106
MB
> sharepoint DB that I'd love to recover.
> I already tried to ask for help in the italian sql server newsgroup but no
> one was able to help me. What's more, I can't ask for microsoft online
> support as they only support english platforms (perhaps I could give it a
> try, I mean, the problem would still be exactly the same).
> Now the situation is that I've got the mdf and ldf files (I can't use
> backups as I need some recent data that still wasn't backed up) and the DB
> has a torn page that I'm unable (don't know how) to recover from. Here's w
hat
> I get in the event viewer:
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17055
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> 18052 :
> Errore: 823, gravit_: 24, stato: 2.
>
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 84 46 00 00 10 00 00 00 ?F.....
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.46.51
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravit_: 24, stato: 2
> I/O error (torn page) detected during read at offset 0x00000000318000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\oldmaster.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 0a 00 00 00 6f 00 .....o.
> 0038: 6c 00 64 00 6d 00 61 00 l.d.m.a.
> 0040: 73 00 74 00 65 00 72 00 s.t.e.r.
> 0048: 00 00 ..
> Tipo evento: Errore
> Origine evento: MSSQL$SHAREPOINT
> Categoria evento: (2)
> ID evento: 17052
> Data: 11/06/2005
> Ora: 12.48.06
> Utente: TIBERTI\Administrator
> Computer: TWINTIB
> Descrizione:
> Errore: 823, gravit_: 24, stato: 6
> I/O error (torn page) detected during read at offset 0000000000000000 in
> file 'F:\Programmi\Microsoft SQL
> Server\MSSQL$SHAREPOINT\Data\STS_content
.mdf'.
> Per ulteriori informazioni, consultare la Guida in linea e supporto tecnic
o
> all'indirizzo http://go.microsoft.com/fwlink/events.asp.
> Dati:
> 0000: 37 03 00 00 18 00 00 00 7......
> 0008: 13 00 00 00 54 00 57 00 ...T.W.
> 0010: 49 00 4e 00 54 00 49 00 I.N.T.I.
> 0018: 42 00 5c 00 53 00 48 00 B.\.S.H.
> 0020: 41 00 52 00 45 00 50 00 A.R.E.P.
> 0028: 4f 00 49 00 4e 00 54 00 O.I.N.T.
> 0030: 00 00 07 00 00 00 6d 00 .....m.
> 0038: 61 00 73 00 74 00 65 00 a.s.t.e.
> 0040: 72 00 00 00 r...
> I've already tried with tools to recover data from DBs (SQLRecovery) but i
t
> obviusly wasn't able to recover the data saved from sharepoint...
> What I'm looking for is: some directions (I am a system engineer even if I
> don't work on databases but I can deal with that under correct guidance) o
n
> how to fix this or how to obtain support from microsoft for an affordable
> price (I'm a freelance consultant and MAPS subscriber and I already had to
> spend much money for the RAID disks to get recovered and can't spend a
> fortune on this).
> thanks to you all!
> Alessandro|||as stated in a previous post in microsoft.public.it.sq
(http://groups.google.it/group/micro...d56532ea25dab83),
I creted a new instance, created a new db in it with the STS_content name
(the same as the previous one), stopped the instance, replaced the new empty
files (mdf and ldf) with the old full ones, brought back sql service online
and then tried to remove the torn page check following the procedure
explained at
http://www.google.com/url?sa=D&q=ht...0individual.net
but I was unsuccesful:
USE master
GO
ALTER DATABASE STS_content
SET TORN_PAGE_DETECTION OFF
USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_resetstatus STS_content
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
GO
but the DB still remains in emergency status
I then received other directions to follow:
http://www.google.com/url?sa=D&q=ht...news.dfncis.de
but when a try to rebuild the DB I get:
[Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionChe_ckFor
Data
(CheckforData()).
Server: messaggio 11, livello 16, stato 1, riga 0
Errore di rete generico. Controllare la documentazione della rete.
Connessione interrotta.
if you need the english translations of the messages, let me know.
<markandersen@.evare.com> ha scritto nel messaggio
news:1120589924.726106.226510@.g44g2000cwa.googlegroups.com...
> It would be helfpul to know in more detail what you have done.
> Is it really the master database which is corrupted or another
> database?
> Only the mdf file, not the ldf file right?
> And you're unable to do any recovery of the file whatsoever?
> Mark
>|||Tibor (hope that's your first name),
the problem is: being unexperienced with SQL I don't know how to get out
tables/objects from there... the only thing I was able to do following some
directions was to start the DB in emergency mode so the DB was accessible...
I had some mail exchange with Mark that I'd love to share (after getting his
permission BTW) with others hoping that could be of any help. Greetings,
Alessandro
Alessandro,
Enterprise Manager has a section entitled
"Management/DatabaseMaintenancePlans". If you simply upgraded, then I would
look there to see about backups.
You could search your harddrive for files based on how recent they are as
another tool to locating backups. They don't have any own suffix, although
.TRN is a default suffix to look for.
I am unfamiliar with sqlrecovery. When I searched for it I found
www.sql-server-repair.com which might be of interest to you. also
www.sqlrecovery.net. What is your sqlrecovery tool?
Mark Andersen
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 10:33 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
Sorry for bring so ignorant about that subject. i was using the basic
sharepoint db installation (which uses msde) and then upgraded to SQL. I don
't
know if there were transaction logs backups or not, how can I check it?
Enterprise manager? Looking for files with particular extension?
Greetings,
Alessandro
PS: the pdf you saw is the report about the data that the recovery service
could export from the corrupt db using a quite common and well known
software called sqlrecovery divided DB by DB and I sent it to you just to
make you see the difference between the data they are able to export and the
size of the DB (most probably 'cause sqlrecovery doesn't recognize files
stored in the SQL db as they are probably stored in a proprietary
format/data type):
----
--
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: venerd 8 luglio 2005 16.15
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
You can share these emails.
I could not tell from the attachment what all those files are.
Did you have log backups running regularly on yoru server? You should
have had a dbmaintenance plan running (or some other script) every X hours
backing up the transaction logs. This is usually done more frequently than
a full backup.
The restore procedure is to get the last good full backup (not the .mdf
file but the sql backup!) and then restore it, followed by all log backups.
You can restore at least to the point of the torn page, perhaps further.
If you do not have any full or log backups and all you have is an mdf
file, the best I can suggest is that you go back to the last .mdf file which
you backed up. Presumably you backup the .mdf and .ldf files from time to
time. However, I am unsure that such a backup will really work unless sql
server was offline when the .mdf and .ldf file were backed up as you would
have two files which are not synchronized.
***
Do you have a full database backup? Do you have a series of transaction
log backups/dumps?
Mark Andersen
781-270-5501 (work)
781-307-1944 (cell)
--Original Message--
From: Alessandro Tiberti [mailto:alessandro@.tiberti.it]
Sent: Friday, July 08, 2005 5:26 AM
To: Andersen, Mark
Subject: R: Power failure->Sharepoint DB with torn page
First of all thanks for helping.
What I was actually trying to do is to start the db in emergency mode
and try to remove the torn page detection and try to access it just to
recover the data from sharepoint. Probably something would be missing but
most of them should be ok. but as I am not a SQL specialist I had troubles
following pthe procedures to fix that (I was only able to make the db in
emergency mode). Another chance would be to bring up the db in emergency
mode and copy all the data/tables/don't know what's the appropriate word
into another fresh db and then bring it up in place of the corrupted one.
These should be the things that most probably would help me stating to
what other italian sql MVPs told me. I also tried to ask for data recovery
but I saw that the data that can be restored is probably unuseful looking at
it's dimension (see the attached pdf file) as many picture images and other
files where in the sharepoint repository (so in the DB itself). Anyway,
2/3MBs out of 106 are nearly nothing, I would probably get more from the feb
backup.
Greetings once more!
Alessandro
PS: I'd like to share all this with the NG, would you mind as these were
private emails?
----
Da: Andersen, Mark [mailto:MarkA@.evare.com]
Inviato: gioved 7 luglio 2005 22.05
A: Alessandro Tiberti
Oggetto: RE: Power failure->Sharepoint DB with torn page
Got the file. Yes, I get a torn page error trying to restore from the
.mdf file.
If that occurs, I believe you have to restore from your last known
backup and transaction logs. I guess that is what i thought you were going
to provide. Have you tried to restore from that?
If you don't have transaction log backups then you cannot restore.
There might be companies which could edit your file and try to restore it
that way, but that's a fairly specialized service.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ha
scritto nel messaggio news:%23AKtRvfgFHA.2896@.TK2MSFTNGP09.phx.gbl...
> What you should have done it to do regular transaction log backups. If
> you'd done that, you could now just do a log backup, restore the most
> recent clean db backup and then all log backup. Zero data loss.
> In your situation, you could consider exporting all tables and objects to
> a new database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/