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.

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 (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – Database in RESTORING State for Long Time

  1. 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

  2. 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?

  3. 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

  4. 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:\SBUP\F1′
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    GO

    — Restoring Differential
    RESTORE DATABASE [ABM] FROM DISK = N’C:\SBUP\D1′
    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

  5. 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?

  6. 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: http://www.serversdatarecovery.com/sqldatabase.html

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

  7. 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 !!!

  8. 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?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s