For the first time since we've gone live with our SQL 2005 database I have tried to simulate a crash recovery. We need to restore our live database on top of our development database to actualize the data so I thought it would be a nice occasion to try a normal procedure by restoring the most recent Full Backup and then all the transaction logs backups.
So I am trying to restore Database1 on top of Database2. Both databases are set to Full Recovery model and our backups procedures are as follows.
Full Database Backup every day at 6:00PM
Transaction Logs Backups every 2 hours from 1:00AM to 11:00PM using "WITH NO INIT", all backup sets are in the same file.
Differential Backup every day at 12:00PM (To make recovery faster should we have to restore in the afternoon.)
My understanding is that I need to restore my most current full backup leaving the database in norecovery mode, then apply all the transaction logs backup in sequence still in norecovery mode and then recover the database to complete the process.
I've been trying that for 2 hours now and so far I have failed miserably
Here's what I did in sequence, I'm using SQL Server Management Studio
Step 1: Restored the Full Backup. I right-clicked on Database2 and selected Task->Restore->Database.
To Database: I selected Database2
From Database: I chose Database1
Select backup sets to restore: I unchecked everything except my Full Backup (Dated yesterday 6:00PM)
Then in "Options" I checked "Overwrite existing database", in "Restore database files as:" I changed the "Restore as:" names to those of Database2 and set "recovery state" to "Leave the database non-operational...................(RESTORE WITH NORECOVERY)".
That worked, my restore went through and the database stayed in "Restoring" mode.
Step 2: Tried restoring the transaction logs backups. I right-clicked on Database2 and selected Task->Restore->Transaction Log
From previous backups of Database: Database1
Select transaction log backups to restore: I selected the first one in the list.
Options: "Leave the database....................(NORECOVERY)".
On my first attempt I had all the transaction log backups selected and got the same message. Then I tried de-selected them one at a time from bottom to top until I had only the first one selected and I'm still getting this.
Why am I getting an error about the backup sets beginning at LSN 33934 when that backup set isn't selected. It says I need a backup set that includes LSN 33742xxxxxxx which is clearly included in the set I have chosen.