A very interesting question I received the other day.

“Our database has been in restoring stage for a long time. We have already restored all the necessary files there. After restoring the files we are expecting that  the database will be in operational mode, however, it is continuously in the restoring mode. Any suggestion?”

The question is very common. I sent user follow up emails to understand what is actually going on with the user. I realized after restoring their bak files and log files their database was in the restoring state because they had not restored the latest log file with RECOVERY options.

As they had completed all the database restore sequence (bak and log in order), the real need for them was to recover the database from norecovery state. User can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored.

There are three different ways to recover the database.

1) Recover the database manually with following command.

RESTORE DATABASE database_name WITH RECOVERY


2) Recover the database with the last log file.

RESTORE LOG database_name FROM backup_device WITH RECOVERY

3) Recover the database when bak is restored

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

To understand how the backup restores timeline works read Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology