SQL server databases stuck in restoring state

Question

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state!

Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?

Answer 1

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore.

The database is now waiting for the latest transaction log file.

You can either:

1. Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or
2. Restore the database again, but this time using ... WITH RECOVERY; ... or
3. Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY;

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

• http://technet.microsoft.com/en-us/library/ms177446.aspx
• https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state

Answer 2

Simple T-SQL Script for Solving this problem :

write this script on New Query window and execute :

RESTORE DATABASE [DataBase Name] WITH RECOVERY;

Answer 3

I just had this situation, and the cure was quite surprising:

ALTER DATABASE DBName SET ONLINE;

Apparently the NetBackup restore that broke left it in a weird state. No other solution worked (though I hadn't yet tried restarting the SQL Server service)

I'd be careful with the database, though, as theoretically once a restore starts, then fails, you could have corrupted data. I'm just going to restore the database again anyway, so it doesn't matter to me.

Answer 4

As we know, the default database restore option is with Recovery which ensures the database is available and Online for use after completion of database restore.

Example:

RESTORE DATABASE YourDB FROM DISK= 'C:\\Data\\YourDBBackup.bak'
WITH RECOVERY
GO

Let’s see the important points about Restore with NO Recovery

• Database is not usable
• Remains in restore mode
• Next restore sequence can be done
• Doesn’t rollback any uncommitted transactions

Restore With NoRecovery

This option especially used when multiple backup are to be restored. It means, when you execute the restore command with norecovery option that means, the database is not released to the users until last backup in sequence is restored. With last backup, the Recovery option is used and database goes online.

Example:

RESTORE DATABASE YourDB FROM DISK - 'C:\\Data\\Backup_part1.bak' 
WITH NORECOVERY
GO

And then:

RESTORE LOG YourDB FROM DISK = 'C:\\Data\\BackupLog-part2.trn'
WITH RECOVERY
GO