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

Is it possible to recover deleted data from log file of sql server 2005 Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 4:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 27, 2010 1:12 AM
Points: 133, Visits: 48
in my database by mistake some of the data is deleted from tables,bcoz of wrong query fired, now I want to recover the deleted rows again ........so is it possible to have my previous data again....

thanx

vaibhav agrawal
Post #634565
Posted Monday, January 12, 2009 4:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 3, 2013 12:45 PM
Points: 90, Visits: 281
Hi,

it is possible to recover your data if the database is in full revovery model. You have to perform

1. a transaction log backup
2. restore the most recent full backup with norecovery
3. restore all (if exist) transaction log backups with norecovery
4. restore the last transaction log with recovery using the STOPAT option if you know the exact time that the wrong query has been fired.

If the database is in simple recovery model it is not possible to recover your data.


PSA



Post #634575
Posted Monday, January 12, 2009 4:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 2:20 AM
Points: 535, Visits: 991
-Do you have any backups available with you before the delete?
-What is the recovery model of your database?


-Vikas Bindra
Post #634576
Posted Monday, January 12, 2009 8:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:32 PM
Points: 10,205, Visits: 13,148
If you are in simple recovery you can restore the last full backup to another database and then copy the deleted rows (if they exist in the last backup) to the source database. I have done this when I did not want the entire database to be unavailable during a restore.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #634742
Posted Monday, January 12, 2009 9:50 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:07 AM
Points: 774, Visits: 1,191
No quick UNDO solution for sure

In additions to above methods (restore backup, find data)
you could perhaps use 3rd party tools to either
- read the trans. log for data
- use Red Gate Data Compare to compare Backup with Live DB, and sync. the missing data


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #634804
Posted Tuesday, January 13, 2009 2:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 21, 2011 1:11 PM
Points: 227, Visits: 82
The best way will be restore the backup in a different box that is taken before deletion of the data. And if you know the query that deleted the data then just modify the query such that it selects the same data instead of deleting. And then you can export that data from there to the prod.
Post #635857
Posted Wednesday, January 16, 2013 11:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 9:54 PM
Points: 24, Visits: 88
Hi,

can anyone help me how to recover the old data from SQL database? The data in the database only lasts for 3months. Pleaseeeeeee this is very urgent.
Post #1408156
Posted Thursday, January 17, 2013 1:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 39,955, Visits: 36,310
Please post new questions in a new thread and give as much information as possible. Thanks,


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 #1408194
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse