TL:DR
- Deleted .ndf files
- Database wouldn't load
- Set deleted files offline
- Brought database online
- Restored files from disk backup
- How can I bring them online/remove the database reference?
During a (manual) disk cleanup process while the specific database was offline a developer in our shop deleted some .ndf files from the data directory because they were believed to be no longer in use. Said database was then unable to start due to the missing files. These files were not on the primary filegroup and were part of a partition scheme based on monthly data. There are no applications (outside SQL Server) which need to access the deleted files and the bulk of them were empty.
The files were set to offline to enable the database to be brought back online. The files have since been recovered from a disk backup – not a SQL Server backup. The files themselves have not been modified in 3+ months and I would like to remove them as a matter of cleanup. The partition scheme they were part of has been removed.
A full restore is not feasible as it is a 1TB database and disk space will not allow it. Is there SQL script I can run in SSMS to either bring the files back online to remove them properly or remove the references outright? I tried to do an alter of the database/file with recovery but SQL let me know they must be offline (sys.master_files tells me they are).
Best Answer
I'm 99% sure that setting a file to offline is a one-way operation. The only route to bring them back online is to restore the data from a full backup and then apply log backups.
There may be an obscure hack route to get this working but I'd be inclined to rebuild the database. Create a new database, transfer the schema you want to keep, copy the data across.