Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database recovery after truncate command Expand / Collapse
Author
Message
Posted Friday, February 08, 2013 2:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 482, Visits: 512
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
Posted Friday, February 08, 2013 2:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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
Posted Friday, February 08, 2013 2:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 8:20 AM
Points: 482, Visits: 512
@Gila
Truncate command was not in transaction.

Post #1417548
Posted Friday, February 08, 2013 3:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Posted Wednesday, February 13, 2013 8:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:44 AM
Points: 102, Visits: 1,165
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
Post #1419801
Posted Wednesday, February 13, 2013 11:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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
Posted Thursday, February 14, 2013 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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 »

Add to briefcase

Permissions Expand / Collapse