Bad Logs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Davoli - 29 Feb 2008 21:18 GMT

Let me correct that. The data part of the database is also corrupt. Is there
a way to delete the corrupted part of the data base and try a partial
recovery?

Signature

Chris Davoli

Reply to this Message

Andrew J. Kelly - 01 Mar 2008 00:51 GMT

As Greg stated you are best to call MS PSS and work directly with someone
there.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?

Reply to this Message

Chris Davoli - 01 Mar 2008 02:14 GMT

what is microsoft PSS? Is there a phone or email or something?

Signature

Chris Davoli

> As Greg stated you are best to call MS PSS and work directly with someone
> there.
[quoted text clipped - 3 lines]
> > a way to delete the corrupted part of the data base and try a partial
> > recovery?

Reply to this Message

Roy Harvey (SQL Server MVP) - 01 Mar 2008 03:05 GMT

>what is microsoft PSS? Is there a phone or email or something?

Microsoft support. You start with a phone call, pay them some money
with your charge card, and go on from there. Unless of course you
already have a support contract.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Andrew J. Kelly - 01 Mar 2008 15:40 GMT

http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> what is microsoft PSS? Is there a phone or email or something?
>
[quoted text clipped - 5 lines]
>> > a way to delete the corrupted part of the data base and try a partial
>> > recovery?

Reply to this Message

Greg D. Moore (Strider) - 01 Mar 2008 14:45 GMT

> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?

I would still HIGHLY recommend calling Microsoft.

However, if it's the data portion that's corrupt and not the log, the
recovery scenario may be something like this.

Back up "the tail of the log" as its called (there's a special command for
this but if you've already stopped SQL Server, I don't think you'll be able
to do this.)

Now, if you truly have a good backup from several months ago and haven't
truncated the log since then or in any other way broken the log chain, you
MIGHT be able to restore the database backup from then and then restore the
log and apply that.

HOWEVER, trying this on your own.... I give about a chance of 1 in a 1000 of
working. With Microsoft helping, I think you could get this down to about 1
in a 100. In other words, not very likely. Sorry.

Signature

Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Reply to this Message

Tibor Karaszi - 01 Mar 2008 18:38 GMT

Just to add a few tiny bits to Greg's recommendations:

> I would still HIGHLY recommend calling Microsoft.

Just to start with "I agree".

> Back up "the tail of the log" as its called (there's a special command for this but if you've
> already stopped SQL Server, I don't think you'll be able to do this.)

To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the backup
command, like
BACKUP LOG dbname TO DISK = 'C:\db.trn' WITH NO_TRUNCATE

This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of course

:-) ). We can even copy the ldf file(s) for a database to some other machine, create a (dummy)

database there, stop that SQL Server, delete its database files, slide in the log file(s) for this
damaged database, start that SQL Server and now do the log backup using NO_TRUNCATE. It is all about
getting the log records from the ldf file to a transaction log file.

Of course, a pre-requisite for all this is an unbroken chain of log backups...

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> Let me correct that. The data part of the database is also corrupt. Is there
>> a way to delete the corrupted part of the data base and try a partial
[quoted text clipped - 15 lines]
> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not very
> likely. Sorry.

Reply to this Message

Greg D. Moore (Strider) - 02 Mar 2008 04:14 GMT

> To backup the log of a damaged database one might have to add the
> NO_TRUNCATE option of the backup command, like
[quoted text clipped - 7 lines]
> NO_TRUNCATE. It is all about getting the log records from the ldf file to
> a transaction log file.

Ah, I wasn't sure that would work. (and I assume you mean delete the log
files, not both database files?)

> Of course, a pre-requisite for all this is an unbroken chain of log
> backups...

A mighty big one. ;-)

This is the sort of thing I might try on a lark if I had spare time, but for
production data, I'd definitely be calling Microsoft. Anything that Tibor
and I might say may or may not work and I can't speak for Tibor (though I'm
sure he'd agree) I'd hate to have you attempt to follow any advice I might
give in this case that makes things worse when Microsoft is very likely to
have a better idea.

>>> Let me correct that. The data part of the database is also corrupt. Is
>>> there
[quoted text clipped - 18 lines]
>> of working. With Microsoft helping, I think you could get this down to
>> about 1 in a 100. In other words, not very likely. Sorry.

Reply to this Message

Tibor Karaszi - 02 Mar 2008 11:42 GMT

>> This is doable even if SQL Server has been stopped (assuming one start SQL Server again, of
>> course :-) ). We can even copy the ldf file(s) for a database to some other machine, create a
[quoted text clipped - 4 lines]
> Ah, I wasn't sure that would work. (and I assume you mean delete the log files, not both database
> files?)

Yep, it work, and I've done that in productions. And I did indeed mean delete all database files.
Say you have a SQL Server installation that is toast, all you have is the ldf file for your critical
database. What you want to do is essentially to turn this ldf file into a log backup file. For this
you need to "get it into" a working SQL Server so you can issue a BACKUP LOG command.
So on on some working SQL Server, you create a database. The sole purpose of this is to get an entry
in "sysdatabases". The database files are not of interest for us. This is why we stop that SQL
Server and delete the database files. And now we copy the lof file from the crasched server (in the
right path, and file name, of course - it need to be the same as the log file for the "dummy"
database we created). So when we not start that SQL Server it will look like any SQL Server for
which the data files for the database are lost - but the log files are there. This is why we can
BACKUP LOG ... WITH NO_TRUNCATE against that database. :-)

> This is the sort of thing I might try on a lark if I had spare time, but for production data, I'd
> definitely be calling Microsoft. Anything that Tibor and I might say may or may not work and I
> can't speak for Tibor (though I'm sure he'd agree) I'd hate to have you attempt to follow any
> advice I might give in this case that makes things worse when Microsoft is very likely to have a
> better idea.

I absolutely agree.
If you don't know, by heart, what measures to take "Oh, that happenened - I know I can do this, I've
done it plenty of times before.", then call MS Support.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> To backup the log of a damaged database one might have to add the NO_TRUNCATE option of the
>> backup command, like
[quoted text clipped - 38 lines]
>>> Microsoft helping, I think you could get this down to about 1 in a 100. In other words, not
>>> very likely. Sorry.

Reply to this Message

TheSQLGuru - 01 Mar 2008 15:31 GMT

You can also try out ApexSQL Log. It may be able to recover some of the
data. Others are right though - corrupt database is deffinitely a time to
contact Microsoft support!

Signature

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

> Let me correct that. The data part of the database is also corrupt. Is
> there
> a way to delete the corrupted part of the data base and try a partial
> recovery?

Reply to this Message