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

User · Answer

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

User · Answer

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  I am lazy and run SSMS from my desktop remoting into all the database servers as needed   and fire up the default SSMS program on the actual database server  Next go to the  quot recovery quot  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

User · Answer

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    Stop all SQL related services from windows services  I opened the DATA folder where the Ldf and Mdf files resides in the SQL directory  normally its like    C  Program Files            MSSQL DATA 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    Then I started all the SQL related services  in step 1  again from windows services  Started my MS SQL Management studio with normal login  Right click on the culprit database and hit DELETE  to delete the database at all   All the LDF and MDF files related to this database have gone from DATA folder  mentioned in step 2   Created a new database with the same name  same name of the one I deleted in step 6 - the culprit database   Then  database name - right click -  tasks -  Take Offline  I then Copied both the files  from step 3  back to the DATA folder  step 2    database name - right click -  tasks -  Bring Online

User · Answer

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

User · Answer

Right Click database go to Tasks -- gt  Restore -- gt  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

User · Answer

What fixed it for me was    stopping the instance  creating a backup of the  mdf and  ldf files in the data folder Restart the instance delete the database stuck restoring put the  mdf and ldf files back into the data folder Attach the instance to the  mdf and  ldf files

User · Answer

Use the following command to solve this issue  RESTORE DATABASE  DatabaseName  WITH RECOVERY

User · Answer

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

User · Answer

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  lt database name gt  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

User · Answer

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

User · Answer

I figured out why   If the client who issued the RESTORE DATABASE command disconnects during the restore  the restore will be stuck   It s 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

User · Answer

Let check and run SQL Agent Service firstly  Using following T-SQL   SELECT filename      FROM master sys sysaltfiles     WHERE dbid   DB ID  db name    Using T-SQL continuously   RESTORE DATABASE  FROM DISK    DB path  WITH  RESTART  REPLACE    Hope this help

User · Answer

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  lt database name gt  WITH RECOVERY

User · Answer

Here s how you do it    Stop the service  MSSQLSERVER   Rename or delete the Database and Log files  C  Program Files Microsoft SQL Server MSSQL 1 MSSQL Data     or wherever you have the files  Start the service  MSSQLSERVER   Delete the database with problem  Restore the database again

User · Answer

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

User · Answer

Have you tried running a VERIFY ONLY   Just to make sure it s a sound backup   http   msdn microsoft com en-us library ms188902 aspx

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

There can also be problem deleting a stuck database if snapshot is enabled  For me this worked    First I followed Tipu Delacablu steps  read a few posts up  run command  drop database  your database   which will give you an error telling you the name of the snapshot database run command  drop database  snapshot database   and then run the command in step 2 again

User · Answer

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

User · Answer

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

User · Answer

RESTORE DATABASE  DatabaseName     FROM DISK     databasename  bak     WITH REPLACE  RECOVERY

User · Answer

In my case  it was sufficient to drop the database which was hanging in state  Restoring     with the SQL command    drop database  lt dbname gt     in a query window    Then I right-clicked on Databases and selected Refresh which removed the entry in Management Studio  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

[sql-server] SQL Server: Database stuck in "Restoring" state

Examples related to sql-server

Examples related to backup

Examples related to restore