Database stuck in RESTORING state

restoring stateHave 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 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 wish only 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 find do not find using sqlcmd very friendly or familiar.

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

Leave a Reply

Your email address will not be published. Required fields are marked *