4 TB restore to compressed files ends in SQL error 5171

• 
  Have you tried restoring using just SQL Backup, to test if the backup file is valid?

  petey July 13, 2011 21:51

  - 0 +
• 
  We do not have enough disk space for an uncompressed restore of this backup.
  I have been able to run a VERIFYONLY which returned an exitcode=0 and sqlerrorcode=0. This process takes 6 hours.
  
  I have deleted the (In Recovery)database and will make a second attempt with the same file and process.

  epetro July 14, 2011 07:30

  - 0 +
• 
  I again received this error from SQL Backup:
  
  SQL error 5171: J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
  
  I see some ads for 3rd parth software to repair(attempt) this error.
  
  Does RedGate offer anything that would assist?

  epetro July 14, 2011 14:39

  - 0 +
• 
  I was able to restore to .vmdf, .vndf, and .vldf files as a virtual restore. No errors were returned.
  
  Over the weekend I created a new backup file on a new drive. The compress restore again failed with the same erorr.
  
  SQL error 5171: J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
  
  It really looks like all the data is there as files, can I attach it or something?

  epetro July 18, 2011 14:13

  - 0 +
• 
  On top of this error, I try to run the following code and get the below error.

  RESTORE DATABASE [Reporting01_Compress] with recovery
  

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

  epetro July 18, 2011 15:32

  - 0 +
• 
  I was in a location to restore to .vmdf, .vndf, and .vldf documents like a electronic restore. No mistakes have been returned.
  
  Over the weekend I developed a producer new backup document on the producer new drive. The compress restore once more failed using the identical erorr.
  
  SQL error 5171: J:NDFReporting01_Compress_RemitPostBatches.NDFX is not really a principal databases file.
  
  It genuinely appears like each of the details is there as files, can I attach it or something?

  Polo Cheap July 18, 2011 22:08

  - 0 +
• 
  I was able to detach the 'broken' database using

  exec sp_detach_db
  

  This left the files intact. I then thought a

  CREATE DATABASE
  

  would resolve my issue, but I was met with a new error:

  Msg 823, Level 24, State 6, Line 1
  The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'K:\IDF\REPORTING01_Compress_LogTapeCharge_Indexes.NDFX'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
  

  I am now attempting a backup of the 4 TB database to a .hbc (hyperbac compress) file.
  Perhaps the restore will play nicer with it.

  epetro July 19, 2011 13:47

  - 0 +
• 
  I was unsuccessful in restoring the HBC file. The good news is that I received the same error(points for consistency).

  Msg 5171, Level 16, State 2, Line 1
  J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX is not a primary database file.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.
  

  I'm leaning towards an issue with the source database. However, I am not in a position to attempt a native backup and test restore. I may have to leave this open ended and move my trials to another dataset and server.

  epetro July 22, 2011 12:59

  - 0 +
• 
  Interesting. What happens when you try with backups of smaller DB's?
  
  Also, have you run DBCC CHECKDB on the source database at all?
  
  Pete

  peter.peart July 25, 2011 12:18

  - 0 +
• 
  I was able to execute

  DBCC CHECKDB ('REPORTING01') WITH no_infomsgs
  

  which returned only "Command(s) completed successfully."
  
  I have had great success with smaller databases(Native 600GB-) They average 82% compression. (Meaning a 500GB db now has a footprint of 90GB). I think I will inquire on the Compression forum regarding large restores now that I don't believe the backup to be at fault.

  epetro July 27, 2011 14:23

  - 0 +
• 
  I have not resolved the issue. I have only created a second thread in another forum. You are welcome to keep an eye on both items.
  The second forum is here.

  epetro July 27, 2011 23:46

  - 0 +
• 
  I found that this particular server missed a SQL Backup update. It was running 6.3.
  
  I updated to 6.5.1.9 AND UPDATED SERVER COMPONENTS
  
  The next attempt at a restore, I adjusted some file locations in the 'move to' segment.
  
  6 hours later...Success!
  
  Now on to load testing.
  (Note: I found the server components outdated when support requested a log file. I opened it and saw a 6.3.?.? So some credit goes to support )

  epetro August 03, 2011 08:47

  - 0 +

Add comment

Please sign in to leave a comment.