restore transaction log

dear all,

I want to restore transaction log to other server using transact-SQL.

can we restore serveral transaction log files at the same time ?

anybody have experience on this one.

thanks

Use the "WITH NO RECOVERY" option after each restore to allow subsequent restores. The "WITH STANDBY" option allows the database to be inspected (read only) after eah restore. Use the "WITH RECOVERY" option on the final restore to make the database read/write.

You need to make sure each log is restored in the order in which it was created. Further, you need to ensure the transaction log backup matches the full backup i.e. : you can't skip log backups, and if you truncated the log at any point, then the sequence is broken and you would need to start again with another full backup.

|||

hi rod,

I have tries your suggestion but I still have some problem.

I restore my full backup to other server using no recovery option and then I restore the transaction log. which on the last transcation log I use recovery option.

that part its work perfectly.

the problem is when I want to restore another transcation log there is an error that said :

Msg 3117, Level 16, State 4, Line 1

The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

we do daily restore transaction log to other server.

regards,

-dedys

|||

Ok, now I understand what you are trying to do.

Basically you can't do what you trying. Once you've restored a log "with recovery" you cannot restore another log at a later point. You would need to take another full backup.

What you should probably do is setup transaction log shipping but select the option to leave the database in a read only state.This allows you to read the database between log restores, however, if you are using the database when the log restore is scheduled, then either the resore will fail, or you can select the option to terminate current connections before the restore.

Does this make sense ? Let me know if you need further help on setting up log shipping.

Hope this helps.

|||

hi rod,

yes actually I want to set log shipping.

but I can't set it cause my production server is using sql 2000 and my staging server is using sql 2005.

so I plan to do it manually using job.

if you said that we can't add more transaction log after using with recovery.

so what is the best option for my case.

is there a third party software to do this ?

thanks

-dedys