File/FileGroup Restore to point in time...

Hello all...
I'm trying to do a "point in time" File/Filegroup restore. The purpose of
the restore is to retrieve a table that was deleted by accident.
The problem with my "point in time" restore... When I restore the active
log, required for file/filegroup restores, after the filegroup differential,
I end up still missing the table because I have to restore logs that included
the transaction that deleted the table.
My question is... How do I do a "point in time" file/filegroup restore that
restores the table that was deleted by accident.
Thanks for you help...
BATMANHi
Use the STOPAT clause. Stop before the table was dropped. You loose all
transactions after that as it will not be possible to restore any further
logs after that.
e.g.
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
Regards
Mike
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
You don't. That would leave the database in an inconsistent state (some data is old, some is new).
What you do is that you restore into a new database, stopat there and then copy the desired table(s)
into your production database. You can check out the PARTIAL option for the restore command, might
be useful.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:8BE2F91C-2313-4E88-BACE-B0B8AC280C36@.microsoft.com...
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Wow... That sucks!
Thanks for your help though guys!
When dealing with a VLDB that has distributed files across multiple
filegroups, it's really inconvenient to have to restore to a temporary
database to retrieve one table. This means that you cannot do a â'point in
timeâ' recovery the way Books Online leads you to believe. But now thinking
of the logic behind it all, it makes sense why you would have to restore logs
up to the last active log.
So I guess the ideal solution is to make the primary filegroup as small as
possible so that you can use the PARTIAL restore option on a temporary
database, to only restore the filegroup needed, and then use the STOPAT
option to restore the logs prior to the table being dropped.
Again, thanks for the help!
BATMAN
"BATMAN" wrote:
> Hello all...
> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
> the restore is to retrieve a table that was deleted by accident.
> The problem with my "point in time" restore... When I restore the active
> log, required for file/filegroup restores, after the filegroup differential,
> I end up still missing the table because I have to restore logs that included
> the transaction that deleted the table.
> My question is... How do I do a "point in time" file/filegroup restore that
> restores the table that was deleted by accident.
> Thanks for you help...
> BATMAN|||Make sure you test this thoroughly. Read about the PARTIAL option, it might be that the backup in
question need to be a full backup. I'm not sure, it might do to have a file group backup including
the file group in question as well as the primary file group. Check it out to make sure.
In 2005, you can mark a file group as read-only and when restoring such, you don't need to apply
transaction log backups. Assuming it has been read-only since the db backup, of course. It wouldn't
help in this particular situation, but worth mentioning...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:D0F06F33-156C-4D63-AFB3-4080DB916335@.microsoft.com...
> Wow... That sucks!
> Thanks for your help though guys!
> When dealing with a VLDB that has distributed files across multiple
> filegroups, it's really inconvenient to have to restore to a temporary
> database to retrieve one table. This means that you cannot do a "point in
> time" recovery the way Books Online leads you to believe. But now thinking
> of the logic behind it all, it makes sense why you would have to restore logs
> up to the last active log.
> So I guess the ideal solution is to make the primary filegroup as small as
> possible so that you can use the PARTIAL restore option on a temporary
> database, to only restore the filegroup needed, and then use the STOPAT
> option to restore the logs prior to the table being dropped.
> Again, thanks for the help!
> BATMAN
>
> "BATMAN" wrote:
>> Hello all...
>> I'm trying to do a "point in time" File/Filegroup restore. The purpose of
>> the restore is to retrieve a table that was deleted by accident.
>> The problem with my "point in time" restore... When I restore the active
>> log, required for file/filegroup restores, after the filegroup differential,
>> I end up still missing the table because I have to restore logs that included
>> the transaction that deleted the table.
>> My question is... How do I do a "point in time" file/filegroup restore that
>> restores the table that was deleted by accident.
>> Thanks for you help...
>> BATMAN