Failed MSSQL Backup Restoration Due to Defunct State in a File: How to Resolve?

Question

I'm having trouble restoring an MSSQL backup because one of the files is in a defunct state. I need help fixing this to complete the restoration. Thank you! Additionally, my MSSQL backup restoration reaches 99% progress, but then it fails. I'm not sure what to do next. Any insights or suggestions on resolving this would be highly appreciated. Thank you!

I've been attempting to remove the issue to proceed with the restoration. I'm using SQL 2014 on the source machine and aiming to restore the backup on a SQL Server 2017 machine.

Additionally, I found a related issue that may be connected to my problem, and it appears to have been fixed in a Microsoft support article: KB3009576 - Cannot Restore Database with Defunct Files. However, I'm still encountering difficulties.

the command is:

restore database sampleDatabase from disk = 'D:\Customer-backup\new.bak' with move 'sample' TO 'D:\Customer-backup\sampleDatabase.mdf', move 'sample_large_db' TO 'D:\Customer-backup\sampleDatabase_large_db.ndf', move 'sample_log' TO 'D:\Customer-backup\sampleDatabase_log.ldf', STATS = 1

and the last log that i think is relevant to problem is:

Date 9/19/2023 10:11:53 AM
Log SQL Server (Current - 9/19/2023 10:18:00 AM)
Source spid73
Message External dump process return code 0x20000001.
External dump process returned no errors.

Using restore filelistonly from disk='...' command, I noticed that the 'IsPresent' value for certain NDF files is false, which could be insightful.

LogicalName	PhysicalName	Type	FileGroupName	Size	MaxSize	FileId	CreateLSN	DropLSN	UniqueId	ReadOnlyLSN	ReadWriteLSN	BackupSizeInBytes	SourceBlockSize	FileGroupId	LogGroupGUID	DifferentialBaseLSN	DifferentialBaseGUID	IsReadOnly	IsPresent	TDEThumbprint	SnapshotUrl
sample	E:\Customer-backup\sample_0.mdf	D	PRIMARY	15801843712	35184372080640	1	0	0	9B49836A-5B02-4D6B-A163-746665E8803D	0	0	12389580800	512	1	NULL	78738000000546700284	C917BFF2-276D-449A-ACB1-0887C31E6309	0	1	NULL	NULL
sample_large_db1	E:\Customer-backup\sample_0.ndf	D	LARGE_DB	0	35184372080640	3	519000001077600001	0	2497E2C6-5607-403A-A299-8762FC8B1872	0	0	0	512	2	NULL	1007000004570500045	1A03C813-A6B9-433F-8342-9A596FBFEF34	0	0	NULL	NULL
sample_large_db	E:\Customer-backup\sample\sample_1.ndf	D	LARGE_DB	2147483648	35184372080640	4	711000000500500001	0	4DA939C6-1D71-451C-A22F-B46403BF9713	0	0	2424832	512	2	NULL	78738000000546700284	C917BFF2-276D-449A-ACB1-0887C31E6309	0	1	NULL	NULL
sample_log	E:\Customer-backup\sample\sample_2.ldf	L	NULL	220790784	2199023255552	2	0	0	BFA2E756-B483-4946-BC5E-0FA7471B2D0B	0	0	0	512	0	NULL	0	00000000-0000-0000-0000-000000000000	0	1	NULL	NULL

Answer 1

I successfully resolved it by following these steps:

1. Initially, I accessed the attached database and copied its physical files to another server.
2. When attempting to attach the database to the new server, an error was encountered regarding missing *.ndf files. To address this dependency, I created a copy of an existing .ndf file and renamed it, allowing the successful attachment of the database.
3. However, while attempting to create a backup, a new error occurred with the message:

I/O error 23 (Data error (cyclic redundancy check)) detected during read at offset 0x000001ac1c4000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16\MSSQL\DATA\MoreData.mdf’.

4. To resolve this, I opted to create a new simple database and export all tables to it.