!!!HELPHELPHELPRestoring Data From Transaction Logs HELPHELPHELP!!!! 2

[JaredP]

SQL Server 7

1 database

over written with blank data to only 7 tables of like 25
other tables and fields are correct. No data in Database. need to restore.

Question

Is it possible to restore the data from the transaction file, or anything other than the 2 month old Backup?


RSVP

cheers
Jared

 

[ecobb]

If you have Transaction Log backups, you can restore from them. But you're going to have to restore them individually in the order they were taken, starting from the last database backup. (which I hope was not 2 months ago!)
If you don't have a recent database backup, or if you don't have any transaction log backups, then I think you're pretty much screwed. I hope I'm wrong.

 

[JaredP]

I hope so too. Thanks!

But to clarify, is there no rollback option or undo option? there must be or why are Tranasaction LOGs backedup so much if they weren't something important. More information would be wonderful about this

 

[nowayout]

you could also restore whole data which might be loss of some time bound loss(if it is like week or one day old full database back up). or with trans log you can restore to the time when trans log got backed up i.e. if it is two months old backup and no current transaction log back up then the data which is overwritten is pretty much gone and i have no clue if the SQL server have roll back or undo option for that.

good luck and hope for the best

 

[ecobb]

To my knowledge there isn't a roll back feature, once it's gone it's gone. That's where the importance of frequent database/transaction log backups come in, if there's ever an "oops!" in the database, you can restore from a frequent backup. It's my understanding that the purpose of the Transaction File is for the transaction log backups. It records transactions, makes a backup of them, deletes itself, and starts recording transactions again. Once a transaction log is created, all previous transacations are deleted from the Transaction File so any restorations have to be done from the logs. That's why they have to be restored in the order they were created.

I hope I am making sense...

 

[JaredP]

well my Friends. THe Task has been completed. i have called microsoft. And for a Sum of 400 Canuck, we were able to restore and replace from 1 hour before the eraser took place. i was on the Phone to the Engineer over at Microsoft, Larry, he gave me the code and told me how to implement it. i did so. the data was back and the database reports no errors or corruption. this in my opinion is a huge this but prehaps it isn't here is the code.


backup log goldmine_and_sales_marketing to disk = 'c:\mssql7\backup\GM_May27_TL.bak' with no_truncate



RESTORE DATABASE GoldMine_Sales_And_Marketing
FROM DISK = 'c:\MSSQL7\BACKUP\GoldMine_Sales_And_Marketing_db_200303230200.BAK'
WITH STATS = 5, NORECOVERY



RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'c:\mssql7\backup\GM_May27_TL.bak'
WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY,
STOPAT = 'MAY 26, 2003 14:30'

this was the first step. then this code does the restore from the Transaction Logs.

RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'C:\MSSQL7\BACKUP\GoldMine_Sales_And_Marketing\GoldMine_Sales_And_Marketing_tlog_200303240000.TRN'
WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY

RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'C:\MSSQL7\BACKUP\GoldMine_Sales_And_Marketing\GoldMine_Sales_And_Marketing_tlog_200303250000.TRN'
WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY

RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'C:\MSSQL7\BACKUP\GoldMine_Sales_And_Marketing\GoldMine_Sales_And_Marketing_tlog_200303260000.TRN'
WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY

RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'C:\MSSQL7\BACKUP\GoldMine_Sales_And_Marketing\GoldMine_Sales_And_Marketing_tlog_200303270000.TRN'
WITH FILE = 1, NOUNLOAD, STATS = 5, NORECOVERY

RESTORE LOG GoldMine_Sales_And_Marketing
FROM DISK = 'c:\mssql7\backup\GM_May27_TL.bak'
WITH FILE = 1, NOUNLOAD, STATS = 5, RECOVERY,
STOPAT = 'MAY 26, 2003 14:30'

like perhaps i am just the dumbest guy in the whole world but i learned alot from this experience. DO YOUR BACKUPS!!!!!!!


but this works for restoring from Transaction files as long as you have the event Log file to see when the last time the PC was restarted. and last backup. and lots of important information.

THanks for all those who responded to my plight.

hope this code helps others. cuz it cost me 400 bucks!!!!!!!!


cheers

One Love

Jared

 

[ecobb]

Wow!! We all learned something from your experience! I'm glad you were able to restore, I had never heard of this. Thanks for the disaster recovery tip!!