Database recovery after truncate command

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Database recovery after truncate command

Database recovery after truncate command

Rate Topic

Display Mode

Topic Options

Author

Message

chetanr.jain

Posted Friday, February 08, 2013 2:45 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:09 AM
Points: 465, Visits: 440

Hello Friends,

I got a query from one of my team member’s friend. Here is the scenario :

SQL server Version : SQL 2005
Database Recovery Model : Simple

Issue : Jr. DBA has run a truncate command on a table having 1 mn rows (confused on similar naming convention between production / dev) . After knowing the mistake, he requested for data recovery.
Unfortunately, the database is quite old, 2 Weeks back.

Any Ideas ??

Thanks,
Chetan

Post #1417542

GilaMonster

Posted Friday, February 08, 2013 2:53 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334

Unless the truncate was run in a transaction that has not yet been committed or there's a recent backup to restore, that data is not coming back.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1417544

chetanr.jain

Posted Friday, February 08, 2013 2:57 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:09 AM
Points: 465, Visits: 440

@Gila
Truncate command was not in transaction.

Post #1417548

anthony.green

Posted Friday, February 08, 2013 3:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:40 AM
Points: 5,075, Visits: 4,833

Then the only way would be to restore the database from backup.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1417569

prettsons

Posted Wednesday, February 13, 2013 8:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:34 AM
Points: 100, Visits: 1,088

If you don't have backup then you can also try third party SQL Database Recovery Software to repair your database. First try software demo version.

SQL Database Recovery Expert Smile

Post #1419801

GilaMonster

Posted Wednesday, February 13, 2013 11:17 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 38,028, Visits: 30,334

prettsons (2/13/2013)

If you don't have backup then you can also try third party SQL Database Recovery Software to repair your database. First try software demo version.

Except that the database wasn't damaged.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1419819

homebrew01

Posted Thursday, February 14, 2013 7:08 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:23 AM
Points: 2,580, Visits: 7,292

Find the most recent backup prior to the truncate.
Restore the database temporarily with a different name, such as "ProductionDB_20130115Backup" to identify what it is.
Copy the table from ProductionDB_20130115Backup to ProductionDB.
When you are confident you no longer need the restored database, delete ProductionDB_20130115Backup.

Better than nothing I would think.

Post #1420043

« Prev Topic | Next Topic »

Permissions