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
  • 5
    what's the command and exact errors you are getting Sep 18 at 11:55
  • Welcome to the DBA.SE community. Could you please edit your question and add the details (restore"" command and **error message) from the comment to the question? It makes for really better reading. Thanks.
    – John K. N.
    Sep 19 at 7:12
  • @JohnK.N. Thank you for the warm welcome to the DBA.SE community. I maked this improvement. Thanks. Sep 19 at 8:19
  • @siggemannen I improved the question with adding restore command and error message Sep 19 at 8:20
  • no one has no idea? 2 days ago


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.