Microsoft SQL Database Stuck in Restoring Mode

These are the possible resolutions from highest to lowest recommendations

-- Normal method of restoring a database - unlikely to fix this issue
RESTORE DATABASE TestOrg_MSCRM WITH RECOVERY

-- Sample output
/*
Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Completion time: 1900-02-30T21:30:08.9829634-07:00
*/

-- Try to put database in Single User mode
USE master;
GO
ALTER DATABASE TestOrg_MSCRM
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
-- If successful
USE master;
GO
ALTER DATABASE TestOrg_MSCRM
SET MULTI_USER;
GO

-- Sample output
/*
Msg 5052, Level 16, State 1, Line 3
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 3
ALTER DATABASE statement failed.
Completion time: 1900-02-30T21:31:02.8571497-07:00
*/

-- Test restoring database from an MDF, LDF, DAT files
EXEC sp_attach_db @dbname = N'TestOrg_MSCRM',
@filename1 = N'X:\SQL\TestOrg_MSCRM.mdf',
@filename2 = N'X:\SQL\TestOrg_MSCRM.ldf',
@filename3 = N'X:\SQL\TestOrg_MSCRM_dat1.ndf'

-- Last Resort Solution: Restore the Database using its prior backups
RESTORE DATABASE TestOrg_MSCRM 
FROM DISK = 'Z:\Backup\TestOrg_MSCRM.bak'
WITH REPLACE, RECOVERY --force restore

-- Sample successful message
/*
Processed 225240 pages for database 'TestOrg_MSCRM', file 'mscrm' on file 1.
Processed 1 pages for database 'TestOrg_MSCRM', file 'mscrm_log' on file 1.
RESTORE DATABASE successfully processed 225241 pages in 7.261 seconds (242.348 MB/sec).
Completion time: 1900-02-30T21:44:32.8200046-07:00
*/

Leave a Reply

Your email address will not be published. Required fields are marked *