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’ |
Categories:
