drop\truncate table, will sql able to recover?

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?
motioneyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pinaldaveCommented:
For Drop table you can recover from log.
For truncate you can not as it is not logged in transaction.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
><<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
NightmanCTOCommented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
motioneyeAuthor Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.