You will need a previous Full Backup and any log backups taken since the last Full backup
1) Take a tail of the log backup
BACKUP LOG OldDB TO DISK='C:\OldDB_Tail.trn' WITH NO_TRUNCATE
2) Find Transaction for Deleted Records (Operation will be LOP_DELETE_ROWS for DELETEs and LOP_SET_BITS & LOP_MODIFY_ROW for a TRUNCATE TABLE)
SELECT * FROM fn_dblog(NULL,NULL) WHERE AllocUnitName = 'dbo.YourTableName'
3) Restore Previous Full Back and log backups plus the tail to a new
database WITH NORECOVERY and STOPAT='Just Before Begin Time for Transaction'
RESTORE DATABASE NewDB
FROM DISK='C:\Previous_Full_Backup_Of_OldDB.bak'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM',
MOVE 'OldDB' TO 'C:\DATABASES\NewDB.mdf',
MOVE 'OldDB_Log' TO 'C:\DATABASES\NewDB.ldf'
RESTORE LOG NewDB
FROM DISK='C:\Previous_TranLog_Backup_Of_OldDB.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'
RESTORE LOG NewDB
FROM DISK='C:\OldDB_Tail.trn'
WITH NORECOVERY,
STOPAT='2012/01/11 11:35AM'
-- This recovers the restored database and allows access
RESTORE DATABASE NewDB WITH RECOVERY
This blog post covers in more detail finding the time to STOPAT using fn_dblog.
This blog post has a go at recovering data directly from the log but this might be very time consuming compared to a point in time recovery.
Also this blog post by Robert L Davis has an example on combining STOP and STANDBY to allow you to query the state of the databases at different points in time.
You can restore the BACKUP with a different name in the same instance and follow steps below.
1) restore the database ( right click
on databases > Restore
( it can be in the same instance )) with a differente name.
2) In this new database ( The restored one ) , open tables
, search the table you've deleted, right button
, Script table as
> CREATE TO
. This will crate a Script. Execute it on the old database ( the one you have deleted the table). It will create the structure of that table.
3) Now you can do something like this:
USE [Old Database]
GO
SELECT * INTO [the blank table you've created in the new database]
FROM [the table on the backup, that has Data]
This will copy the data from the backup table, to the table you created.
Best Answer
If your database is in full recovery mode you can also try third party tools such as ApexSQL Log or SQL Log Rescue.
These tools will attempt to read your transaction log and reconstruct statements.
You can also try reading transaction log manually using fn_dblog function but it’s going to be complex since this is not a well-documented function.