Solved

drop\truncate table, will sql able to recover?

Posted on 2007-04-05
12
2,568 Views
Last Modified: 2012-08-13
Hi,
I'm using Full recovery mode with my database, so if let say i drop the table or maybe truncate, will I able to recover it?
0
Comment
Question by:motioneye
12 Comments
 
LVL 21

Expert Comment

by:pinaldave
Comment Utility
For Drop table you can recover from log.
For truncate you can not as it is not logged in transaction.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<I'm using Full recovery mode with my database, so if let say i drop the table or maybe truncate, will I able to recover it?>>Yes if your last log backup was done before the truncate else No.

Hope this helps...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
I have to disagree with both above in a technical way.

yes, you can recover (even if the last log backup was not yet done!)

you have to restore the last full backup, and then apply all the transaction log backups to just the point of time before you issued to drop/truncate statement. For that, you HAVE to do a transaction log backup AFTER the time the statement was issued.

note: this will restore the ENTIRE database, so if you only want to "restore" a single table, you have to restore under a new database name, and then copy back the table from the restored database.

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<For that, you HAVE to do a transaction log backup AFTER the time the statement was issued.>>
Can you explain to me how you can get a the data that was deleted if you apply the transaction log BACKUP after the operation.  You will simply get the version of the database with the data deleted...Or maybe did I miss something?

Regards...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
><<For that, you HAVE to do a transaction log backup AFTER the time the statement was issued.>>
>Can you explain to me how you can get a the data that was deleted if you apply the transaction log >BACKUP after the operation.  You will simply get the version of the database with the data deleted...Or maybe did I miss something?

yes, you miss something. Can you explain me, how you want to restore to the point in time just before the "drop/truncate table", unless you perform a backup log just before that bad transaction?

of course, the transaction log backup WILL contain the DROP/TRUNCATE, but the nice thing with the transaction restore is that you can say for example, assuming you did the DROP/TRUNCATE at 2007-04-05 17:49:24:
restore until 2007-04-05 17:49:23  

so, after that restore, all the table with the contents in the last second is restored.
note that, if the drop/truncate completed at 2007-04-05 17:49:26, you could restore until 2007-04-05 17:49:25 with the table still in place.


hope that clarifies



0
 
LVL 29

Expert Comment

by:Nightman
Comment Utility
As a comment on the truncate, it is a common misconception that truncate is not logged. It is - however, the individual row "deletes" are not logged - SQL simply logs the pages and extents that have been dropped. This is why truncate appears to be so much faster.

Aside from that, angelIII has provided a complete explanation as to how to restore from log backup, right up until the point in time *before* the drop/truncate statement. Note that this is one of the reasons why developers should not have access to the production system - that sinking feeling when you press F5 and *then* realize that you were connected to the production server is *not* fun :0)
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<Can you explain me, how you want to restore to the point in time just before the "drop/truncate table", unless you perform a backup log just before that bad transaction?>>
For the below explanations I assume the following:
Time of LogBackup1: T-1
Time of Deletion of data:  T
Time of LogBackup2: T+1

I believe there is a communication misunderstanding here.  I perfectly agree with the above.   If one wants to get the data that was deleted, one must obviously apply only up to LogBackup1.   This the time of the last log backup BEFORE the TRUNCATE statement (Time T).  I do not understand why you state that one would need to apply the *transaction log backup  AFTER (T+1) the time the statement was issued(meaning T)*  

I am sorry I am still missing something.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>I am sorry I am still missing something.
yes, it looks like that.

you have to apply the restore of the log backup T+1, but not completely!
telling sql server to restore the log backup PARTIALLY is possible, and one of the major reasons why transaction log backups exist. you can replay, technically, each individual transaction. you don't need to apply the full transaction log backup.

so, during the restore of the transaction log backup, you can restore to the time T-x (x being a very small time, ie 1 second), using the log backup2 (done at T+1)

of course, in case you know that there was no change in the table since T-1 and T, you do indeed not need the logbackup2 (T+1), but how can one be? Anyhow, why would be want to be sure, if you just can apply the log backup restore to Time T

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<you have to apply the restore of the log backup T+1, but not completely!
telling sql server to restore the log backup PARTIALLY is possible, and one of the major reasons why transaction log backups exist. you can replay, technically, each individual transaction. you don't need to apply the full transaction log backup.>>
Now I understand what you were getting at.  I personally never get into such granularity.  I usually set up a frequency of log backups of 10 minutes then I just restore up the T-1 log backup to get the version of data before an operation T and simply announce a maximum loss of data of 10 minutes to the client (I have found out it easier to automate at deployment time).  I guess old habits die hard ;)

Thanks for clarifying...

 
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>I usually set up a frequency of log backups of 10 minutes
which not everybody does ;)
glad we could clarify things

now it's up to motioneye to tell us if he could follow us :-)
0
 

Author Comment

by:motioneye
Comment Utility
Hi,
This is really interesteing topic for me, as AngelIII were said that sql should able to restore the Tlog partially??? that make me confuse too,
Ok in my environment I do a Tlog backup for every 5 mins

2007-04-05 17:30:00 --schedule Tlog bakup
2007-04-05 17:35:00 --schedule Tlog bakup
2007-04-05 17:37:00 -- run the truncate statement
2007-04-05 17:37:40 -- run the tlog backup manually
2007-04-05 17:40:00 --schedule Tlog bakup

So when will be the last Tlog should I apply?
<so, during the restore of the transaction log backup, you can restore to the time T-x (x being a very small time, ie 1 second), >
Or is this means I schedule the backup to run at every second???
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
>So when will be the last Tlog should I apply?
assuming that before the first listed tlog backup you had a full back:
restore that full backup, keep the db in restore mode
restore those 2 first tlog backup, keep the db in restore mode
restore that 3 tlog backup (which you run manully), with the mention of STOPAT = N'4/5/2007 5:37:00 PM' , with db in normal mode

to keep the db in restore mode, you have to put the option NORECOVERY in the RESTORE STATEMENT

article that compares the interactive way in enterprise manager, and the tsql statement:
http://www.databasejournal.com/features/mssql/article.php/3530616

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now