Restoring using the transaction log 1

[TomKane]

Hello,

I have a query about restoring and the transaction log and would be grateful for any advice you might have.

We have an application that uses a sort of workflow and the contents of the table that keeps track of where something is within the workflow was deleted. We ended up restoring the one table from our previous backup and using a mix of guesswork and presumption we did our best to figur out where everything was supposed to be.

It sort of worked - time will tell us how well it worked. We probably had it coming. Anyway, looking forwards what I was wondering about the transaction log - I'm told that if you know what you're doing there are ways of using it to retore your database to points prior to "disaster" situations. I was wondering if anyone could point me in the right direction as to how I could implment this.

Thanks in advance,
Tom

 

[MaffewW]

here is the basics of it. You do a restore from your last full or differential backup and specify the NORECOVERY option, this allows further transaction log(s) backups to be applied. When you restore the backup of the last transaction log you use the WITH RECOVERY option telling the system that this is the last log to be restored and use the STOPAT option to specify the point in time you want to restore to (ie before the disaster!)

RESTORE DATABASE yourdb FROM DISK = 'd:\backupdatafile' WITH NORECOVERY

RESTORE LOG yourdb FROM DISK= 'd:\backuplogfile' WITH RECOVERY,STOPAT='03/07/2003 09:05:00'


Also, you can also specify marked transactions

BEGIN TRANSACTION blah blah WITH MARK [description]

so if you are doing a transaction which you think may cause problems you can do a restore back to the named mark, if you need more help with this lookup marked transactions in BOL


Matt

Brighton, UK

 

[TomKane]

Matt,

Thanks for that. This may seem like a stupid question but rhe bit where you say "When you restore the backup of the last transaction log" does that mean that we would have to backup the transaction log periodically during the day in order to be able to do that? Apologies if I appear a bit dense.

Thanks again,
Tom

 

[MaffewW]

you dont necessarily have to in order for the above to work but you probably should. You should backup your transaction log regularly, the main purpose of this is to truncate the log to avoid file growth and speed up data recovery in case of problems. An example of a backup strategy could be a full daily backup, differential backups every 3 hours, and hourly log backups. The transaction log records transactions since your last full or differemtial backup. If for example you deleted a table at 15:15 pm, you could restore your full backup (lets say you did it at 9.00am), restore the 15:00pm differential backup, then restore to 15:14 as above from the transaction log which was started at 15:00

if you have a very small database with very few transactions, you may not need so many backups, so if you did a full nightly backup and no backups throughout the day, you could use the current transaction log which would include the whole days transactions to restore to a point in time. Hopefully I've explained that ok, check out the faq in the programming forum re developing a bakup/restore strategy.



Matt

Brighton, UK

 

[harjitgill]

How do I recover if I do not have any backup files from the transaction logs.

RESTORE DATABASE yourdb FROM DISK = 'd:\backupdatafile' WITH NORECOVERY

The problem I have is that I did not do any backups and the backupdatafile directory is empty. When I do the following

RESTORE LOG yourdb FROM DISK= 'd:\backuplogfile' WITH RECOVERY,STOPAT='03/07/2003 09:05:00'

I get an error message

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or
WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Thanks in advance

Harj