There are two ways to restore a differential backup in SQL Server:
- Directly use TSQL statements as described in this MSDN page
- Use SQL Server Management Studio user interface as described here.
If you are using SQL Management Studio to restore differential backups, and you have restored full backups several time using SSMS, but this is your first time to restore a differential backup then you are likely to encounter the following error:
Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)
This is because you tried to restore a differential backup on an available/operational/functional/running database which is not allowed.
In order to restore a differential backup, you will first need to restore the last full backup with NO RECOVERY option. So, in SSMS you need to select the appropriate full-backup and choose Restore With NoRecovery option from the Options page as depicted in the following screenshot.
Once restored, the database will be shown in the Object Explorer as Restoring.
Notice that the database is non-available/non-functional at this time and is waiting for a differential backup to be applied. Now, restore the appropriate differential backup and choose Restore With Recovery from the Options page:
That’s it. You have successfully restored a differential backup.
A Final Note:
Note that differential backups are cumulative and each differential backup contain changes since the last full backup, not the last differential backup. So if you have a full backup of 2009-01-01 and have differential backups for each day, and you want to restore your database to 2009-01-10, then you just need to restore the full backup (with no recovery) of 2009-01-01 followed by the differential backup of 2009-01-10. For more information, read the following MSDN articles from SQL Server books online: