question

ETHMAN5 avatar image
ETHMAN5 asked

How to recover the Deleted tables in SQL server ( No back ups)

Hi How to recover the deleted tables in sql server 2005 i dont have any backups.
recovery
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You should implement a good back up strategy as soon as possible. It's irresponsible to not have backups in place for production systems. Depending on the business, it might even be illegal.
6 Likes 6 ·
No backups @ all - are you sure??? Not even a sneaky wee maintenance plan??? when you right click on the database and go to properties, under the backup heading does it say 'none'?
2 Likes 2 ·
Leo avatar image
Leo answered
I am sure you can't recover the table if you havn't got any backup. Are you sure you haven't got a backup? you can try windows system restore and see data will recover that way.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Leo Nope, the system restore will not recover any data, it is designed to restore the protected system files and does not really affect anything else. So you are correct in saying that there is no way to recover tables if there are no backups.
0 Likes 0 ·
no system restore will restore system data , but not application data. it is notpossible to have these things
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
There is no safety net for data when you issue TSQL commands or use the SSMS UI other than using transactions in your code and scheduling backups on your server. If there is no backup then you wont be able to go back to any previous state. sorry to bring bad news.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
As the others have said, there is no good way to restore the tables if you have no backup. One technique that might give you at least some hope *if* that database is in full recovery mode is Apex SQL Recover [ http://www.apexsql.com/sql_tools_recover.aspx][1] . I have never tested this particular tool, but I have used others from Apex with good results. The one other option that is very far from ideal is that if you are not fully normalized, you may have redundant data in the database and be able to reconstruct the lost tables from that. I have a colleague that was called in as a contractor to do precisely that when a developer dropped a needed table and there was no professional DBA making sure there were backups available. [1]: http://www.apexsql.com/sql_tools_recover.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

FYI this will only work if you have taken at least one full backup of the db. Otherwise even if the recovery model is set to full, you are really in pseudo-simple mode, as there is no log backup chain established
3 Likes 3 ·
As I stated, I have not tried it, and I am certainly not vouching for it. But the marketing materials specifically state that if in full recovery mode that the software will at least try to work form the active transaction log and that no backups are required for it to function in some circumstances.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
Sigh... SQL is very obedient. You tell it to delete, it doesn't ask if you are sure... it just does it. It is your responsibility to safeguard Client's data and ensure that a *reasonable** amount of it is recoverable. There is an *outside* chance of getting the data if your O/S backup had an open file agent. Ask your backup team if they can restore from prior to the issue to a test environment and you may be able to get the data. Otherwise my friend you have just been educated in the school of !@#$ happens. * reasonable = what the Client is willing to lose
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Technically known as a YAFIYGI interface - you asked for it, you got it.
3 Likes 3 ·
@Blackhawk - I think you mean "reasonable = what the Client is UNwilling to lose"? If they are willing to lose it then you dont need the backup!?
0 Likes 0 ·
basit 1 avatar image
basit 1 answered
It Is not possible to Get the Deleted Data if you have no Backup... If you have Created the Snapshot or having the Recovery policy . Sorry to but it is not possible to get back the data.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
As everyone have answered, there is no way of restoring the lost data unless you have a backup, a snapshot or an open transaction around your delete: BEGIN TRAN DROP TABLE x --COMMIT TRAN I always use transactions before I modify any data and before I commit the data, I alsways check the result to make sure I got what I asked for. If not I rollback the transaction. Sorry to say, but I guess you learned a very valueable lesson, and I think I speak for all of us when I say that we all have done some stupid things in the early stages of our carriers. The lesson is a bitter medicin, but very valuable. I guess you'll never do anything lake that in the future.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I try to be cautious with explicit open transactions due to the locking and blocking they can cause. They are certainly appropriate in some situations and I do use them on the rare occassions I have to make ad hoc changes in production, but I try to avoid making ad hoc changes in production.
1 Like 1 ·
Of course, but sometimes you have to update data in production due to major changes in the datamodel. These updates should be performed during a maintenance window and I wouldn't do that without transactions. Locking is a small issue compared to major data corruption. :)
1 Like 1 ·
Hakan, you are quite right of course. I just think people should realize the locking and blocking consequences before using an explicit transaction and only use them when the trade offs make sense, like you describe.
0 Likes 0 ·
vijay 2 avatar image
vijay 2 answered
Hello ETHMAN, Do you have any old backups like while restoring this Database????????? If you have at least one full back up then you can recover this Table again. If you dont ahve any Backup its not possible to recover.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

basit 1 avatar image
basit 1 answered
If you have no backup then you can not recover the data , if you have created the Snapshot then you have an option to recover the lost data...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ruancra avatar image
ruancra answered
Check your recycle bin
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
There is another option for recovering table data by using fn_dblog() function. use mydatabase go SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS' By running this command user can view the deleted data. But, recovering data using fn_dblog function is too lengthy process. Instead of this you can try [SysTools SQL Recovery Software][1] which can recover deleted tables, views, stored procedures etc. [1]: https://www.systoolsgroup.com/sql-recovery.html
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think after six years the OP has probably given up.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.