SQL Server recovery using differential backup

One of my DBAs said while trying to restore differential backup he was getting  :
Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward
and didn’t know what to do.  I am not sure if anybody else have encountered this, well I certainly have done the same mistake early in my career. I thought I will blog it so next time I can direct my DBA to this post.

Basically there are three type of recovery state for SQL Server :

Restore with recovery  – restore from backup and rollback uncommitted transaction. No further backups or logs can be applied. The database is available for read/write operations after the restore is complete.

Restore with no recovery –  restore from backup, but don’t rollback anything. Further logs or backups can be applied. The database is left in restoring mode and no read/write operations can be performed.

Restore with standby  – restore from backup, but don’t rollback any transactions, but all the undo information is kept in a file. However the database is available for read-only operations.

In order to restore from a differential backup or restore/apply transaction log, you need to first do a restore with no recovery of full backup, you need to keep restoring with no recovery option until you restore the last file which you will use restore with recovery.

Here is an example of how you would restore a differential backup :

RESTORE DATABASE   RAJUDB
FROM DISK = ‘D:\MSSQL\BACKUP\FULL\RAJUDB_full.bak’
WITH NORECOVERY;

RESTORE DATABASE  RAJUDB
FROM DISK = ‘D:\MSSQL\BACKUP\DIFF\RAJUDB_diff.bak’
WITH RECOVERY;

I hope this helps