4 TB restore to compressed files ends in SQL error 5171

epetro

Anyone have any luck recoverying from a 5171 error?
Is the USB drive to blame?

I'm torn between posting on SQL Backup 6 or SQL Storage Compress 5.

After 7+ hours, my test restore of a 589GB backup from USB failed with the following SQL error:
J:\NDF\Reporting01_Compress_ChargeAudit.NDFX is not a primary database file

I am attempting to restore 22 filegroups each with 1 file as a compressed database. I believe the data has been completely pulled from the .sqb file and compressed into the correct files. However, the database is now stuck in recovery, and my experience with SQL Storage Compress proves that this may never resolve itself.

The scenario thickens...
I had to use "VirtualDiskSize=1700" in order to bypass the inevitable error regarding free disk space. The 22 files are restored across 4 drives. Some of the files were restored to non-original disk drives, but I don't believe that is an issue. Each drive involved currently has a minimum of 68GB free.

I tried to run the DIFF with RECOVERY, but it failed with errors 4319.

Here is the code used to restore:(with carriage returns for readability)

EXECUTE master..sqlbackup '-SQL "
RESTORE DATABASE [Reporting01_Compress]
FROM DISK = ''F:\DatabaseBAK\REPORTING01\FULL_(local)_REPORTING01_20110709_060001.sqb''
WITH NORECOVERY
, MOVE ''REPORTING01'' TO ''K:\MDF\Reporting01_Compress.MDFX''
, MOVE ''REPORTING01_Log'' TO ''E:\LDF\Reporting01_Compress.LDFX''
, MOVE ''REPORTING01_PKEBC'' TO ''I:\IDF\Reporting01_Compress_PKEBC.NDFX''
, MOVE ''REPORTING01_MrgChargeActive'' TO ''E:\NDF\Reporting01_Compress_MrgChargeActive.NDFX''
, MOVE ''REPORTING01_ChargeActive'' TO ''K:\NDF\Reporting01_Compress_ChargeActive.NDFX''
, MOVE ''REPORTING01_LogTapeCharge_Indexes'' TO ''K:\IDF\Reporting01_Compress_LogTapeCharge_Indexes.NDFX''
, MOVE ''REPORTING01_Transactions'' TO ''J:\NDF\Reporting01_Compress_Transactions.NDFX''
, MOVE ''REPORTING01_TapeTempDemo'' TO ''J:\NDF\Reporting01_Compress_TapeTempDemo.NDFX''
, MOVE ''REPORTING01_ChargeAudit'' TO ''J:\NDF\Reporting01_Compress_ChargeAudit.NDFX''
, MOVE ''REPORTING01_RemitPostBatches'' TO ''J:\NDF\Reporting01_Compress_RemitPostBatches.NDFX''
, MOVE ''REPORTING01_LogChargeActive'' TO ''J:\NDF\Reporting01_Compress_LogChargeActive.NDFX''
, MOVE ''REPORTING01_LogInsuranceProfile'' TO ''J:\NDF\Reporting01_Compress_LogInsuranceProfile.NDFX''
, MOVE ''REPORTING01_LogWorkfileUserActivity'' TO ''E:\NDF\Reporting01_Compress_LogWorkfileUserActivity.NDFX''
, MOVE ''REPORTING01_PKDS'' TO ''E:\IDF\Reporting01_Compress_PKDS.NDFX''
, MOVE ''REPORTING01_LogTapeCharge'' TO ''I:\NDF\Reporting01_Compress_LogTapeCharge.NDFX''
, MOVE ''REPORTING01_ClaimLog'' TO ''I:\NDF\Reporting01_Compress_ClaimLog.NDFX''
, MOVE ''REPORTING01_Indexes01'' TO ''E:\IDF\Reporting01_Compress_Indexes01.NDFX''
, MOVE ''REPORTING01_ChargeAudit_Indexes'' TO ''K:\IDF\Reporting01_Compress_ChargeAudit_Indexes.NDFX''
, MOVE ''REPORTING01_LogChargeActive_Indexes'' TO ''E:\IDF\Reporting01_Compress_LogChargeActive_Indexes.NDFX''
, MOVE ''REPORTING01_ChargeActive_Indexes'' TO ''J:\IDF\Reporting01_Compress_ChargeActive_Indexes03.NDFX''
, MOVE ''REPORTING01_Transactions_Indexes'' TO ''J:\IDF\Reporting01_Compress_Transactions_Indexes.NDFX''
, MOVE ''REPORTING01_MrgTables'' TO ''E:\NDF\Reporting01_Compress_MrgTables.NDFX''"'

petey

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

epetro

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

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

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

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.

Polo Cheap

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?

epetro

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

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.

peter.peart

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

epetro

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

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

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 )