Ablikim's Blog

If you prefer a manual repair, you can try the solutions below to solve your problem.

1 - Recover a Database That Is in The Restoring State

If the database is stuck in the restoring state and you don't have other backups to restore, you can recover the database using the following command:

RESTORE DATABASE [Database name] WITH RECOVERY

This command will make the database that is in the "restoring" state available for end-users. You can access this database again, but you are unable to restore additional backups for this database without starting all over again with the full backup.

If you receive an error that the database is in use, try to set the user to single-user mode:

USE master
GO
ALTER DATABASE Database name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Then, try the restore with the first recovery command again. 

2 - Break/Remove the Database Mirroring 

If your database is part of SQL Server Database Mirroring, it can also be stuck in the restoring state. Database Mirroring is a solution that allows you to have high availability for your database. To solve your problem, you can remove database mirroring and the database will return to normal. Here are the steps to remove database mirroring using SQL Server Management Studio:

• Step 1:During a database mirroring session, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.
• Step 2:Expand "Databases", and select the database.
• Step 3:Right-click the database, select "Tasks", and then click "Mirror". This opens the Mirroring page of the Database Properties dialog box.
• Step 4:In the "Select a Page" pane, click "Mirroring".
• Step 5:To remove mirroring, click "Remove Mirroring". Click "Yes", the session is stopped and mirroring is removed from the database.

Then the mirroring database will return to the normal state and you can back up and restore the database as a normal database.