I would like to share the correct way of restoring SQL Database when you are using both the full-backup as well as the differential-backup file to restore your DB.
As usual I started normally by restoring the DB from a file, leaving all the rest of the options to default. Later when I tried to restore my differential-backup file then following error showed up on my screen:
I contacted my IT dept soon where they guided me the correct way of restoring the DB with the differential-backup.
Difference Between Recovery and NoRecovery State while restoring DB:
NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error.
Summing up, After restoring the full back up if you want to apply further differential or log backup you cannot do that when database is online and already active. You need to have your database in the state where it can further accept the backup data and not the online data request, else there can be data inconsistency. This is the reason that when there are more than one database backup files to be restored, one has to restore the database with NO RECOVERY keyword in the RESTORE operation.
- So Firstly, While restoring the DB with the full-backup
Right Click DB -> Task -> Restore -> Database ,
Go to the Options on the top left corner of the window.
Choose the Recovery State option with “RESTORE WITH NONRECOVERY”.
or use the command:
RESTORE DATABASE SampleDB FROM DISK = 'D:\Backup\SampleDBFull.bak' WITH NORECOVERY;
- Secondly, while restoring the differential backup, follow the first step if you have many differential-backup file. When you start restoring the last differential-backup file then Choose the Recovery State option with “RESTORE WITH RECOVERY”.
or use the command:
RESTORE DATABASE SampleDB FROM DISK = 'D:\Backup\SampleDBDifferential.bak' WITH RECOVERY;
Note: Sometimes even after full restoration of DB, there is a “Restoring…” message in front of the DB.
- Run the following command:
RESTORE DATABASE SampleDB WITH RECOVERY;
I’ve wasted a lot of time struggling with this, hope this post will guide someone with the correct steps to follow.