Bad Logs

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?
--
Chris DavoliAs Greg stated you are best to call MS PSS and work directly with someone
there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> 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?
> --
> Chris Davoli
>|||what is microsoft PSS? Is there a phone or email or something?
--
Chris Davoli
"Andrew J. Kelly" wrote:
> As Greg stated you are best to call MS PSS and work directly with someone
> there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> > 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?
> > --
> > Chris Davoli
> >
>|||On Fri, 29 Feb 2008 18:14:00 -0800, Chris Davoli
<ChrisDavoli@.discussions.microsoft.com> wrote:
>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|||"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> 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.
> --
> Chris Davoli
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||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!
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
> 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?
> --
> Chris Davoli
>|||http://support.microsoft.com/default.aspx?scid=fh%3BEN-US%3Bofferprophone
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
news:A2EC39C0-4AE9-4E86-91F4-3080E5A0D9DE@.microsoft.com...
> what is microsoft PSS? Is there a phone or email or something?
> --
> Chris Davoli
>
> "Andrew J. Kelly" wrote:
>> As Greg stated you are best to call MS PSS and work directly with someone
>> there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> > 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?
>> > --
>> > Chris Davoli
>> >
>>|||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...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> 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.
>
>> --
>> Chris Davoli
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
> 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.
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.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> 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.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>>
>|||>> 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.
> 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.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eJdwguBfIHA.4376@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F5D8A180-8F94-4654-836B-7CBE021CD5E0@.microsoft.com...
>> 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.
> 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.
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
>> news:eNaOrq6eIHA.4696@.TK2MSFTNGP05.phx.gbl...
>> "Chris Davoli" <ChrisDavoli@.discussions.microsoft.com> wrote in message
>> news:DE900A73-3DDD-46A8-B8E7-BC5936115264@.microsoft.com...
>> 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.
>>
>> --
>> Chris Davoli
>>
>>
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>>
>