Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I’ve accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all.
I know that SQL Server can’t rollback already committed transaction but I was wondering if there are any alternative ways to get the rest of the data back. Any help such as some uncommon sql scrpts or anything like that would be greatly appreciated.
When you drop table data is still in the database but SQL Server is treating it as free space and it will overwrite it soon. You need to stop all database activities or make a copy of mdf and ldf files and see if you can find a third party tool that supports this. One tool I know is ApexSQL Recover but I guess there are probably others out there.
If the database is in full recovery, and the pages haven't been reused yet, you may be able to recover some of this data from the log by creating a dummy table and grabbing data from things like DBCC PAGE.
The full technique is way too involved for a full answer here, and I don't expect this to be a long-term answer on this site, nor do I want to replicate his content, but please have a thorough look at this blog post from Mladen Prajdic:
