Help w/ recovery please

Alright, I don't know much about SQL server, this wasnt my
project, but now I'm tasked with fixing it. Yesterday
someone ran a script that dropped a bunch of tables, so
we'd like to recover to a point just before those actions
occurred.
We have current MDF and LDF files, and a backup of both
that is dated Sept 27. The LDF file format is "simple"
and is truncated to 2MB.
Is there a way to step back through the log and undo the
actions of the script? Or do we need to restore the 9.27
copy of the database and hope that the latest LDF goes
back that far? Can someone post a link or give a brief
tutorial on how to fix this? Any help would be
appreciated.The ideal course of action would have been.
12:00 Full Database Backup
15:00 Tables dropped
15:01 You were informed
15:02 Take Transaction Log Backup
15:05 Restore database from last full backup
15:10 Restore database from Transaction Log backup and use STOPAT
14:59
How do the backups you have fit with this scenario ...?
An alternative is create a new database from the last full backup you have.
Then DTS the dropped tables from the restored DB into the Live DB.
HTH
Ryan Waight, MCDBA, MCSE
"Brad V" <anonymous@.discussions.microsoft.com> wrote in message
news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
> Alright, I don't know much about SQL server, this wasnt my
> project, but now I'm tasked with fixing it. Yesterday
> someone ran a script that dropped a bunch of tables, so
> we'd like to recover to a point just before those actions
> occurred.
> We have current MDF and LDF files, and a backup of both
> that is dated Sept 27. The LDF file format is "simple"
> and is truncated to 2MB.
> Is there a way to step back through the log and undo the
> actions of the script? Or do we need to restore the 9.27
> copy of the database and hope that the latest LDF goes
> back that far? Can someone post a link or give a brief
> tutorial on how to fix this? Any help would be
> appreciated.|||If you had your DB in simple mode, it means you did not
have the ability to back up your transaction log and it
was continually truncated. A production system should
ideally be set to full or bulk-logged. Because of this,
point-in-time recovery is virutally impossible.
So you could restore your Sept. 27th DB to a new DB (not
the same name), and consider it your new master. There
are third party tools to trawl through the log, but since
it has been continually truncated, it will be of no use to
you most likely.
Then I would DTS or BCP out the data from the older DB and
reinsert it into the 27th database.|||Brad
By having your log in simple mode you will not be able to
recover the data. Your backup will be of the database on
Sept 27. Unless the data you want was available then,
there is no way you can recover it.
You can not step back through the log as you do not have
the log available. If you want to be able to recover you
need the database to be in full recovery mode. In this
mode you also need to backup the transaction logs
regularly.
Even if you had done this you do not step back, you step
forward. You have to load the full backup that is the
nearest before the time and date you want. You then use
the transaction log backups to roll forward to the point
in time that you are trying to reach.
Idealy you should do a full backup at least once a day.
Frequency of transaction log backups depends on the
criticality of the data. Typically they might be anything
from a couple of times a day to every five minutes (or
occasionally even more).
Sorry there is no good news this time.
Hope this helps.
John
>--Original Message--
>Alright, I don't know much about SQL server, this wasnt
my
>project, but now I'm tasked with fixing it. Yesterday
>someone ran a script that dropped a bunch of tables, so
>we'd like to recover to a point just before those actions
>occurred.
>We have current MDF and LDF files, and a backup of both
>that is dated Sept 27. The LDF file format is "simple"
>and is truncated to 2MB.
>Is there a way to step back through the log and undo the
>actions of the script? Or do we need to restore the 9.27
>copy of the database and hope that the latest LDF goes
>back that far? Can someone post a link or give a brief
>tutorial on how to fix this? Any help would be
>appreciated.
>.
>|||> Which is unlikely because it has already reached its 2MB
> limit and been truncated.
It doesn't matter, the log option is not available as the database is in
simple recovery mode, as the other has mentioned. Your closest bet to get
*something* out of the log is some tool that can work against the log, like
www.lumigent.com log explorer. However, it is likely that the log has been
truncated anyhow (regardless of any limit on file size).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brad V." <anonymous@.discussions.microsoft.com> wrote in message
news:11bc01c3a872$fda2f840$a601280a@.phx.gbl...
> Ok, so I need to create a transaction log backup, restore
> the 9-27 database (probably to a new name), then apply the
> log and hope that it goes back far enough to include all
> data entered since then (stopping just before the snafu).
> Which is unlikely because it has already reached its 2MB
> limit and been truncated. (I'll have to double-check that
> it was indeed limited, and not just coincidence that its
> at 2048KB)
> Thank you all for your help, I just started learning SQL
> server last night. I'll try this when I get home from
> work.
>
> >--Original Message--
> >The ideal course of action would have been.
> > 12:00 Full Database Backup
> > 15:00 Tables dropped
> > 15:01 You were informed
> > 15:02 Take Transaction Log Backup
> > 15:05 Restore database from last full backup
> > 15:10 Restore database from Transaction Log backup
> and use STOPAT
> >14:59
> >
> >How do the backups you have fit with this scenario ...?
> >
> >An alternative is create a new database from the last
> full backup you have.
> >Then DTS the dropped tables from the restored DB into the
> Live DB.
> >
> >
> >--
> >HTH
> >Ryan Waight, MCDBA, MCSE
> >
> >"Brad V" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
> >> Alright, I don't know much about SQL server, this wasnt
> my
> >> project, but now I'm tasked with fixing it. Yesterday
> >> someone ran a script that dropped a bunch of tables, so
> >> we'd like to recover to a point just before those
> actions
> >> occurred.
> >>
> >> We have current MDF and LDF files, and a backup of both
> >> that is dated Sept 27. The LDF file format is "simple"
> >> and is truncated to 2MB.
> >>
> >> Is there a way to step back through the log and undo the
> >> actions of the script? Or do we need to restore the
> 9.27
> >> copy of the database and hope that the latest LDF goes
> >> back that far? Can someone post a link or give a brief
> >> tutorial on how to fix this? Any help would be
> >> appreciated.
> >
> >
> >.
> >|||Ok, so I need to create a transaction log backup, restore
the 9-27 database (probably to a new name), then apply the
log and hope that it goes back far enough to include all
data entered since then (stopping just before the snafu).
Which is unlikely because it has already reached its 2MB
limit and been truncated. (I'll have to double-check that
it was indeed limited, and not just coincidence that its
at 2048KB)
Thank you all for your help, I just started learning SQL
server last night. I'll try this when I get home from
work.
>--Original Message--
>The ideal course of action would have been.
> 12:00 Full Database Backup
> 15:00 Tables dropped
> 15:01 You were informed
> 15:02 Take Transaction Log Backup
> 15:05 Restore database from last full backup
> 15:10 Restore database from Transaction Log backup
and use STOPAT
>14:59
>How do the backups you have fit with this scenario ...?
>An alternative is create a new database from the last
full backup you have.
>Then DTS the dropped tables from the restored DB into the
Live DB.
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Brad V" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0f4e01c3a865$8d738fb0$a601280a@.phx.gbl...
>> Alright, I don't know much about SQL server, this wasnt
my
>> project, but now I'm tasked with fixing it. Yesterday
>> someone ran a script that dropped a bunch of tables, so
>> we'd like to recover to a point just before those
actions
>> occurred.
>> We have current MDF and LDF files, and a backup of both
>> that is dated Sept 27. The LDF file format is "simple"
>> and is truncated to 2MB.
>> Is there a way to step back through the log and undo the
>> actions of the script? Or do we need to restore the
9.27
>> copy of the database and hope that the latest LDF goes
>> back that far? Can someone post a link or give a brief
>> tutorial on how to fix this? Any help would be
>> appreciated.
>
>.
>