SQL SERVER – Database in RESTORING State for Long Time

A very interesting question I received the other day.

“Our database has been in restoring stage for a long time. We have already restored all the necessary files there. After restoring the files we are expecting that  the database will be in operational mode, however, it is continuously in the restoring mode. Any suggestion?”

The question is very common. I sent user follow up emails to understand what is actually going on with the user. I realized after restoring their bak files and log files their database was in the restoring state because they had not restored the latest log file with RECOVERY options.

SQL SERVER - Database in RESTORING State for Long Time recoveryoption

Solarwinds

As they had completed all the database restore sequence (bak and log in order), the real need for them was to recover the database from norecovery state. User can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored.

There are three different ways to recover the database.

1) Recover the database manually with following command.

RESTORE DATABASE database_name WITH RECOVERY

2) Recover the database with the last log file.

RESTORE LOG database_name FROM backup_device WITH RECOVERY

3) Recover the database when bak is restored

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

To understand how the backup restores timeline works read Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Auditing and Profiling Database Made Easy with ApexSQL Trigger and ApexSQL Audit
Next Post
SQL SERVER – Weekly Series – Memory Lane – #038

Related Posts

20 Comments. Leave new

  • Strangely enough I had this problem for the first time today in many years of working with databases. I even tried rebooting the server and that did not stop it from being stuck. It was not due to no recovery though. It was because the database restore was interrupted before completing. What worked for me was using the following code which is in line with your suggestions.
    RESTORE DATABASE mydb
    FROM DISK = ‘Z:\SQLServerBackups\mydb.bak’
    WITH RECOVERY

    Reply
  • I have a SharePoint DB with an RBS. The DB files are about 20GB but the backup files are about 215GB.
    They are so big, I have them split into 9 different bak files. As luck would have it, the DB went corrupt and I tried a restore.
    I received:
    There is insufficient free space on disk volume ‘C:\’ to create the database.
    The database requires 277928078500 additional free bytes, while only 247033552896 bytes are available.
    Why does SQL need 260GB to do the restore? Are there any restore options I can include to push this restore through?

    Reply
  • Hello Pinal,

    I have a old database backup from 2009 nothing else. I have managed to restore it and now it’s in restoring mode. If I try to “RESTORE” database with recovery, I am getting this error “The database cannot be recovered because the log was not restored”. While doing restore from that old (2009) backup, I was getting specified cast not valid error. However i managed to restore it with Tsql. And Now I am stuck with this. Please advise.

    Taral

    Reply
  • Hi Pinal,
    I am using the below scripts to restore Differential files in the database
    ======================================
    ALTER DATABASE ABM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    — Restoring Full Backup
    RESTORE DATABASE [ABM] FROM DISK = N’C:SBUPF1′
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    GO

    — Restoring Differential
    RESTORE DATABASE [ABM] FROM DISK = N’C:SBUPD1′
    WITH FILE = 1, NoRECOVERY, NOUNLOAD, STATS = 10
    GO

    RESTORE DATABASE [ABM] with RECOVERY

    ALTER DATABASE ABM SET MULTI_USER

    =================
    But I get the below error message when T-SQL compiles the Differential script :

    Msg 3136, Level 16, State 3, Line 2
    This differential backup cannot be restored because the database has not been restored to the correct earlier state.
    Msg 3013, Level 16, State 1, Line 2
    RESTORE DATABASE is terminating abnormally.

    Do you know what is the issue ? I should mention the full backup & differential are the latest files which I made

    Reply
    • Hi Hanif,

      Why are you restoring the differential backup two times. You can restore the full backup file first with NORECOVERY option and then restore the differential backup with recovery option if there are no more log files to be restored after that

      Reply
  • Hello,
    ANy way to know who initiated the restore?
    Thanks,
    DOm

    Reply
  • I tried to recover the database using RESTORE DATABASE db_name WITH RECOVERY, but I’m still getting the following errors:

    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.

    Can you please suggest another way to make the database available for users again?

    Reply
  • I was also using RESTORE DATABASE command to restore the databases but not helped. Actually, it there were some corrupted objects in the database which were stopping from being restoration. Then, I looked for a third party tool and successfully restored the corrupt SQL server databases:

    It helped me to fix the corrupted items in SQL databases and restore it.

    Reply
  • Hi there. My question is: You restore a database from one server onto another server. All logins exist on both servers. So, what database objects do you modify after completing the restore?. Thanks !!!

    Reply
  • Hi Pinal. I am in a multi-server enviroment, and I have to set up a job to reindex the user databases. How do I execute the job against multiple servers?

    Reply
  • Pinal, when do you need to use the REPLACE switch while restoring databases? I expect that you can help me. Thanks and greetings !

    Reply
  • Mahendra Khatik
    October 11, 2014 3:13 am

    Thanks Pinal for such a helpful information !

    Reply
  • Excellent article. I will be dealing with a few of these issues as well..

    Reply
  • Excellent , it worked for me , after i restored the database it went to recovery mode with FOR LOAD , after i ran restore database db_name with recovery it came online.

    Reply
  • Hi…i face a problem when i wan restore a backup file to my sql database …it pop up a ‘expected backup description record . Exiting before completion due to errors’
    so i cant restore the database..what should i do? thank you

    Reply
  • Thanks a lot

    Reply
  • Hi, You are just awesome..

    Reply
  • Thank you!

    Reply
  • Hi,
    i’m starting restoration of 1.7TB Database is sql 2012 standard edition with 160gb compressed backup taken from simple recovery mode. but it is taking long time approx 6hrs even 5% also not completed. this is first time restoration in Drive. Drive we have 3TB storage. please share your thoughts..

    Reply

Leave a Reply

Menu