SQL Server 2008 R2 database stuck in Restore

Question

I have two SQL Server 2008 R2 servers one for PROD and the other for DR. I am trying to add log shipping for a database called School.

Steps so far

• Back up School database

• Restore with database using the UI or using the following SQL statement i.e.

  Restore database "School" 
  From disk ='t:\Data\School.bak' 
  with NoRecovery
  

• The result is database is stuck in Restoring

• If I restore the database with

  Restore database "School" 
  From disk ='t:\Data\School.bak' 
  With recovery
  

  The database restore completes but the log shipping fails.

• I have deleted the database and recreated it again using

  Restore database "School" 
  From disk ='t:\Data\School.bak' 
  With **NoRecovery** 
  

  but it is still stuck in Restoring state.

Is there a way that I can restore the database without having the database been stuck in the restoring state.

Answer 1

This seems like expected behavior to me. Am I misreading something?

After you've restored the database and any differential or required transaction log backups with the NORECOVERY option, you need to tell SQL Server you're done restoring files. The NORECOVERY option is specifically there to let you restore multiple files.

You should just need to run:

RESTORE DATABASE [School] WITH RECOVERY;

That will tell SQL Server you're done, and it will complete the restoration and it will no longer show up as restoring.

Answer 2

I have deleted the database and recreated it again using Restore database "School" from disk ='t:\Data\School.bak' with NoRecovery

you have to use below command as well,if you dont have any further logs

restore database databasename with recovery

some more info:

Restore with database using the UI or using the following SQL statement i.e.

next time try to issue restore statement using tsql,so that you can know status

restore database databasename from disk="path"
 with stats=5

now if you want to know indepth details on where it is and what it is doing,you can use a trace flag like below

dbcc traceon(3004,3605,-1)
GO
  restore database databasename from disk="path"
     with stats=5

this logs output to errorlog like below

2008-01-23 08:59:56.26 spid52 RestoreDatabase: Database dbPerf_MAIN
2008-01-23 08:59:56.26 spid52 Opening backup set
2008-01-23 08:59:56.31 spid52 Restore: Configuration section loaded2008-01-23 08:59:56.31 spid52 Restore: Backup set is open
2008-01-23 08:59:56.31 spid52 Restore: Planning begins
2008-01-23 08:59:56.32 spid52 Halting FullText crawls on database dbPerf_MAIN
2008-01-23 08:59:56.32 spid52 Dismounting FullText catalogs
2008-01-23 08:59:56.32 spid52 X-locking database: dbPerf_MAIN
2008-01-23 08:59:56.32 spid52 Restore: Planning complete
2008-01-23 08:59:56.32 spid52 Restore: BeginRestore (offline) on dbPerf_MAIN
2008-01-23 08:59:56.40 spid52 Restore: PreparingContainers 2008-01-23 08:59:56.43 spid52 Restore: Containers are ready

For your current problem, you can look at event log as it will log current phase

Is there a way that I can restore the database without having the database been stuck in the restoring state.

you will have to get the spid of the backup and see the wait type and troubleshoot accordingly

select * from sys.dm_Exec_requests where sessionid=backupspid