Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

drop\truncate table, will sql able to recover?

Posted on 2007-04-05
12
Medium Priority
?
2,588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

670 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