Database stuck in "Restoring" state

Question

I backed up a database:

BACKUP DATABASE MyDatabase
TO DISK = 'MyDatabase.bak'
WITH INIT --overwrite existing

And then tried to restore it:

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE --force restore over specified database

And now the database is stuck in the restoring state.

Some people have theorized that it's because there was no log file in the backup, and it needed to be rolled forward using:

RESTORE DATABASE MyDatabase
WITH RECOVERY 

Except that, of course, fails:

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.

And exactly what you want in a catastrophic situation is a restore that won't work.

---

The backup contains both a data and log file:

RESTORE FILELISTONLY 
FROM DISK = 'MyDatabase.bak'

Logical Name    PhysicalName
=============   ===============
MyDatabase    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf
MyDatabase_log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF

Answer 1

Try any of the following solutions when stuck here:

-- Solution 1 - This one worked for me right away.
-- Recover the database manually with following command.
RESTORE DATABASE database_name WITH RECOVERY

-- Solution 2
-- Recover the database with the last log file.
RESTORE LOG database_name FROM backup_device WITH RECOVERY

-- Solution 3
-- Recover the database when bak is restored
RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

Source: https://blog.sqlauthority.com/2013/07/19/sql-server-database-in-restoring-state-for-long-time/

Answer 2

change databaseName with youre database name.

RESTORE DATABASE your DatabaseName WITH RECOVERY

Answer 3

Right Click database go to Tasks → Restore → Transaction logs In the transactions files if you see a file checked, then SQL server is trying to restore from this file.

Uncheck the file, and click OK. Database is back.

This solved the issue for me, hope this helps someone.

Answer 4

I figured out why.

If the client who issued the RESTORE DATABASE command disconnects during the restore, the restore will be stuck.

It is odd that the server, when told to restore a database by a client connection, will not finish the restore unless the client stays connected the entire time.

Answer 5

In my case, it was sufficient to drop the database which was hanging in state "Restoring..." with the SQL command

 drop database <dbname> 

in a query window.

Then I right-clicked on Databases and selected Refresh which removed the entry in the SQL Server Management Studio (SSMS). Afterwards I did a new restore which worked fine.

---

(Note that bringing it offline did not work, a restart of the SQL service did not work, a server reboot did not work as well).

Answer 6

This problem occurred for me today on a VM SQL Server. I attempted to restore a 1.8GB Database and it was stuck at 0% with ASYNC_IO_COMPLETION.

I tried multiple times, moving the .bak file to the same drive I was restoring to; even trying to restore another unrelated database that was <400MB in size.

I tried everything on this thread, with no luck.

Then I came across Restore stuck in ASYNC_IO_COMPLETION on DBA and the answer worked for me.

Enable Instant File Initialization and restart SQL Server, then retry the restore. The SSMS GUI showed a percentage progress almost instantly, as did:

Select percent_complete,* From sys.dm_exec_requests

My guess is that the storage behind the VM is very slow, or at fault.

Answer 7

In my case I just Right Click on Database then Task-->Restore-->Database-->Ok and everything became fine.

Answer 8

This is an old issue that keeps coming up all the time with SQL Server, even the latest 2019 version! I don't know why Microsoft have left this pain take hold for so long and allowed their MSSQL engine to continue behaving this way. Never the less, I propose another possible solution for those that tried the RESTORE DATABASE WITH RECOVERY option and it still didn't work.

Log into the server itself and fire up the default SSMS program on the actual database server. Next go to the "recovery" database and DELETE it. Done. Problem gone. If you needed to keep it, copy the MDF file and rename it and attach it as a new database. Worked for me on SQL Server 2008 R2.

Answer 9

If you want to restore an SQL Server database from a backup file, you can use the following script:

RESTORE DATABASE [MyDatabase] -- which database to restore
FROM DISK = N'X:\MyDatabase.bak' -- location of the database backup
WITH 
    FILE = 1, -- restore from a backup file
    -- declare where the file groups should be located (can be more than two)
    MOVE N'MyDatabase_Data' TO N'D:\SSDPATH\MyDatabase.mdf',
    MOVE N'MyDatabase_Log' TO N'E:\HDDPATH\MyDatabase.ldf',
    -- Tape option; only relevant if you backup from magnetic tape
    NOUNLOAD,
    -- brings the database online after the database got restored
    -- use this option when you don't want to restore incremental backups
    -- use NORECOVERY when you want to restore differential and incremental backup files
    RECOVERY,
    -- replace existing database with the backup 
    -- deletes the existing database
    REPLACE, 
    -- print log message for every 1 percent of restore
    STATS = 1;

Answer 10

Use the following command to solve this issue

RESTORE DATABASE [DatabaseName] WITH RECOVERY

Answer 11

Ran into a similar issue while restoring the database using SQL server management studio and it got stuck into restoring mode. After several hours of issue tracking, the following query worked for me. The following query restores the database from an existing backup to a previous state. I believe, the catch is the to have the .mdf and .log file in the same directory.

RESTORE DATABASE aqua_lc_availability
FROM DISK = 'path to .bak file'
WITH RECOVERY

