| Author |
Topic |
|
mrjack
Yak Posting Veteran
50 Posts |
 Posted - 2006-07-11 : 05:29:35
|
| i accidently truncated a table..hw do i get the data back? its urgent..please help me |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-07-11 : 05:30:58
|
I trust that you have backups?Then ou just gotta look at restoring.Duane.  |
 |
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 2006-07-11 : 05:38:02
|
| no..i dont have backup..anything i can do please? ill die.. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-07-11 : 05:41:28
|
| You're screwed dude!!!You should have had backups.Is this data's original source maybe somewhere else?Can you maybe load it from files or from another database - thats about your only hope.Duane. |
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 2006-07-11 : 05:47:48
|
| im officially dead..please.. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-07-11 : 05:51:37
|
| You can't recover data from nothing.If you don't have a backup or some source where you can regenerate the data from - then there is nothing you can do.I think you have to look into putting a backup system in place and explain to your boss how sorry you are and show him/her the plan you have to ensure that something like this does not happen again.Look at the backup database and restore database command in BOL.Google "Disaster recovery"....and pray.Duane. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-11 : 07:53:21
|
| When the database Recovery model is FULL is there anything in the TLog BEFORE the first full backup is done?Not something I practice, so I ain't a clue, and its just a thought ... 'coz if so one of those Log Reader thingies might help.Kristen |
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 2006-07-11 : 08:04:47
|
| anything i can do with my transaction log? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-11 : 08:08:58
|
Only if its building one ... which may not be the case until after you have taken your FIRST full backup (sorry, can't remember).If your LDF file is relatively massive that may be a good sign (but could also merely be a sign that you have done one, or more, large-transaction operations).Edit: TRUNCATE is NOT a large-transaction operation  Kristen |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-07-11 : 08:12:03
|
| Around year back i also had done same mistake and i had sent email on SQLMAG, i got the following reply from them just have a look at this,if its help.. for me it didnt work, i some how had a back up of previousday, for the current had to do all the transactions manually.If you were running in full recovery mode then the data will be in the log or your log backup – there are tools that can extract this information, do a live.com search and you’ll see some products that can do this: http://www.live.com/#q=sql%20log%20data%20recovery&offset=5 Otherwise I’m afraid the data is effectively gone once the transaction commits and really gone when checkpoint takes place.Chirag |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-11 : 08:45:56
|
| Always backup Database Regularly. Test Delete, Tuncate and Update statements in TEST server before applying them to PRODUCTION Server. It is time to learn how to BACKUP Database regularlyMadhivananFailing to plan is Planning to fail |
|
|
|
mrjack
Yak Posting Veteran
50 Posts |
Posted - 2006-07-11 : 09:01:42
|
| i cant feel my heart pumping man...im deep shit naw.. the table name almost the same...arrghhhh!!!! i learned it the hard way man... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-07-11 : 09:05:26
|
well maybe it's better if you leave this company anyway...them not having a proper backup plan is showing that they don't posses the neccessary profesionalism tooperate a database in my opinion.Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-18 : 17:56:27
|
| mrjack,I realize this thread is week old, but maybe you haven't recovered yet...What is your database recovery model set to for this database? If FULL, then you may be in luck still even without backups. I believe the third party tools can read a transaction log, perhaps a tlog backup (which can still be performed if you are in FULL recovery model). I don't think it cares about a starting point of a full backup. Take a look at Lumigent's and Red Gate's log reader tools.Tara Kizer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-17 : 08:04:42
|
| Maybe this approach will do if recovery model is full?Backup the database now. Restore database as a new database with point in time restore. Select the time just prior to the time you truncated the table.That did it for me at a client once.Peter LarssonHelsingborg, Sweden |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-17 : 12:29:22
|
quote:
Originally posted by Peso
Maybe this approach will do if recovery model is full?Backup the database now. Restore database as a new database with point in time restore. Select the time just prior to the time you truncated the table.That did it for me at a client once.Peter LarssonHelsingborg, Sweden
That only works if you have a full backup and the entire transaction log chain up to and including the truncation of the table. A full backup does not allow you to pick a point in time to restore to.Tara Kizer |
|
|
|
|
help...