Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
I was afraid that was the case from my failed attempts to bring them back and my previous Google searches. I am more than hesitant to hack through the sys.master_files to change state because that's just bad news. I'll look at rebuilding the database if the business case is there, but it may become a rainy day project as the time involved would outweigh the benefit for this particular database. –  thisIsSteve Sep 8 '11 at 15:39
    
Both answers sound like they would work but I don't think we actually have a proper SQL Server Backup. I'm going to accept your answer Mark because it was first. –  thisIsSteve Sep 10 '11 at 15:45

You can temporarily restore to an external HDD (need a trace flag) or a compressed NTFS folder to get back the database.

To remove the filegroup requires an online database and to run DBCC SHRINKFILE (EMPTYFILE) to flag it as unused.

There could be some hackery but a restore is the simplest way. You can't change sys tables since SQL Server 2005 either.

share|improve this answer
    
This seems pretty reasonable. I think both your solution and Marks are not very difficult and probably the safest for database integrity. Does it make a difference that we only keep a disk backup and not an actual SQL Server backup of the data drive? Would that make it more or less difficult (or even impossible)? –  thisIsSteve Sep 9 '11 at 13:11
    
Is it that you don't have a SQL Server backup of the problem files, or do you never take SQL Server backups? –  Mark Storey-Smith Sep 9 '11 at 13:31
    
To my knowledge, we don't take SQL Server backups of this particular server because we don't have the disk space. I am not in charge of backups but I do know that BackupExec is used to do full disk backups once a week. –  thisIsSteve Sep 9 '11 at 14:33
    
@thisIsSteve: you could well be in trouble then. MDFs and LDFs are locked by SQL Server: you must backup using SQL Server BACKUP. A straight file restore by a 3rd party tool won't work –  gbn Sep 10 '11 at 7:35
    
It's not mission critical data and won't really cause any problems. No real data was "lost" because it was just cleanup. Just leaves a bad taste in my mouth because there's a bit of extra junk laying around. –  thisIsSteve Sep 10 '11 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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