Answer 12

By default, every RESTORE DATABASE comes with RECOVERY set up. The 'NORECOVERY' options, basically tells the SQL Server that the database is waiting for more restore files (could be a DIFF file and LOG file and, could include tail-log backup file, if possible). The 'RECOVERY' options, finish all transactions and let the database ready to perform transactions.

So:

1. if your database is set up with SIMPLE recovery model, you can only perform a FULL restore with NORECOVERY option, when you have a DIFF backup. No LOG backup are allowed in SIMPLE recovery model database.
2. Otherwise, if your database is set up with FULL or BULK-LOGGED recovery model, you can perform a FULL restore followed by NORECOVERYoption, then perform a DIFF followed by NORECOVERY, and, at last, perform LOG restore with RECOVERY option.

Remember, THE LAST RESTORE QUERY MUST HAVE RECOVERY OPTION. It could be an explicit way or not. In therms of T-SQL, the situation:

1.

 USE [master]
    GO
    RESTORE DATABASE Database_name 
    FROM DISK = N'\\path_of_backup_file.bak WITH FILE = 1, [REPLACE],NOUNLOAD, 
    RECOVERY -- This option could be omitted.
    GO

WITH REPLACE option must be used with caution as it can lead to data loss

Or, if you perform a FULL and DIFF backup, you can use this

   USE [master]
    GO
    RESTORE DATABASE Database_name
      FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1, 
       NOUNLOAD,NORECOVERY
    GO
    RESTORE DATABASE Database_name
      FROM DISK =N'\\path_of_**diff**backup_file.bak' WITH FILE = 1, 
     NOUNLOAD, RECOVERY
    GO

 2. USE [master]
    GO
   -- Perform a Tail-Log backup, if possible. 
   BACKUP LOG Database_name
   GO
   -- Restoring a FULL backup
   RESTORE DATABASE Database_name
    FROM DISK = N'\\path_of_backup_file.bak' WITH FILE = 1, 
     NOUNLOAD,NORECOVERY
  GO 
  -- Restore the last DIFF backup
  RESTORE DATABASE Database_name
    FROM DISK = N'\\path_of_DIFF_backup_file.bak' WITH FILE = 1,
     NORECOVERY,NOUNLOAD
  GO
  -- Restore a Log backup
  RESTORE LOG Database_name
    FROM DISK = N'path_of_LOG_backup_file.trn' WITH FILE = 2,
    RECOVERY, NOUNLOAD
  GO

Of course, you can perform a restore with the option STATS = 10 that tells the SQL Server to report every 10% completed.

If you prefer, you can observe the process or restore in real-time based query. As follow:

USE[master]
GO
SELECT session_id AS SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
        WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO

Hope this help.

Answer 13

Here's how you do it:

1. Stop the service (MSSQLSERVER);
2. Rename or delete the Database and Log files (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data...) or wherever you have the files;
3. Start the service (MSSQLSERVER);
4. Delete the database with problem;
5. Restore the database again.

Answer 14

RESTORE DATABASE {DatabaseName}
   FROM DISK = '{databasename}.bak'
   WITH REPLACE, RECOVERY

Answer 15

You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE,RECOVERY

You may have more sucess using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option.

Answer 16

What fixed it for me was

1. stopping the instance
2. creating a backup of the .mdf and .ldf files in the data folder
3. Restart the instance
4. delete the database stuck restoring
5. put the .mdf and.ldf files back into the data folder
6. Attach the instance to the .mdf and .ldf files

Answer 17

