April 11, 2008 at 3:59 pm
Hi All,
One of my subordiante has deleted xyz table from one of our database which kept 80 million records now I want to recover only xyz table luckly I have last night full backup. Now I'll going to restore that database to another server and after restoring I will going to use BCP or Export/import wizard.
Right now I am using Full recovery model for database where xyz table has been deleted but my question is which recovery model should I use to Import the records so that my transaction log file will not fill up.
Regards,
Frozen
April 11, 2008 at 8:52 pm
individual Table restores are not possible yet. An option is to restore the DB to a new name or server, and then copy the table(s) needed back to the original location.
I guess if this table so happened to be in its own filegroup, you could do a filegroup restore as well.
-- Cory
April 11, 2008 at 8:53 pm
U can use simple or bulk-logged recovery model. After import over, U can revert to full recovery.
April 11, 2008 at 10:39 pm
Cory Ellingson (4/11/2008)
individual Table restores are not possible yet. An option is to restore the DB to a new name or server, and then copy the table(s) needed back to the original location.I guess if this table so happened to be in its own filegroup, you could do a filegroup restore as well.
A third party tool, SQL LiteSpeed, can. I don't know if it needs its own format to do so, but you certainly could investigate. It might save some time.
K. Brian Kelley
@kbriankelley
April 14, 2008 at 8:36 am
supposing your production db is PRODDB (Recovery=FULL), and you have restored the dump to LEGACYDB
you have the problem of getting the lost 80M rows from LEGACYDB into PRODDB without blowing the tranlog there. Easiest is to use SSIS (use the wizard why not) which will use fast-mode into the empty PRODDB.dbo.xyz table, meaning that any constraints (PK,FK,UC) are bypassed until all 80M are in.
but you will still be creating indexes as you go, so it is best to script these out and apply after all data pumped in. Or create+populate a new index-less abc table then apply indexes, then rename to xyz at the end.
oh, and I suggest your fire said "subordiante" for being so careless, after you have checked that s/he didn't drop that audit table to hide even worse crimes.
HTH
Dick
April 15, 2008 at 4:18 pm
From my personal experience using SQL LiteSpeed I would rather recommend perform backup, restore and delete all tables that no longer needed. And it takes probably less time...
April 16, 2008 at 9:39 am
There is a tool called HyperBac which is very good for copy particular table and rows. It has a great feature and also it's very cheap.
try this link
http://www.dell.com/downloads/global/solutions/Dell_hyperbac_white_paper.pdf
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply