Recovering data from truncated table.

I have accidentally truncated a table, and would like to get the data back.
Here are the parameters:
1. The database is using the full recovery model
2. A full backup has never been done.
3. A transaction log backup has never been done.
Since the log was never truncated, theoretically all the data is still in
the log file, but can't be retrieved normally since a full backup is
unavailable.
I tried the apexSQL Log program. Here are the results:
1. It reports that it discovered about 2500 out of about 45000 rows in a
detached copy of the log file. 2500 is the number of rows inserted after the
truncation.
2. When you try to use the "Recover Truncated Data" option, it errors out,
reporting that "file not found"
Any ideas on what I should try next?
Enkidu,
Might try calling Microsoft Product Support and see if they can work some
magic.
In the future however, I'd recommend systematic full and t-log backups for
the database. You might also consider issuing possible "damaging" admin
statements in a user-defined transaction.
HTH
Jerry
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after
> the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors
> out,
> reporting that "file not found"
> Any ideas on what I should try next?
|||Jerry,
Thanks for the advice. The data was performance data we were collecting
that actually truncated during the creation of a DTS package.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uCpb9LcyFHA.3188@.TK2MSFTNGP14.phx.gbl...
> Enkidu,
> Might try calling Microsoft Product Support and see if they can work some
> magic.
> In the future however, I'd recommend systematic full and t-log backups for
> the database. You might also consider issuing possible "damaging" admin
> statements in a user-defined transaction.
> HTH
> Jerry
|||> Since the log was never truncated, theoretically all the data is still in
> the log file
Nope. Since you never did a database backup, the database is in "log auto-truncate mode"
(essentially the same as simple recovery mode).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enkidu" <Fajja@.discussions.microsoft.com> wrote in message
news:D90002B0-F29D-4DDF-ADBD-55ECB5DD91B9@.microsoft.com...
>I have accidentally truncated a table, and would like to get the data back.
> Here are the parameters:
> 1. The database is using the full recovery model
> 2. A full backup has never been done.
> 3. A transaction log backup has never been done.
> Since the log was never truncated, theoretically all the data is still in
> the log file, but can't be retrieved normally since a full backup is
> unavailable.
> I tried the apexSQL Log program. Here are the results:
> 1. It reports that it discovered about 2500 out of about 45000 rows in a
> detached copy of the log file. 2500 is the number of rows inserted after the
> truncation.
> 2. When you try to use the "Recover Truncated Data" option, it errors out,
> reporting that "file not found"
> Any ideas on what I should try next?