This error occurs while I restore database from .bak file
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. (Microsoft SQL Server, Error: 3624)
After this the Database goes offline and it property sets to Single User.
Best Answer
If
SELECT @@VERSION < 10.50.1746.0
then it is possible that the issue you are coming across has been fixed in a specific cumulative update, and can be averted by updating your instance.The particular issue is described here:
KB #980832 : FIX: "A system assertion check has failed" error message...
The cumulative update is here:
Cumulative Update #4 for SQL Server 2008 R2 RTM
But I recommend applying the most recent service pack and cumulative update available (since they come with a slew of other fixes as well):
SQL Server 2008 R2 Service Pack 2
Cumulative Update #4 for SQL Server 2008 R2 Service Pack 2