Have you ever tried to restore your database, but while doing so the operation is stuck in RESTORING state?

If the following steps are similar to what you did, then you can find the answer at the end of this article:

 

1. You created and ran a script similar to this in order to backup your database:
   

   BACKUP DATABASE MyDatabase
   TO DISK = 'MyDatabase.bak'
   WITH INIT -- this option will overwrite the database backup

2. Then you tried to restore the backup with a script which is something similar to this:
   

   RESTORE DATABASE MyDatabase FROM DISK = 'MyDatabase.bak' 
   WITH REPLACE -- which will force your restore over a specified database

   and from this point on you’re stuck in a RESTORING state…?

And now, what you have been waiting for, the SOLUTION of getting out of restoring state! In order to get unstuck, you need to run the following script, which will take care of bringing your database online as part of the restore process.

You can use this solution only if you are sure and you do not intend to restore any transaction log backups and want to replace a database when restoring, for example you only want to restore a database backup and be able to access the database.

RESTORE DATABASE MyDatabase
FROM DISK = 'MyDatabase.bak'
WITH REPLACE, RECOVERY -- this is the missing keyword that helps you in this situation

In case you still care about your current data and you do not want to replace a database, then the script you should be using should be the previous script without the REPLACE option, so just the RECOVERY option.

RESTORE DATABASE MyDatabase
FROM DISK = 'MyDatabase.bak'
WITH RECOVERY

You may find it easier to restore your database using the visual interface of SQL Server Management Studio wizard, in case you do not find using sqlcmd very friendly or familiar.

Using SQL Server Management Studio

The option that is responsible for the state of the database after restoration is indicated on the Options tab; set it to RESTORE WITH RECOVERY to avoid the Restoring state.

How to Take the Database out of the RESTORING State?

Using T-SQL

If the database was mistakenly put in the RESTORING state, then it is not necessary to restore the database again; it is enough to execute the following T-SQL command:

RESTORE LOG [Almarina] WITH RECOVERY;

Using SSMS

To bring the database out of the erroneously set RESTORING state, simply select “transaction Log” from the Restore menu.

Next, on the “OPTIONS” tab, make sure the option “Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)” is selected, and click OK.

Why do we need the RESTORING state?

The RESTORING state is only a problem if it is the result of incorrect options being selected, or erroneous T-SQL Commands. However, when restoring transaction log backups, this state is necessary to ensure the database remains unchanged between individual operations of restoring each transaction log backup.

First, you restore a full backup WITH NORECOVERY, leaving the database in RESTORING mode, then you restore the transaction log backups, also WITH NORECOVERY, and only the last backup is restored WITH RECOVERY – putting the database in production mode.