Microsoft SQL: Force ReportServer database out of being stuck in recovery mode

Error: ALTER DATABASE is not permitted while a database is in the Restoring state.
Should you encounter an issue whereby a database restore operation has hung or fails to complete, leaving the database inaccessible in a restoring state, you can use the following to recover your database;
RESTORE DATABASE MyDatabase WITH RECOVERY
 
1
2
RESTORE DATABASE ReportServer
WITH RECOVERY
 
You may encounter the following error;
“The database cannot be recovered because the log was not restored.”
To resolve this, force the restore using the following to bring the database back online;
RESTORE DATABASE MyDatabase FROM DISK = ‘MyDatabase.bak’ WITH REPLACE, RECOVERY –force restore over specified database
 
1
2
3
RESTORE DATABASE ReportServer
   FROM DISK = ‘ReportServer.bak’
   WITH REPLACE, RECOVERY –force restore over specified database
 
In the event that you’ve detached the database and damaged / corrupted / lost the log file, you can also use the following to attach a single data file on its own, which will then recreate the associated log file;
exec sp_attach_single_file_db @dbname=’TEST’, @physname=’d:dataTEST1_DATA.MDF’
1
2
exec sp_attach_single_file_db @dbname=’ReportServer’,
@physname=’d:ReportServer_DATA.MDF’
 

Leave a Reply

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