Database state is showing as 'Restoring' for a longtime when I do with NORECOVERY option

Question

I have a two backup files (MS SQL SERVER) 1) Demo.bak 2)DemoDiff.bak

I want to restore the Base database (Demo.bak) then Differential backup (DemoDiff.bak). As I need to restore the differential database then I have to restore my base database with NORECOVERY option. But, when I restore with NORECOVERY option the database state is showing as 'Restoring' for a long time (Actually it as only 3519 KB size).

Can anyone help me out from this?

Answer 1

RESTORING is the expected state of a database after a RESTORE with NORECOVERY. You can then apply transaction log backups or a differential backup.

Recovery takes the database from RESTORING to ONLINE.

Answer 2

You can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored.

There are three different ways to recover the database.

1) Recover the database manually with following command.

RESTORE DATABASE database_name WITH RECOVERY

2) Recover the database with the last log file.

RESTORE LOG database_name FROM backup_device WITH RECOVERY

3) Recover the database when bak is restored

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY