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