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


How to recover the deleted tables in sql server 2005

i dont have any backups.

more ▼

asked Aug 16, 2010 at 08:30 AM in Default

avatar image

482 44 46 51

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'?

Aug 16, 2010 at 08:39 AM 5QL53rv3r

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.

Aug 16, 2010 at 09:53 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

10 answers: sort voted first

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.

more ▼

answered Aug 16, 2010 at 08:41 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left


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

more ▼

answered Aug 16, 2010 at 06:50 PM

avatar image

12.1k 30 36 42

Technically known as a YAFIYGI interface - you asked for it, you got it.

Aug 16, 2010 at 10:13 PM ThomasRushton ♦♦

@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!?

Aug 17, 2010 at 12:01 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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 . 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.

more ▼

answered Aug 16, 2010 at 10:28 AM

avatar image

15.6k 22 57 38

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

Aug 16, 2010 at 05:28 PM Nick Kavadias

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.

Aug 17, 2010 at 10:34 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 16, 2010 at 08:41 AM

avatar image

1.6k 55 59 62

@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.

Aug 16, 2010 at 08:47 AM Oleg

no system restore will restore system data , but not application data. it is notpossible to have these things

Aug 18, 2010 at 01:51 AM ramesh 1
(comments are locked)
10|1200 characters needed characters left

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:


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.

more ▼

answered Aug 17, 2010 at 11:15 AM

avatar image

Håkan Winther
16.6k 38 46 58

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.

Aug 17, 2010 at 02:48 PM TimothyAWiseman

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. :)

Aug 17, 2010 at 10:58 PM Håkan Winther

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.

Aug 18, 2010 at 06:52 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 16, 2010 at 08:30 AM

Seen: 29888 times

Last Updated: May 19, 2016 at 09:42 AM

Copyright 2018 Redgate Software. Privacy Policy