Advertisement
Advertisement


SQL Server: 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
2013/10/14
1
569
10/14/2013 8:16:49 PM

Accepted Answer

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.

2018/11/15
438
11/15/2018 12:09:52 PM


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.
2019/05/09

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.

2014/05/19

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.

2016/11/17

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*
2012/07/30

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:

enter image description here

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.

2014/01/17

Source: https://stackoverflow.com/questions/520967
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]