retrieving backup

Hi All

Bit of a situation so hopefully someone can advise.

I've a client who has a sql 2000 database that *unfortunately* hasn't
had a backup procedure in place. Its been running for just over a year
and on Monday it got, well, screwed. They have a 50 gig ldf file.
Yes, 50 gig. Is it possible to put in place a database maintenance
plan that will allow me to roll back to, say, last friday just from the
ldf file?

Thoughts? Any advice would be welcome.

MTIA

Mark<mark_drewersback@.yahoo.co.uk> wrote in message
news:1117042116.016174.212890@.g49g2000cwa.googlegr oups.com...
> Hi All
> Bit of a situation so hopefully someone can advise.
> I've a client who has a sql 2000 database that *unfortunately* hasn't
> had a backup procedure in place. Its been running for just over a year
> and on Monday it got, well, screwed. They have a 50 gig ldf file.
> Yes, 50 gig. Is it possible to put in place a database maintenance
> plan that will allow me to roll back to, say, last friday just from the
> ldf file?
> Thoughts? Any advice would be welcome.
> MTIA
> Mark

Most of the transactions in the log have presumably already been committed
to the database, so you could try sp_attach_db and/or
sp_attach_single_file_db, but there's no guarantee they will work (and you
don't say exactly what "screwed" means, so you might not even have the .mdf
anyway).

Apart from that, your most best option is probably to call Microsoft and see
what they can do - there's no other way to recover data directly from
unattached mdf/ldf files. If there's a year's worth of business data in
there, then the cost of getting Microsoft involved is most likely negligible
compared to the value of the data.

Simon|||By screwed, I mean, the app that is running from it is now reporting
duplicate primary key errors. We have the mdf file. What I'd like to
know is if we succesfully restored it using the mdf/ldf file then would
it be possible to roll back to the previous Friday, when it was running
fine, given that no back ups have taken place?

Thoughts?|||(mark_drewersback@.yahoo.co.uk) writes:
> By screwed, I mean, the app that is running from it is now reporting
> duplicate primary key errors. We have the mdf file. What I'd like to
> know is if we succesfully restored it using the mdf/ldf file then would
> it be possible to roll back to the previous Friday, when it was running
> fine, given that no back ups have taken place?

If there had been been a one-year old backup, and an unbroken transaction
log since then, yes.

If there is no backup at all, I don't think you can do it with the commands
that ships with SQL Server.

However, there are 3rd party tools out there that can read the transaction
log, and generate commands to undo transactions in the transaction log.
Lumigent Log Explorer (www.lumigent.com) has been in business for a long
time. Log Pi (www.logpi.com) is another alternative.

I would suggest that the best to examine more close why you get PK
duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp