Earlier today I was experiencing an issue with one of our databases called SALES_DB is in Restoring state. I tried to run below ALTER DATABASE commands on the database to set it in online state but it throws the following error.
|
1 2 |
ALTER DATABASE SALES_DB SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE SALES_DB SET ONLINE WITH ROLLBACK IMMEDIATE |
ALTER DATABASE is not permitted while a database is in the Restoring state.
The database seems to be stuck in RECOVERING state forever. Somehow database restore operation has hung or fails to complete, leaving the database inaccessible in a restoring state, usually this is because the database log is missing or corrupt.
1. First check .mdf and .ldf files are exist.
2. Check data and log drives are online and available.
3. Try to stopping and restarting the SQL service.
4. If nothing works you can use below code to recover your database;
|
1 2 3 4 5 |
USE master; GO RESTORE DATABASE SALES_DB WITH RECOVERY; |
As soon as successfully restored the database take full backup.
Neil Sep 17 , 2015 at 8:45 pm /
I don’t think I’d advise step three, restarting the services can cause corruption and put the database into suspect mode, I’ve experienced this twice with junior DBA’S panicing and restarting the services
johnson Welch Sep 07 , 2016 at 2:55 pm /
Great stuff thanks for sharing ! The SQL Server Databases Stuck in Restoring State is beginning to clear up. As i found another helpful post for the same see here: https://madhivanan.wordpress.com/2016/09/06/issue-in-recovering-a-database-that-is-in-the-restoring-state-reference/