I have deleted one table accidentally how can I rollback that. Please suggest that.
As Joe Stefanelli said, ApexSQL tools can help. There are actually two tools - if the whole table was dropped - ApexSQL Recover. If only the table records are deleted, both ApexSQL Log and ApexSQL Recover can help.
The limitation of both tools while in trial is that they recover only 1 out of 10 transactions. The good news is that ApexSQL Log shows all the records in the grid even in trial, so you can see the records that can be recovered. The limitation is that you cannot create the undo script for all of them until you license the tool
The tools can bring back the data even if you don't have a full or differential database backup as they read the data (MDF) file (ApexSQL Recover) and transaction log (LDF and trn - online, detached, backups - ApexSQL Log)
Disclaimer: I work for ApexSQL as a Support Engineer
If you don't have a good backup, you could give a 3rd party tool like ApexSQL Log a try. I don't know what the limitations on the free trial version are, but it might be worth a shot.
If you have backups then you follow the procedure described in How to retrieve a specific table or rows from database backups or transaction log backups in SQL Server:
Back up the current transaction log
Back up your current transaction log with the NO_TRUNCATE option.
Restore a partial or full database backup
SQL Server 2000 or SQL Server 2005 If your backup strategy includes filegroup backups, you can perform a partial database restore to a different location and restore only the part of the database that contains the table that you want to retrieve. See the following references for more information about how to restore a database to a different name and location: How to restore files to a new location (Transact-SQL)
Restore transaction log backups by using the STOPAT option
Restore the transaction log backups to the partial or full database restore and stop before the point in time when the table or rows were deleted. Use the STOPAT option of RESTORE LOG command to stop the transaction log restore and to recover the database before the time when the table was deleted. See How to restore to a point in time
After you restore the database, you can copy the table or rows back to your original database by using either INSERT, Bcp (Bulk Copy Utility) or SELECT INTO. ...
Run the DBCC CHECKTABLE Transact-SQL reference on the new table to verify the integrity of the data.
I ommitted the details, you can follow the link to the KB article for a full coverage of the topic.
A few people have mentioned 3rd party tools. The only one I have personally had any luck with for transaction log playback is Toad by Quest. It's not very cheap but if it's important it's a life saver. We had some poorly written web apps that were attacked by SQL injections and ruined some old DBs. The attack wasnt really a problem but we wanted to see what code they were executing and so being able to read back the T-LOG was amazingly convenient.
We do have backup, im done with that already.Is there anyway can we work with database log to rollback recent deletion. – Simhadri
You may need to explore transaction-log replaying strategies.
Another helpful method for recovering deleted tables data in SQL Server is to use fn_dblog() function.
use mydatabase go SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'
however the method of recovering data by using fn_dblog() function is too long, but it can be helpful for viewing the deleted records of a table.