August 14, 2008 at 10:39 pm
Hi Friends,
On my box, there is a huge DB whose size in terms of GBs. We were planning to increase the hard disk size to accommodate more data with the same DB. our system engineer asked us to take backup of all files in F drive so that we can increase space by adding another hard disc . Since this DB's data files are on F drive, i was trying to move those files from F drive to D Drive.
What i did .
-----------
1. Richt click on DB from object Explorer.
2. click on Detach
3. navigated to F:/MSSQL/DATA. Found mdf file, 7 ndf files. but no ldf file.
4. Started copying entire MSSQL folder from the above path to some other path in D Drive.
5. After copying 6 ndf files and 1 mdf file, D drive was full and consequently copy failed for 7th ndf file.
6. For sample, i tried to attach the DB from the D drive where i was successful to copy only 6 ndf files and 1 mdf file. I was successfully able to attach the DB which i detached.
7. Sicne i missed 7th ndf file, i thought of deleting some waste files from D drive so that i can copy 7th ndf file also. and some how i got sufficient space to copy the 7th ndf file from F drive to D drive.
8. Now, again i rightclicked on the new attached DB (did in 6th step above) and clicked on DELETE menu.
9. then this newly attached DB deleted from solution explorer.
10. Now when i see the F drive, i dont find the 7th ndf file.
Now when i am trying to attach the DB from either F/D drive's mdf file, i am getting a window with list of files which are missing. and consequently could not able to attach the DB.
Friends, could anybody help me at this stage please. this is very urgent. Please help me frineds.
Thanks & Regards,
Venkat
August 15, 2008 at 12:19 am
Use explorer's search to see if you can find the missing file.
When you deleted the database (step 8) SQL went and deleted all the files that are part of the database. If you didn't have a successful copy of the last file before that, it's gone. You can't usually copy a file that's part of an in-use database as SQL locks the files.
Do you have a backup of this DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2008 at 12:45 am
Hi Shaw, Thanks for your reply. I Searched the all drives, but dont find missing ndf file. But i have the backup file which is very old. using this backup file, and the rest of one mdf file and six ndf files, can i do some thing and bring the DB live ? In fact my manager is not allowing me to restore the same DB from old backup since there are several applicatins which are connecting this DB. So configuring the all those applications settings is not known to anybody in our organization. So is there any way like just restoring the DB using this old backup file and then do something to get back the DB same as that of the one which i had before my bad work i did ?
Please Help me.
Thanks & Regards,
Venkat
August 15, 2008 at 1:15 am
Not unless you have an unbroken chain of transaction logs from the time of that full backup up until now.
You can't restore the backup, then switch some of the files round. SQL will notice that and will mark the DB as suspect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply