x

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

asked Aug 16 '10 at 08:30 AM in Default

ETHMAN5 gravatar image

ETHMAN5
472 41 45 48

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 '10 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 '10 at 09:53 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

9 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 '10 at 08:41 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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

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
more ▼

answered Aug 16 '10 at 06:50 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.7k 28 30 35

Technically known as a YAFIYGI interface - you asked for it, you got it.
Aug 16 '10 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 '10 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][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
more ▼

answered Aug 16 '10 at 10:28 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

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 '10 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 '10 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 '10 at 08:41 AM

Leo gravatar image

Leo
1.6k 51 56 58

@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 '10 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 '10 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:

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

answered Aug 17 '10 at 11:15 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x16

asked: Aug 16 '10 at 08:30 AM

Seen: 16745 times

Last Updated: Jan 29 at 05:34 AM