Where is transaction log backup?

Question

Where is transaction log backup?

elenakhmanson

Valued Member

Points: 60
More actions
February 12, 2004 at 2:07 pm

#84027

Hi all,
I am not db admin, so please bear with me. I need to get some records back that where deleted about 12 hours.
My db uses full recovery model, and I have a complete backup file of this database which to my understanding supposed to contain a transaction Log. Can I restore this database to a specific time just from this backup file? Or did I have to backup transaction log separately? "Point it time restore" option is always disabled when I try to restore this backup.
Thanks,
Elena.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

You have to backup the log separately. If you never backup the log, backup it now before your start the restoration.

When you restore the database, restore the full database with no_recovery option and then restore the log. See BOL for details.

elenakhmanson Valued Member Points: 60 More actions · Answer 2

If I do that I can only restore to the time the log backup was created. It says that time specified is less than the minimum point in time allowed. Is it how it supposed to work? Can I do anything to restore to a different time before that? I really have to get that data back

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Do you backup log regularly? If you are, restore last full database without recovery, restore the log backups after last full backp one by one in sequence up to point of time you want.

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 4

you need atleast one full backup that was taken before your deleted happened. and subsequent transaction log backup(s) taken after your delete has happened. (assuming no one truncated tran log in between)

then you can do the point in time recovery.

-- amit

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

noeld SSC Guru Points: 96590 More actions · Answer 5

It hasn't been explicitly said but just in case:

If your FULL Backup Happened AFTER the deletion and you don't have any previous backups the data you are looking for is GONE!

* Noel

elenakhmanson Valued Member Points: 60 More actions · Answer 6

Thank you all for your help,

I was able to go back in time in the log, but not back enough, I guess it was truncated after the deletion.

At least now I know how to make sure it won't happen again.

SQLBill SSC Guru Points: 51440 More actions · Answer 7

I don't understand what you mean by you "can go back in time in the log, but not back enough".

Did you do a FULL backup before the DELETE happened? Did you do one or more Transaction Log Backups since the FULL backup?

If so, just RESTORE the Full backup WITH NORECOVERY, then restore the transaction log(s) using the WITH STOPAT option to stop the restore before the DELETE happened.

-SQLBill