SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 help...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/11/2006 :  05:29:35  Show Profile  Reply with Quote
i accidently truncated a table..hw do i get the data back? its urgent..please help me

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/11/2006 :  05:30:58  Show Profile  Visit ditch's Homepage  Reply with Quote
I trust that you have backups?
Then ou just gotta look at restoring.


Duane.
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/11/2006 :  05:38:02  Show Profile  Reply with Quote
no..i dont have backup..anything i can do please? ill die..
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/11/2006 :  05:41:28  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/11/2006 :  05:47:48  Show Profile  Reply with Quote
im officially dead..please..
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/11/2006 :  05:51:37  Show Profile  Visit ditch's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/11/2006 :  07:53:21  Show Profile  Reply with Quote
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
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/11/2006 :  08:04:47  Show Profile  Reply with Quote
anything i can do with my transaction log?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/11/2006 :  08:08:58  Show Profile  Reply with Quote
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

Edited by - Kristen on 07/11/2006 08:09:50
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 07/11/2006 :  08:12:03  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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 previous
day, 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/11/2006 :  08:45:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 regularly

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mrjack
Yak Posting Veteran

50 Posts

Posted - 07/11/2006 :  09:01:42  Show Profile  Reply with Quote
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...
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/11/2006 :  09:05:26  Show Profile  Visit spirit1's Homepage  Reply with Quote
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 to
operate a database in my opinion.



Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 07/18/2006 :  17:56:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 07/18/2006 17:57:43
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 08/17/2006 :  08:04:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 08/17/2006 :  12:29:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Larsson
Helsingborg, 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000