Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nryme
Starting Member
2 Posts |
 Posted - 2004-08-18 : 06:41:10
|
| Hello,I lost all data from a database. I have no full backup, only a transaction log.Is it possible to restore the database to a point in the past?Or.. can I extract data from the transaction log, so I can recover the most important INSERT's that have been made. (It's a table with prestations delivered by employees)Greetz,nrymePS: I'm talking about a MS SQL Server 2000 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-18 : 07:49:08
|
| You could buy a product like Log Explorer to do this for you, provided the database was in FULL or Bulk-Logged recovery mode. If it was in Simple, it's tie to look for a job. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 12:50:31
|
| How was the data lost?Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-18 : 13:42:12
|
| By absence of a Full Backup?No maybe that's not it. Is this a trick question Tara? :-)Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 13:51:42
|
 The answer is probably the person acting as the DBA accidentally did it. So why didn't the acting DBA do the most important part of a DBA job? Backups!!!Tara |
|
|
|
nryme
Starting Member
2 Posts |
Posted - 2004-08-19 : 03:07:53
|
| I'm a webdeveloper, not a DBA. It was a database set up for a small webapplication for internal use.There is not much data lost, because only 1 employee used for about a week.I learned my lesson and from now on: each DB I put on our server will be backed up frequently :DCouldn't recover data, so my colleague will have to re-enter all data...Greetz,nryme |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-19 : 07:17:27
|
| Our development databases are backed up with Transaction Log backups hourly, as well as Full Backups daily.We need to restore fairly often (due to our own mistakes!)The Transaction Log enables us to restore to point-in-time.Note that if the last transaction backup was, say, 10AM and the time is now 10:47AM and you would like to restore to, say, 10:46AM then you need to do one more transaction BACKUP before you start restoring - in order to capture the transactions from 10AM to 10:47AMWe also do manual DIFFERENTIAL backups quite often. Just before we do something major which we expect we might have to restore. That way we can restore LAST FULL backup and then LAST DIFFERENTIAL backup - rather than LAST FULL backup, 3AM transaction backup, 4AM transaction backup ...KristenKristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-19 : 08:36:16
|
| We do the same thing for our production system Kristen. Actually we do tran logs every 15 minutes. This "might" be a little overboard for a one-user application though. :) Couldn't hurt anything though, and would be a good learning experience setting it up.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Restore data using Transaction Log