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

  • 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

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

  • Worked! Thanks!

  • You're welcome

  • 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?

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

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

    😎

  • 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
  • There are tables that are being updated when reports are ran, so the users have write capability.

  • 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
  • No, I will have to research that. Do you have any good sources for that?

  • 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
  • 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

  • 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 14 (of 14 total)

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