Solved

drop\truncate table, will sql able to recover?

Posted on 2007-04-05
12
2,570 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 142

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

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 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

20 Experts available now in Live!

Get 1:1 Help Now