Wednesday, November 24, 2010

The log or differential backup cannot be restored because no files are ready to rollforward.


If you have found this page, it is likely that you encountered the following error when you tried to restore a differential backup using Microsoft SQL Server 2005.

Restore failed for Server ''. (Microsoft.SqlServer.Smo)

Additional Information:
System.Data.SqlClient.SqlError: The log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

What this error is telling you is that there is no database that was left in non-operational mode, and thus has not been cleaned up such that uncommitted transactions have not been rolled back.

The easy way to reproduce this error is to backup your database using full recover model, and do full and differential backups. Once you have your full and differential backup files you, if you want to restore your database all you have to do is restore the full backup first, and then one of the differential files (differential backups have all the changes since the last full backup) that brings you up to the point you want to restore to.

You will get the above error when you try to restore the differential backup (after you just restored the full backup).

Unfortunately, you forgot one critical detail (just like I did at first). You MUST restore all but the last (in this case the full backup) with NORECOVERY option.

In the Microsoft SQL Server Management Studio there are three options on the Option "page" when you restore a database.

Option 1 (the default): Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY)

Option 2: Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY)

To properly restore a database that is backup up using full recovery mode with full and differential backups, here are the steps you need to follow to not get the above error.

Restore Full backup
  1. Open the Restore Database window in Microsoft SQL Server Management Studio
  2. Ensure the To database field is filled in with the name you want.
  3. Choose From device as the Source for restore.
  4. Choose the full backup file you want to restore. In most cases this is the most recent full backup file.
  5. Click the Options page on the left navigation.
  6. Choose Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored.(RESTORE WITH NORECOVERY). This is the most important step!!!
Restore Differential backup
  1. Open the Restore Database window in Microsoft SQL Server Management Studio
  2. Ensure the To database field is filled in with the name you want. The same that you specified in step 2 for the Restore Full backup
  3. Choose From device as the Source for restore.
  4. Choose the differential backup file you want to restore. In most cases this is the most recent differential backup file.
  5. Click the Options page on the left navigation.
  6. Choose the default: Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored.(RESTORE WITH RECOVERY) Make sure to choose this if you want to use your database after the restore.

No comments:

Post a Comment