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

Question

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

anu.gbd

SSC Veteran

Points: 245
More actions
December 11, 2007 at 2:09 am

#95520

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

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

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

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

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 More actions · Answer 2

kenwad

Valued Member

Points: 51

June 16, 2008 at 1:03 pm

#829429

Worked! Thanks!

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 3

Jo Pattyn

SSC-Dedicated

Points: 32348

June 17, 2008 at 12:29 am

#829616

You're welcome

rtimblin SSC Veteran Points: 221 More actions · Answer 4

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: 1004504 More actions · Answer 5

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: 221 More actions · Answer 6

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: 442467 More actions · Answer 7

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

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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: 221 More actions · Answer 9

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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: 221 More actions · Answer 11

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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 Say Hey Kid Points: 665 More actions · Answer 13

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: 6869 More actions · Answer 14

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