SQL Disaster Recovery problems

[msprygada]

I am trying to do an actual restore of my production database data to my test server to simulate a disaster situation and I am having some problems. This procedure is for a full disaster recovery, meaning that I only have my backups for recreating the database and I am using the Enterprise Manager for this procedure.

I copied my production backups (master, model, msdb, and user databases) to my test server. Now, I am trying to do a complete database restore on my test server that has a fresh copy of SQL installed.

Here are the steps have so far. I go into single user mode and restore the master database using the "From Device", selecting the master backup device from the production server backups with "Force restore over existing database" and "Leave database operational. No additional transaction logs can be restored" checked. This restores the master database. For model and msdb, I set each to single user access, then do a restore "From Device" selecting the model and msdb backup devices respectively from the production server backups with "Force restore over existing database" and "Leave database operational. No additional transaction logs can be restored" checked. This restores both the model and msdb databases. Everything up to this point appears to be OK.

Now I need to backup my user databases. I restore the last full backup that I have of the user database, and this works fine. Then I need to apply the last differential backup and here is where I am having problems. First I restrict access to the user database to owners, creators, or system admins. I open the backup device from the user database backup device file, I view the contents of the backup device and select the last differential backup in the set. In the options tab, I select "Leave database operational. No additional transaction logs can be restored" and leave unchecked “Force restore over existing database”. I click OK and I get the following error “The preceeding restore operation did not specify WITH NORECVOCERY or WITH STANDBY. Restart the restore sequence, specifying WITH NOREVOCERY or WITH STANDBY for all but the final step. RESTORE DATABASE is terminated abnormally.” I do not see where you can select WITH NOREVOCERY or WITH STANDBY. What is the problem? How are you supposed to restore the differential backup? Any ideas. There is no information about how to do this in BOL.

Thanks for any help in advance…

Matthew

[skhanal]

It's in the Options tab of Restore database dialog.

[msprygada]

In the options tab, I only see: Eject tape, Prompt before restoring each backup, Force restore over existing backup, and the three recover states: Leave operational, Leave nonoperational, and Leave read-only.

[skhanal]

Leave nonoperational is NORECOVERY mode