As a new SQL Server user, you may encounter various challenges when it comes to managing and restoring databases. In this blog post, we will address one common error that you might encounter during the restore process and provide a solution.

Imagine you are in charge of restoring a production database while your regular DBA is on vacation. You have successfully restored the full database backup to the production server, but when you try to apply the log backup, you receive the following error:

Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

This error occurs when the database is in an online state and already active. To understand this better, let’s briefly discuss the different states of a SQL Server database:

• ONLINE: The database is available and online for data operations.
• OFFLINE: The database is offline and not accessible.
• RESTORING: The database is currently being restored.
• RECOVERING: The database is in the process of recovery.
• RECOVERY PENDING: The database is waiting for recovery to complete.
• SUSPECT: The database is in an inconsistent state and may require repair.
• EMERGENCY: The database is in a limited availability state.

When you restore a database, it is common practice to specify the keyword RECOVERY. This brings the database back online and prevents any further log backups from being applied. However, if you need to restore multiple backup files (such as a full backup followed by a differential or log backup), you cannot do so when the database is already online and active.

To restore additional backup files, you need to have the database in a state where it can accept backup data instead of online data requests. This is why, when restoring multiple backup files, you should use the NORECOVERY keyword in the RESTORE operation.

Here is an example code snippet to illustrate this:

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorksFull.bak' WITH NORECOVERY;
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorksDiff.bak' WITH RECOVERY;

It is important to note that this blog post only covers one type of error and its resolution related to the backup and restore process. If you want to dive deeper into the backup timeline and gain a better understanding of the database restore process in the full recovery model, I recommend reading my earlier post on this topic.

When working with live database backup and recovery, it is crucial to properly plan and test these scenarios on a development server. If you need further assistance or have any questions, please leave a comment below.

Stay tuned for our upcoming blog post, where we will cover Standby Server maintenance and recovery.