How to properly restore transaction log

[xo55ox]

Hi,

Currently, I am on SQL2000 SP3 and Windows 2003.

I am trying to restore the full database backup and transaction log
backup on the development server. The full database restore is not a
problem, but the transaction log restore happened during the day is.
Everytime when I try to compare the transaction in the database
(select the last update date from one of my table as a benchmark, then
backup the transaction log for restore) to the development db with the
restored transaction log, I got none of the transaction from the log
backup.

What did I do wrong? Is my concept about using the transaction log
restore wrong?

Thanks in advance.

[Manoj Rajshekar]

Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

[xo55ox]

Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?

Thanks in advance.

ma**********@yahoo.com (Manoj Rajshekar) wrote in message news:<ab*************************@posting.google.c om>...

Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

[xo55ox]

Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?

Thanks in advance.

ma**********@yahoo.com (Manoj Rajshekar) wrote in message news:<ab*************************@posting.google.c om>...

Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar

[Greg D. Moore \(Strider\)]

"xo55ox" <xo****@hotmail.com> wrote in message
news:ab**************************@posting.google.c om...

Hi Manoj,

Thank you very much for the steps on restoring. Actually from your
restore logs description, I have a question. It seems that you are
expecting that there are multiple log backups to restore and they
should be done with the backup option WITH INIT. However, I am trying
to use the backup option WITH NOINIT, NO_TRUNCATE, NOUNLOAD, NOSKIP,
STATS = 10. And from the restore of that one log backup, I am unable
to obtain any transaction which is supposed to be backup all through
the day. Am I using the wrong backup options?
Yes, you need with NO_RECOVERY or STANDBY.

Otherwise all transactions are rolled back or forward and no further
restores can be applied.


Thanks in advance.

ma**********@yahoo.com (Manoj Rajshekar) wrote in message

news:<ab*************************@posting.google.c om>...

Hi,

Follow this

Execute the RESTORE DATABASE statement using the NORECOVERY clause.
Execute the RESTORE LOG statement to apply each transaction log
backup, specifying:
The name of the database to which the transaction log will be applied.
The backup device from where the transaction log backup will be
restored.
The RECOVERY and STOPAT clauses. If the transaction log backup does
not contain the requested time (for example, if the time specified is
beyond the end of the time covered by the transaction log), a warning
is generated and the database remains unrecovered.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

This should work.

Regards,
-Manoj Rajshekar