Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to undo Drop Table Statement

Author  Topic 

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-18 : 08:03:27
I’ve accidentally executed DROP TABLE statement tables and I lost all the data, is there a way I can retrieve my data back?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-18 : 08:32:44
Restore the database from the most recent backup before you dropped the table (to a new database name if you cannot afford to lose other modifications done on the database since the last backup) and copy the table from that restored backup.

People claim to have methods of recovering lost data without a backup and there are even third party commerical tools that may claim to do so, but I have not tried any of those. Here is one such link: http://raresql.com/2012/02/01/how-to-recover-modified-records-from-sql-server-part-1/
Go to Top of Page

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-18 : 08:37:13
I do not have a back up
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 08:45:30
quote:
Originally posted by stahorse

I do not have a back up


see if you can get the details as per below

see

http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-18 : 11:34:14
quote:
Originally posted by stahorse

I do not have a back up

To protect yourself in the future, set up a regular backup plan. It is one of the most essential things you MUST do, and it is so easy to set it up. Ideally, you should peridically restore the backup to a development server to make sure that you have good backups and that you know how to restore a backup when the time comes. http://technet.microsoft.com/en-us/library/ms191002.aspx
Go to Top of Page

ushavellala
Starting Member

10 Posts

Posted - 2013-10-24 : 14:51:37
Firstly, keep backing up the data base before making any changes.

Second, even if you don't have a backup. Before making such transactions, use the Begin Tran, End Tran and Commit. That way even after you commit you can roll back
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 15:23:41
quote:
Originally posted by ushavellala

Firstly, keep backing up the data base before making any changes.

Second, even if you don't have a backup. Before making such transactions, use the Begin Tran, End Tran and Commit. That way even after you commit you can roll back

I don't know of a way to roll back after you commit. Is that possible in SQL Server? How do you do that?

Also, END TRAN is not valid syntax in T-SQL (Microsoft's variant of SQL programming langauge).
Go to Top of Page
   

- Advertisement -