I had a similar issue with restoring using SQL Management Studio. I tried to restore a backup of the database to a new one with a different name. At first this failed and after fixing the new database's file names it was successfully performed - in any case the issue I'm describing re-occurred even if I got this right from the first time. So, after the restoration, the original database remained with a (Restoring...) next to its name. Considering the answers of the forum above (Bhusan's) I tried running in the query editor on the side the following:

RESTORE DATABASE "[NAME_OF_DATABASE_STUCK_IN_RESTORING_STATE]"

which fixed the issue. I was having trouble at first because of the database name which contained special characters. I resolved this by adding double quotes around - single quotes wouldn't work giving an "Incorrect syntax near ..." error.

This was the minimal solution I've tried to resolve this issue (stuck database in restoring state) and I hope it can be applied to more cases.

Answer 18

I have got the MyDbName (Restoring...) case because of SQL Express licensed limit.

In the log file, I found this:

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 10240 MB per database.

So if you are trying to restore a bigger database, you need to switch your SQL Express server to Developer edition for instance.

Answer 19

I had a . in my database name, and the query didn't work because of that (saying Incorrect syntax near '.') Then I realized that I need a bracket for the name:

RESTORE DATABASE [My.DB.Name] WITH RECOVERY

Answer 20

I had the same issue... although I do not know why my database experienced this problem as my drive was not full... It's like it got corrupted or something. I tried all of the above none of them fully worked, I especially thought the suggestion to stop the service and deleting the mdf and ldf files would work... but it still froze up on restore?

I ended up resolving this by deleting the files as mentioned but instead of trying to restore the DB again I copied over fresh .mdf and .ldf files and Attached these using the Front End Attachment wizard. Relief, it worked!!

It took FOREVER to copy over the new files as I am using a Virtual Machine... so copying and pasting using the clipboard took like an hour itself so I would only recommend this as a last attempt.

Answer 21

All the WITH RECOVERY based options did not work for me.

What did was to do the complete restore from Management Studio.

USE [master]
RESTORE DATABASE Sales_SSD
FROM  DISK = N'D:\databaseBackups02\Daily_Sales_20150309_0941.bak' 
WITH  FILE = 1,  
MOVE N'Sales_Data' TO N'C:\Data\SSD\Sales.mdf',  
MOVE N'Sales_Log' TO N'C:\Data\SSD\Sales_1.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

Answer 22

This may be fairly obvious, but it tripped me up just now:

If you are taking a tail-log backup, this issue can also be caused by having this option checked in the SSMS Restore wizard - "Leave source database in the restoring state (WITH NORECOVERY)"

Answer 23

I had a similar incident with stopping a log shipping secondary server. After the command to remove the server from log shipping and stopped the log shipping from primary server the database on secondary server got stuck in restoring status after the command

RESTORE DATABASE <database name> WITH RECOVERY

The database messages:

RESTORE DATABASE successfully processed 0 pages in 18.530 seconds (0.000 MB/sec).

The database was usable again after those 18 seconds.

Answer 24

WITH RECOVERY option is used by default when RESTORE DATABASE/RESTORE LOG commands is executed. If you're stuck in "restoring" process you can bring back a database to online state by executing:

RESTORE DATABASE YourDB WITH RECOVERY
GO

If there's a need for multiple files restoring, CLI commands requires WITH NORECOVERY and WITH RECOVERY respectively - only the last file in command should have WITH RECOVERY to bring back the database online:

RESTORE DATABASE YourDB FROM DISK = 'Z:\YourDB.bak'
WITH NORECOVERY
GO
RESTORE LOG YourDB FROM DISK = 'Z:\YourDB.trn'
WITH RECOVERY
GO

You can use SQL Server Management Studio wizard also:

There is also virtual restoring process, but you'll have to use 3rd party solutions. Usually you can use a database backup as live online database. ApexSQL and Idera has their own solutions. Review by SQL Hammer about ApexSQL Restore. Virtual restoring is good solution if you're dealing with large numbers of backups. Restore process is much faster and also can save a lot of space on disk drive. You can take a look on infographic here for some comparison.

Answer 25

this one did work :

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/8dd1b91d-3e14-4486-abe6-e3a550bfe457

I had a situation where my database showed restoring state and I couldn't run any queries and couldn't connect with our software.

What I did to get out of this situation is:

1. Stop all SQL related services from windows services.

2. I opened the DATA folder where the Ldf and Mdf files resides in the SQL directory, normally its like : "C:\Program Files***********\MSSQL\DATA

3. Then I copied both the Ldf and Mdf files of the database: [db name].mdf and [db name]_log.ldf

I copied both of these files to another folder.

4. Then I started all the SQL related services (in step 1) again from windows services.

5. Started my MS SQL Management studio with normal login.

6. Right click on the culprit database and hit DELETE (to delete the database at all).

7. All the LDF and MDF files related to this database have gone from DATA folder (mentioned in step 2).

8. Created a new database with the same name (same name of the one I deleted in step 6 - the culprit database).

9. Then [database name]->right click -> tasks -> Take Offline.

10. I then Copied both the files (from step 3) back to the DATA folder (step 2).

11. [database name]->right click -> tasks -> Bring Online.

Answer 26

1. Let check and run SQL Agent Service firstly.

2. Using following T-SQL:

   SELECT filename FROM master.sys.sysaltfiles WHERE dbid = DB_ID('db_name');

3. Using T-SQL continuously:

   RESTORE DATABASE FROM DISK = 'DB_path' WITH RESTART, REPLACE;

Hope this help!

Answer 27

OK, I have similar problem and exactly as it was in case of Pauk, it was caused by the server running out of disk space while restoring and so caused a permanent restoring state. How to end this state without stopping SQL Server services?

I have found a solution :)

Drop database *dbname*

Answer 28

There can also be problem deleting a stuck database if snapshot is enabled. For me this worked:

1. First I followed Tipu Delacablu steps (read a few posts up)
2. run command: drop database [your database], which will give you an error telling you the name of the snapshot database
3. run command: drop database [snapshot database], and then run the command in step 2 again.

Answer 29

I have had this problem when I also recieved a TCP error in the event log...

Drop the DB with sql or right click on it in manager "delete" And restore again.

I have actually started doing this by default. Script the DB drop, recreate and then restore.

Answer 30

I had this situation restoring a database to an SQL Server 2005 Standard Edition instance using Symantec Backup Exec 11d. After the restore job completed the database remained in a "Restoring" state. I had no disk space issues-- the database simply didn't come out of the "Restoring" state.

I ran the following query against the SQL Server instance and found that the database immediately became usable:

RESTORE DATABASE <database name> WITH RECOVERY