help me i have by mistake drop a table in SQL 2005 how recovery

hi ,by mistake we have execute a script (drop table and create table ) on
the good data how recevory all the data?please help me it's very importantrichard wrote:
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important
Hi Richard
Assuming you have a backup schedule, you'll have to restore your latest
FULL backup with the NORECOVERY option. Then apply the log backups also
with the NORECOVERY option. You can then backup you current log file (of
course unless it already has been backed up after the mistake) and then
apply this log file with RECOVERY and STOPAT option. The STOPAT time
should then be just before the "accident" happended.
If you haven't got a valid backup, you're left with getting one of the
log reader tools and then get the data from there.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi Richard
In addition to Steen's reply... if you don't have an up-to-date backup, but
you do have an old one then that could be restored as a different database
and you could re-create your table and transfer the data from the old
database using (say) the INSERT...SELECT statement. If your data has been
updated since the backup then you will have lost those changes. You may also
want to read "Designing a Backup and Restore Strategy" in Books online.
John
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||... and here's some thoughts on the subject:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"richard" <richard@.discussions.microsoft.com> wrote in message
news:E30819A6-B5EC-4EB2-AA96-E85DCB2E440D@.microsoft.com...
> hi ,by mistake we have execute a script (drop table and create table ) on
> the good data how recevory all the data?please help me it's very important|||"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
> thanks dear steen,
we are trying the step one: your latest
> FULL backup with the NORECOVERY option
it seems to take very long time (database' name restoring...) does it
normally?|||Hi Steen,
Further to richard's post please note that in this case after analysing the
transaction logs, surprisingly enough we cannot see the transaction entries
for "drop statements", so please suggest how can we recover the data from the
data file (.mdf).
- Joy
"Steen Persson (DK)" wrote:
> richard wrote:
> > hi ,by mistake we have execute a script (drop table and create table ) on
> > the good data how recevory all the data?please help me it's very important
> Hi Richard
> Assuming you have a backup schedule, you'll have to restore your latest
> FULL backup with the NORECOVERY option. Then apply the log backups also
> with the NORECOVERY option. You can then backup you current log file (of
> course unless it already has been backed up after the mistake) and then
> apply this log file with RECOVERY and STOPAT option. The STOPAT time
> should then be just before the "accident" happended.
> If you haven't got a valid backup, you're left with getting one of the
> log reader tools and then get the data from there.
>
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||richard wrote:
> "Steen Persson (DK)" wrote:
>
>>richard wrote:
>>hi ,by mistake we have execute a script (drop table and create table ) on
>>the good data how recevory all the data?please help me it's very important
>>Hi Richard
>>Assuming you have a backup schedule, you'll have to restore your latest
>>FULL backup with the NORECOVERY option. Then apply the log backups also
>>with the NORECOVERY option. You can then backup you current log file (of
>>course unless it already has been backed up after the mistake) and then
>>apply this log file with RECOVERY and STOPAT option. The STOPAT time
>>should then be just before the "accident" happended.
>>If you haven't got a valid backup, you're left with getting one of the
>>log reader tools and then get the data from there.
>>
>>--
>>Regards
>>Steen Schlüter Persson
>>Database Administrator / System Administrator
>>thanks dear steen,
> we are trying the step one: your latest
>>FULL backup with the NORECOVERY option
> it seems to take very long time (database' name restoring...) does it
> normally?
Hi Richard
When you restore with the NORECOVERY option, the database will not be
operational until you restore the following logfiles where the last one
will have to be with the RECOVERY option. This option tells the database
that no more data will be restored and the database will be operational.
You can read up on RESTORE options in Books On Line - here're also some
explanation to each option.
Regards
Steen|||Joy wrote:
> Hi Steen,
> Further to richard's post please note that in this case after analysing the
> transaction logs, surprisingly enough we cannot see the transaction entries
> for "drop statements", so please suggest how can we recover the data from the
> data file (.mdf).
> - Joy
Hi Joy
I'm not very good at the different log reader tools, but I'm sure you
should be able to see the delete transactions. How much of it that is
logged though depends on how you've deleted the data (i.e. what sql code
did you run?).
When data is deleted, there are no way to recover it from the mdf file.
You can try John's suggestion which was to restore your most recent
backup to a different database and then take the data from there. That
will of course require that the data hasn't changed since then.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator