Sql-server – Production Database went into restoring mode

sql serversql-server-2008-r2

Yesterday one of my production database went into restoring mode,but fortunately i recovered it using below query

USE MASTER
EXEC SP_CONFIGURE 'Allow updates',1
RECONFIGURE WITH OVERRIDE
GO

EXEC SP_RESETSTATUS 'DatabaseNAME'
DBCC DBRECOVER('DatabaseName')
EXEC SP_CONFIGURE 'Allow updates',0
GO

RECONFIGURE WITH OVERRIDE
GO

It brought my database online, but I want to know why it went in restoring mode, I read SQL error log but I didn't get much information about it. Is the query I used to bring the database online correct? It is used to recover the database from suspect mode.

Best Answer

(Restoring...) is typically seen under two circumstances.

  1. You're restoring a database, and have specified the WITH NORECOVERY option to allow, typically, log backups to be restored. The last restore operation would not use the WITH NORECOVERY option, thereby directing SQL Server to run the recovery process on the database, and allow client access to it.

  2. You've taken a backup of the database logfile, and have specified the WITH NORECOVERY option to ensure you have backed up the tail of the log. This option might be used to ensure no one can access the database in anticipation of migrating the database to a different server. You may inadvertently use this option when running a transaction log backup via the GUI:

    enter image description here

  3. While a database is being restored, it will show up as RESTORING.

You can test the tail log recovery scenario mentioned above in option 2 using this MCVE:

First, we create a database, and set it to FULL recovery:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'RestoringModeTest')
BEGIN
    ALTER DATABASE RestoringModeTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RestoringModeTest;
END
GO
CREATE DATABASE RestoringModeTest;
ALTER DATABASE RestoringModeTest SET RECOVERY FULL;
BACKUP DATABASE RestoringModeTest TO DISK = 'NUL:';
BACKUP LOG RestoringModeTest TO DISK = 'NUL:';
GO

This shows the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ ONLINE     ║
╚═══════════════════╩════════════╝

Here we backup the tail of the log. (NOTE, this is NOT a real backup, since the target is the NUL: device. DO NOT USE THIS IN PRODUCTION!)

BACKUP LOG RestoringModeTest TO DISK = 'NUL:' WITH NORECOVERY;

Now, when we look at the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ RESTORING  ║
╚═══════════════════╩════════════╝

This statement will cause SQL Server to run the recovery process on the database to return it to the ONLINE state:

RESTORE DATABASE RestoringModeTest WITH RECOVERY;
Related Question