SQLTeam.com | Weblogs | Forums

SQL database is in RESTORING mode

sql2012

#1

Please suggest the net step:

My database stuck in restoring state.
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.


#2

How did you get to this situation


#3

I am restoring the database from .BAK file. I have full backup of my database.


#4

How are you restoring it?


#5

Do you have multiple backups?


#6

Did you cancel the RESTORE process in the middle?
If that is the case then you need to delete the database and restore it again with the fresh backup.


#7

Using SSMS.


#8

Yes, I have.


#9

I did not cancel the RESTORE Process. It is still in the same state.


#10

Do you just have a full backup or additional log backups to be restored?


#11

Restore the first backup with Restore with NORECOVERY option and last with Restore with Recovery. I guess, you are selecting the Restore with Recovery option with the first backup. It could be a reason of getting this error.


#12

Yes, It fixed the RESTORING issue but, I am unable to access my database.Now, database is in Suspect mode.


#13

Do not detach database in this situation. Set it into emergency mode
ALTER DATABASE DB_NAME SET EMERGENCY
DBCC CHECKDB (‘DB_NAME’)
Set database into single user mode:
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)
Note: be careful while using repair allow data loss option. With this, you may lose some data.
After that, you need to set your DB in multi-user mode:
DBCC CHECKDB DB_NAME SET MULTI_USER


#14

Thank you. I read about repair allow data loss option. Don’t want to try that.


#16

you should at least run this and check for any errors.

DBCC CHECKDB (‘DB_NAME’)


#17

Yes, I agree with Ahmad. You should run DBCC CHECKDB to find the database inconsistency. Repair allow data loss is last resort to fix this issue. You can also check more references available on web related to your issue.


#18

Run DBCCCHECKDB command or try REBUILD and REPAIR option if found any error in Windows Error log. Here are some more important points you can consider to repair this error:
https://www.sqlrecoverysoftware.net/blog/sql-database-in-recovery-mode.html


#19

I have already repaired the database using Stellar Repair for MS SQL. Your software did not work....