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 |
