How to bring database online from restoring state in SQL Server

[mithileshtata]

To bring a SQL Server database online from a "Restoring" state, you'll need to complete the restore process. Typically, a database remains in the "Restoring" state when you're applying multiple transaction log backups after a full backup, or when you intend to apply differential backups or additional log backups. Here's how you can bring the database online:

SQL Server Database Stuck in Restoring State - Easy Fix

Option 1: Using T-SQL​

If you have finished restoring all necessary backups, you can bring the database online by running the following T-SQL command:

RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;

This command finalizes the recovery process and brings the database online.

Option 2: Using SQL Server Management Studio (SSMS)​

1. Restore the Database:
   
   • Right-click the database that is in the "Restoring" state in the Object Explorer.
   • Choose Tasks > Restore > Transaction Log... (if you're restoring a log backup).
   • In the restore dialog, under "Select the backup sets to restore," check the backups you want to restore.
2. Complete the Restore:
   
   • Ensure that under the "Options" section, the "Recovery state" is set to RESTORE WITH RECOVERY. This option is selected by default to bring the database online after the restore process.
   • Click OK to complete the restore process and bring the database online.

Important Notes:​

• No Recovery: If you still need to apply more backups (e.g., additional differential or log backups), the database should remain in the "Restoring" state until all backups are applied.
• Read-Only: If you're doing a "Restore with Standby," the database will be in a read-only mode, and you must use the RESTORE WITH RECOVERY command to bring it fully online.

After running the command or completing the steps, your database should be online and accessible for use.

You can also try SQL Recovery Software