August 26, 2008 at 4:06 pm
I inherited new sql servers and upon rebooting one of the servers I received an error where one of the databases was not able to be brough online because of missing files or inadequate space.
Upon researching the issue, I discovered for this particular database they had segmented the Data files into 5 physical files. The first 4 files can be located. There is no trace of the 5th and final data file.
Does anyone know a way to edit the entry in the sys.masterfiles to remove reference to the 5th data file so we can attempt to bring this database back online.
( No we haven't located any copies of previous backups!! ) I am a new DBA for this company and trying to clean up issues left from previous bad DBA's.
Any suggestions or help you can offwer would be greatly appreciated.
August 27, 2008 at 5:56 am
I don't think that just removing a datafile entry in master will fix this problem. If your data is spreaded over the 5 datafiles, you're missing 20% of your data if SQL allows you to start a database with 1 datafile missing.
Unfortunately, SQL has not the possibility to mark a datafile as disabled (like Oracle does) and let you start a database with disabled datafiles.
Wilfred
The best things in life are the simple things
August 27, 2008 at 7:24 am
Have you searched for the file across all drives on the file system? Checked the Recycle bin?
August 28, 2008 at 3:42 pm
Once again I inheritited this server and problem and have searched all drives. I haven't found the missing data file referenced in the master files. Interestingly the 5th data file doesn't even have an extension.
I have a feeling that for this particular database the client will be screwed as we can't find all the pieces and can't bring the data back online.
August 29, 2008 at 5:57 am
Maybe worthwhile looking at the server logs to try to find when the file was lost. Also, our network people take reqular back-ups of the disks...
August 29, 2008 at 6:20 am
first thing is to ascertain if anything was inside the missing file.
start by querying the sys.sysindexes table and sort by filegroup id. Get the file group id of the missing file and check if it exists in the query.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply