1

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
  • 5
    what's the command and exact errors you are getting Sep 18, 2023 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, 2023 at 7:12
  • @JohnK.N. Thank you for the warm welcome to the DBA.SE community. I maked this improvement. Thanks. Sep 19, 2023 at 8:19
  • @siggemannen I improved the question with adding restore command and error message Sep 19, 2023 at 8:20
  • no one has no idea? Sep 23, 2023 at 11:59

1 Answer 1

2

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’.
  1. To resolve this, I opted to create a new simple database and export all tables to it.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.