The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY

  • anu.gbd

    SSC Journeyman

    Points: 75

    Hi, I am trying to take a differenctial backup on SQL server 2000 and restoring on the same server but different instance. When i am trying to take differential backup its showing error as

    “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.

    RESTORE DATABASE is terminating abnormally.”

    Please help me out uf anyone solved it before.

    Anu

  • Jo Pattyn

    SSC-Dedicated

    Points: 31251

    You have to restore a full backup with the option no recovery / standby as a base (so it doesn’t rollback incomplete transactions and additional restores can be done). After that you can apply differential backups / transaction logs with the norecovery / standby options.

    Only when you no longer need to apply additional restores you can use RESTORE DATABASE X WITH RECOVERY.

  • kenwad

    Valued Member

    Points: 51

    Worked! Thanks!

  • Jo Pattyn

    SSC-Dedicated

    Points: 31251

    You’re welcome

  • rtimblin

    SSC Veteran

    Points: 211

    But what if all you want to do is a differential restore each evening from one database to another and not a full restore each time?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    You can apply the diff with NORECOVERY/STANDBY as well, but it means that the DB will either be completely inaccessible (norecovery) or readonly (standby)

    If you want to bring the DB into a fully usable state (read-write) and make changes to it, then you’ll have to start with a restore of the full backup each time.

    What are you trying to achieve?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • rtimblin

    SSC Veteran

    Points: 211

    We have a 90gb database that we need to keep current (for offline processing). It takes 1 hour to copy it across the network and then 4 hours to restore it. We wanted to do a differential backup of the main server and then do only a partial restore to the secondary on a nightly basis, and do only the full on the weekends. Is that impossible? Or, can it be done and how? Thanks.

  • Lynn Pettis

    SSC Guru

    Points: 442080

    Is the off-line processing reporting only, ie no updates to the database?

    😎

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Depends what you need the secondary to be doing. Read-only or read-write?

    If you can consider an upgrade to 2005, you could use database mirroring with a snapshot on the mirror server. (but that’s read-only also)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • rtimblin

    SSC Veteran

    Points: 211

    There are tables that are being updated when reports are ran, so the users have write capability.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Then what you’re trying to do is impossible with backups.

    To allow the users to write, the last backups needs to be restored with recovery. That means to apply any more backups, you have to start with the full backup again.

    Have you considered replication? Snapshot or transactional?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • rtimblin

    SSC Veteran

    Points: 211

    No, I will have to research that. Do you have any good sources for that?

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Books Online?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • almirfiorio

    Mr or Mrs. 500

    Points: 501

    kenwad - Monday, June 16, 2008 1:03 PM

    Worked! Thanks!

    im have same problem

    im have a  full backup with 87 diferentials backup in same file
    this backup are create in sql server 2000 and the file have  12 gb
    but im have this error
    “”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.
    RESTORE DATABASE is terminating abnormally.”
    can  help me  to restore my database ???

    Thanks
    Almir

  • NorthernSoul

    SSCertifiable

    Points: 6707

    almirfiorio - Friday, December 14, 2018 8:30 AM

    kenwad - Monday, June 16, 2008 1:03 PM

    Worked! Thanks!

    im have same problem

    im have a  full backup with 87 diferentials backup in same file
    this backup are create in sql server 2000 and the file have  12 gb
    but im have this error
    ""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.
    RESTORE DATABASE is terminating abnormally."
    can  help me  to restore my database ???

    Thanks
    Almir

    So you have 1 full backup and 87 differential backups have been taken since the full backup? If that’s the case you only need to restore the full backup with norecovery and then restore the latest (or whichever you want) diff with recovery.

    Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

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