Thursday, March 14, 2013

MSSQL Server : The log or differential backup cannot be restored because no files are ready to rollforward

I was facing following error while restoring Differential backup.

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) 

The reason for this erro 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 the database using full recover mode 
- Do full and differential backups. 
- Restore the DB, First from Full backup then Differential

I got the above error while trying to restore the differential backup (after you just restored the full backup). 

Reason for the error:

I restored the Full backup with Recovery option. It must be restored with NORECOVERY option to allow rest of the backup sets to be restored (Differential/Transactional)

In the Microsoft SQL Server Management Studio there are three options on the Option "page" while restoring 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 steps:

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)
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) 

If you want to restore Transactional log after Differential backup restore, then Choose "RESTORE WITH NORECOVERY" instead of "RESTORE WITH RECOVERY".

RESTORE WITH RECOVERY Needs to be the final step of a recovery process.

No comments: