Error attaching database after deleting NDF file

Question 1

I have deleted .ndf file by mistake from a SQL Server 2008 database. While trying to attach a previous copy, SSMS raises this error:

Attach database failed for Server 'MDSWIN34'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\template.ndf'. 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. (Microsoft SQL Server, Error: 823)

What is the reason for this? Have I lost all of my data?

Question 2

You "accidentally" deleted a secondary data file? Do you not have a backup? If not, pretty sure you're going to be out of luck - if the .ndf file is gone, so is all the data that was on it.

Question 3

Based on all of the information you've provided so far, I'd have to say that, yes, you've lost all of your data. From what it sounds like, I can only guess that something like this happened:

your disk filled up
you saw this big .ndf file
you tried to delete it (presumably because you didn't realize it was a secondary data file)
you couldn't delete it, because it was in use
you stopped SQL Server (or detached the database)
now you could delete the file - yay?

The problem is, the .ndf file is a crucial part of your database. The fact that you couldn't have possibly deleted it without detaching the database or shutting down SQL Server should have been a clue.

Have you not taken any backups of this database since adding the .ndf file? Preferably from as close to the point that you deleted the file as possible?

Now, if you don't have a database backup (or a simple file copy of the .ndf and all the other files in a consistent state, after a clean detach), it is possible that not ALL hope is lost. You may be able to recover the file using some kind of undelete software or taking it to a data recovery service. Provided that the location on disk where the file used to live has not been zeroed out, formatted or re-occupied. You might get lucky, but odds are what you've done here is essentially the same as dropping a donut in the toilet. You might be able to get part of it back, but you still might not want it.

Unfortunately this is called learning why backups are important, the hard way. And also serves as a good warning to others that they shouldn't be deleting files on their system if they don't know what they are for.

Question 4

+1 OK. nicely explained. I want to know how this .ndf file will be created ? In my case .ndf file was located at IIS servers www.root folder How it was created in that folder? whether .ndf file default system generated or user should create it?

Question 5

That doesn't seem like a valid copy of the file. If it's zero bytes, there's no data in it, which is why you get the end of file error. Is that the only version of the .ndf file you have? Have you not taken any backups of your database (and no, a backup is not a file copy)?

Question 6

Yep, tough lesson to learn, but not much you can do about it now. Restore a backup from the server and start over on the work you lost. Bet you won't do this again. :-)

Question 7

Ya!.. I didn't realize it was a secondary data file. Now Im getting laugh by seeing your correct assumption described in 6 steps...

Question 8

Loved your answer.

Question 9

The error will indicate the data file (usually a .MDF file) and can be read or written. The offset is the physical byte offset from the beginning of the file. The logical page is obtained by dividing the offset number by 8192.

Reaching the end of the file usually means that the file is damaged. In this case, it is the TempDB data file (.mdf), not the log file (.ldf).

You can check this reference: https://learn.microsoft.com/en-us/answers/questions/133136/the-operating-system-returned-error-38.html

score 7 · Accepted Answer · 2012-02-25 06:24:08Z

Based on all of the information you've provided so far, I'd have to say that, yes, you've lost all of your data. From what it sounds like, I can only guess that something like this happened:

your disk filled up
you saw this big .ndf file
you tried to delete it (presumably because you didn't realize it was a secondary data file)
you couldn't delete it, because it was in use
you stopped SQL Server (or detached the database)
now you could delete the file - yay?

The problem is, the .ndf file is a crucial part of your database. The fact that you couldn't have possibly deleted it without detaching the database or shutting down SQL Server should have been a clue.

Have you not taken any backups of this database since adding the .ndf file? Preferably from as close to the point that you deleted the file as possible?

Now, if you don't have a database backup (or a simple file copy of the .ndf and all the other files in a consistent state, after a clean detach), it is possible that not ALL hope is lost. You may be able to recover the file using some kind of undelete software or taking it to a data recovery service. Provided that the location on disk where the file used to live has not been zeroed out, formatted or re-occupied. You might get lucky, but odds are what you've done here is essentially the same as dropping a donut in the toilet. You might be able to get part of it back, but you still might not want it.

Unfortunately this is called learning why backups are important, the hard way. And also serves as a good warning to others that they shouldn't be deleting files on their system if they don't know what they are for.

+1 OK. nicely explained. I want to know how this .ndf file will be created ? In my case .ndf file was located at IIS servers www.root folder How it was created in that folder? whether .ndf file default system generated or user should create it?
That doesn't seem like a valid copy of the file. If it's zero bytes, there's no data in it, which is why you get the end of file error. Is that the only version of the .ndf file you have? Have you not taken any backups of your database (and no, a backup is not a file copy)?
Yep, tough lesson to learn, but not much you can do about it now. Restore a backup from the server and start over on the work you lost. Bet you won't do this again. :-)
Ya!.. I didn't realize it was a secondary data file. Now Im getting laugh by seeing your correct assumption described in 6 steps...

Stack Exchange Network

Error attaching database after deleting NDF file

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

Error attaching database after deleting NDF file

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related

Hot Network Questions