How to recover databases which are in Restore status on SQL Server Instance - SQL Server Tutorial

Scenario:

Think about a scenario when you were restoring bunch of databases and by mistake you has with NORecovery in script or You were restoring a database and you thought that you have to restore transaction log on top of it and now you don't need to restore Transaction log but need to bring database online from Restoring State.

How to bring databases online from Restoring State in SQL Server

You can use below script to bring database from Restoring State to Online.

RESTORE DATABASE YourDatabaseName WITH RECOVERY

If you have multiple databases in Restore state and you want to bring all of them online, you can use below script. Also you can filter the databases in Select statement to include or exclude databases you want to bring online from Restoring state.

USE MASTER
GO

DECLARE @DatabaseName AS VARCHAR(128)

DECLARE Cur CURSOR
FOR
--Get list of Databases You want to Bring online from Restore State
SELECT NAME
FROM sys.databases
WHERE database_id > 4
    AND state_desc = 'RESTORING'

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQLBringOnline NVARCHAR(500) = NULL

    SET @SQLBringOnline = 'RESTORE DATABASE "' + @DatabaseName + '" WITH RECOVERY'

    PRINT @SQLBringOnline

    EXEC (@SQLBringOnline)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END

CLOSE Cur

DEALLOCATE Cur

I executed above script on my SQL Server Instance and it brought all databases online which were in Restore State on SQL Server Instance.

How to change Database state from Restore to Online in SQL Server

Bring databases online from Restore State in SQL Server - SQL Server Tutorial