Results 1 to 6 of 6

Thread: RESTORE LOG WITH NORECOVERY

  1. #1
    Mike Conroy Guest

    RESTORE LOG WITH NORECOVERY

    Novice question ...

    We are running a simple replication whereby we backup the PROD server once, restore onto STANDBY and then run a SP every 5 minutes on PROD that dumps the transaction log and then calls a SP on STANDBY to apply the log to STANDBY. My problem is that both the initial restore and all subsequent logs appear to need the "WITH NORECOVERY" option which leaves the database unusable. BOL says to 'use "WITH NORECOVERY" on all log restorations except the final'. Problem is, because the whole process is automatic, we have no way of telling which of the logs is going to be the last !!!. So, my questions are, 1) am I doing it right and 2)what would happen if a log was applied twice, would SQL know and not apply it ?

  2. #2
    Join Date
    Mar 2010
    Posts
    3
    I am having the same issue. Could you please help me out

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    For original:
    1. can't tell since don't know your purpose of restoring.
    2. sql will not restore same log twice.

    Now for ravibala, what do you really like to do? Your sql version?

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    We have a Production DB Server (SQL Server 2000) and Reporting DB Server (SQL Server 2005)
    Earlier both servers were having SQL 2000. But now they have upgraded Reporting DB Server to SQL 2005.
    Backup Process – We have a process which syncs 5 databases between Production and Reporting. Complete restore is taken on a weekly basis (Sunday) and transaction logs are taken on a daily basis.

    After upgradation of Reporting server to SQL Server 2005, backup process is not running as expected.
    We were able to restore the complete backup successfully which happens weekly. But having some issues while restoring transaction logs.

    While trying to restore the database and transaction logs , we have 3 status modes:
    Recovery : We can’t use it, as it does not allow to restore transaction logs. If transaction logs are not there, we can use it very well for complete backup.
    NoRecovery : It can be used to restore but again database is no useable. It will be in recovery mode.
    StandBy : In the old reporting server(SQL 2000), they were using StandBy mode.
    It is read-only mode. But we are not able to use it as it says a message “This backup cannot be restored using WITH STANDBY because a database upgrade
    is needed. Reissue the RESTORE without WITH STANDBY.”
    As backup is from SQL 2000, it can’t be restored on SQL 2005.

    We have restored the complete backup which was done using “recovery” mode. If complete backup is done in “recovery” mode, it is not restoring transaction logs.

    Could you please help me out in resolving this issue

  5. #5
    Join Date
    Mar 2010
    Posts
    3
    We have a Production DB Server (SQL Server 2000) and Reporting DB Server (SQL Server 2005)
    Earlier both servers were having SQL 2000. But now they have upgraded Reporting DB Server to SQL 2005.
    Backup Process – We have a process which syncs 5 databases between Production and Reporting. Complete restore is taken on a weekly basis (Sunday) and transaction logs are taken on a daily basis.

    After upgradation of Reporting server to SQL Server 2005, backup process is not running as expected.
    We were able to restore the complete backup successfully which happens weekly. But having some issues while restoring transaction logs.

    While trying to restore the database and transaction logs , we have 3 status modes:
    Recovery : We can’t use it, as it does not allow to restore transaction logs. If transaction logs are not there, we can use it very well for complete backup.
    NoRecovery : It can be used to restore but again database is no useable. It will be in recovery mode.
    StandBy : In the old reporting server(SQL 2000), they were using StandBy mode.
    It is read-only mode. But we are not able to use it as it says a message “This backup cannot be restored using WITH STANDBY because a database upgrade
    is needed. Reissue the RESTORE without WITH STANDBY.”
    As backup is from SQL 2000, it can’t be restored on SQL 2005.

    We have restored the complete backup which was done using “recovery” mode. If complete backup is done in “recovery” mode, it is not restoring transaction logs.

    Could you please help me out in resolving this issue

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    You have to upgrade prod server to sql2k5 too in this case unless get rid of log shipping.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •