In SQL Server, database recovery is an important process to ensure data consistency and safety. However, sometimes there can be a situation where the SQL Server database is stuck in the recovery state as shown below. This can happen when the recovery process fails or is interrupted, resulting in the database being unable to complete the recovery.
In this article, I will show the reasons why a SQL Server database is in recovery state and how you can access a database that is in recovery state (Restoring… ). This isn’t a common problem, but when it does, it can leave you scratching your head. In this article, I will look at different causes and specific solutions to solve this problem. Note: The solutions below should work for any version of SQL Server.
Microsoft SQL Server database in RESTORING state after Restore
Usually the restore state occurs when you are restoring a database. Here is an example of this. I will create a full database backup file ( .bak file) and a Log backup file ( .bak file) by running the T-SQL code below in SQL Server Management Studio (SSMS).
1 2 3 4 5 6 7 8 | BACKUP DATABASE [earnings] TO DISK = N'c:sqlearnings.bak' WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [earnings] TO DISK = N'C:sqlearnings_LogBackup_2018-06-02_12-42-07.bak' WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
After we have backups of SQL Server, we will start the restore process. To restore the full and backup Log files, we need to use the NORECOVERY option for the full restore. So if I just restore the full backup like this:
1 2 3 4 | RESTORE DATABASE [earnings] FROM DISK = N'c:sqlearnings.bak' WITH NORECOVERY, NOUNLOAD, STATS = 10 |
The database will now be in the Restoring state. If we forget to restore additional backups, the database will be stuck in this mode. To complete the restore and access the database, we need to execute the restore command for the Log backup file as follows:
1 2 3 4 | RESTORE LOG [earnings] FROM DISK = N'c:sqlearnings_LogBackup_2018-06-02_12-42-07.bak' |
SQL Server database in RESTORING state after backup logging with NORECOVERY . option
Another reason your database goes into recovery is when you back up the logging end using the NORECOVERY option as shown in the example below.
1 2 3 4 5 6 7 8 | BACKUP DATABASE [earnings] TO DISK = N'c:sqlearnings.bak' WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO BACKUP LOG [earnings] TO DISK = N'C:sqlearnings_LogBackup_2018-06-02_12-42-07.bak' WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07', SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10 |
This will cause the database to go into the Restoring state. To fix this problem, you can restore the database backups as shown in the example above.
Make SQL Server databases in the restored state accessible without restoring a backup
If the database is stuck in the restoring state and you don’t have an additional backup to restore, you can restore the database by using the following command:
1 2 | RESTORE DATABASE [earnings] WITH RECOVERY |
After you execute this command, the database will be available.
SQL Server database in recovery state for Database Mirroring
Another reason your database is in recovery is that it is part of SQL Server’s Database Mirroring.* Database Mirroring is a solution that allows you to have high availability for your database. If there is a problem with the primary database on the primary server, the secondary replica database on another server will take over the database operations. The main database is the Principal server, the replica database is the Mirror server and optionally you can add another Mirror server.* Below is an example. We can see the two pictures below, the one above is the Principal server where the database can be accessed. At the bottom, we can see that the Mirror is in a recovery state.
In Database Mirroring, the Mirror server is in a recovery state until a Failover is performed. To access a SQL Server database that was in a recovery state when it was part of Database Mirroring, you can perform a manual or automatic failover from Principal to Mirror.
Sometimes the database is in Restoring state after machine reboot or for another reason.
Usually this happens with large databases when a long transaction is in progress and an unexpected problem occurs such as a server shutdown or restart. If you have this problem, try the following first:
1 2 | RESTORE DATABASE [databasename] WITH RECOVERY |
If you receive an error message that the database is in use, try putting the user in single user mode:
1 2 3 4 5 6 7 8 | USE master; GO ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; |
Then try the restore command again with the restore with recovery command. After a successful restore, you can reset the multi-user mode by using the following T-SQL command:
1 2 3 4 5 6 7 | USE master; GO ALTER DATABASE Database_name SET MULTI_USER; GO |