Solved

drop\truncate table, will sql able to recover?

Posted on 2007-04-05
12
2,575 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
ID: 18862740
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
ID: 18862760
<<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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18862847
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18862936
<<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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18862940
><<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
ID: 18862972
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18862975
<<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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18862985
>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
ID: 18863172
<<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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18863244
>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
ID: 18868253
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18868940
>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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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