Dears;

I have a Full backup of some DB of around 417 GB within SQL Server 2008 R2 , and when I tried to restore it , i get  stuck several times with the error below :

Restore failed for Server   (Microsoft.SqlServer.SmoExtended)

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 

Although I have restored  successfully other several Full+ Deferential backups for the same DB while it was in SQL Server 2008 not 2008 R2.

And so I have rounded all different possible assumptions for such common know error by Microsoft for SQL Server 2000, 2005, 2008 but  no way as next:

1-      I have attempted to restore it within 2 environments clustered and non-clustered of SQL Server 2008 R2.

2-      I have attempted to restore verify it and it was so fine .

3-      I have attempted to restore it using Continue_after_error ,no_checksum but no way.

4-      I have attempted to move Backup file to another Storage but no way.

5-      I have scheduled jobs to DBCC  Checkdb the live DB and it wasn’t any errors since long time right now

6-      I have also DBCC check all master +msdb+model+Tempdb ,and they are so fine

7-      I have also checked all I/O subsystem of SAN and Network controllers , but they are fine evidenced much more by my trial on 2 different environments.

8-      I have also checked Memory and it is so fine all the time since I have 128 GB memory and I control it also so well.

For DB Design +Structure :

·         It has 20 Files containing tables partitioned horizontal +Vertically  (Schema Partitioning +File Stream partitioning ) through these 20 files.

·         I have Data compression of the required tables only.

·         I have one table of more than 300 GB alone which is partitioned as mentioned above.

·         I have implemented Read Committed Snapshot isolation level using row versioning integrated so well with File stream technology since it is a new feature of SQL 2008 R2.

·         DB is in simple recovery model for garbage collection of all file stream data as a result of much Delete /update in images themselves.

 So any one has any idea about such case..How to solve it…?

1. Which SQL Server edition Workgroup/Std/EE is the live and restore environments?

2. Do you have Filestream enabled on restore SQL Server?

3. Which version (eg. 10.50.xxxx) are source and restore SQL Servers?

4. What is the error in the SQL Server log when the backup fails?

5. Is there a mini-dump in the log directory?

Please provide the results of SELECT @@VERSION. 

Also, I would start by installing the latest cumulative updates, if you haven't already.

http://support.microsoft.com/kb/981356/

to know the details about your SQL Server , please run

xp_msver in the manangement studio

and run xp_readerrorlog to know the complete error

Hi PradyothanaDP,

I dont want to offend you, but am sorry to point that the replies you are doing are not going to help any one as all these posts are older by atleast 2-3 years. Unless you are doing this by mistake, I am not sure what your intentions are, but it just helps to create noise.

Hi Ashwin,

When i searched with no answers these are listed out. So i thought this is still existing. Now on wards let me check the data and time of latest reply. Thanks for guidance.

It was happening by oversight. Thanks for guidance.

:)

Hi Pradyothana,

I felt that you were replying without knowing that it was old threads and that is the reason I thought of letting you know. Threads older than 1 year should get locked by default , so these confusion never happens :)