1

I am getting the following error while restoring the Diff back up after restoring the Full backup in Norecovery mode.

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Error:

Msg 5052, Level 16, State 1, Line 4

ALTER DATABASE is not permitted while a database is in the Restoring state.

Msg 5069, Level 16, State 1, Line 4

ALTER DATABASE statement failed.

Location: filesystem.cpp:526

Expression: newTree.IsInArchive ()

SPID: 98

Process ID: 1956

Msg 3013, Level 16, State 1, Line 22

RESTORE DATABASE is terminating abnormally.

Msg 3624, Level 20, State 1, Line 22

A system assertion check has failed. Check the SQL Server error log for details.

Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Help me in resolving this issue.

Improve this question
1
  • What T-SQL did you run?
    – usr
    Jun 21, 2015 at 20:51

2 Answers 2

Reset to default
1

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB

This error can be caused by transient, timing-related errors, or by in-memory or on-disk data corruption.

This message also says that the backup set which you are trying to restore as differential backup is actually not consistent and SQL Server cannot bring database to a logical point if you try to apply this backup. The assertion checks are either SQL Server bugs or something corrupt in database.

You can bring database online and can run regular dbcc checkdb command and post the output

You need to also check your differential backup is consistent and you can do that by below command

restore verifyonly from disk='backup_location\backup.bak'

run above command and paste the result in question

I am unable use dbcc checkdb as the database in restoring db.Issues happens only in three instance where there is totally 6 instance. In other instance restore is working fine.

Since database is in recovery mode you cannot run any command on it that is why every command you run on it fails

Lastly can you also add output of 

sp_readerrorlog
Improve this answer
1
  • Assertion failures are mostly bugs in SQL Server. An assert is a runtime verification of a condition the programmer did not think was possible under any circumstances.
    – usr
    Jun 21, 2015 at 20:51
-1

You can simply restore the Database without any database file.

Try this:

RESTORE DATABASE [yourDatabase] WITH RECOVERY

It won't hurt your database. It will just remove the IN RESTORING-State and you can then run your DBCC CHECKDB and whatever you want.

Improve this answer
1
  • If you read OP's question he is asking that he is getting error while restoring diff backup, your command will bring database online so how diff backup would be restored. This is incorrect answer
    – Shanky
    Nov 30, 2016 at 5:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.