SQL Server database stuck on "Restoring..."

Question

I wanted to restore an SQL Server database to a new database so I could take a look at what records are in the backup. I have looked at articles restoring to the same database and that's not what I want.

The script finished running but the original database states "Restoring..." and is inaccessible. I used SSMS with the resultant script being:

USE [master]
RESTORE DATABASE [WeatherTempo] 
FROM  DISK = N'E:\Weather_backup_2023_09_15_000032_3924540.bak' 
WITH  FILE = 1,  
MOVE N'Weather' TO N'F:\WeatherTempo.mdf',  
MOVE N'Weather_log' TO N'F:\WeatherTempo_log.ldf',  
NOUNLOAD,  STATS = 5

GO

So now I look and this is what I see in SSMS

Weather (Restoring...)
WeatherTempo

This has been going on for about an hour now. Is this normal? Do I now need to do something else to bring the original back online? The source MDF is about 6GB and the LDF is about 30GB.

Answer 1

It does not seem normal. You can use this manual query:

RESTORE DATABASE Weather WITH RECOVERY

It maybe helpful to set it to be single user if it is in use and preventing recovery process:

USE master;
GO
ALTER DATABASE Weather SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Return it back to multi-user after completing the recovery process:

USE master;
GO
ALTER DATABASE Weather SET MULTI_USER;