Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Lost 2 of 3 mdf files, can i read from the 1.

Author  Topic 

imtiaz mohamed
Starting Member

2 Posts

Posted - 2011-11-14 : 09:06:06
Good day all.
We have windows 2008 r2, sql 2008 r2.

Backup wasnt going through for 2 days , then on third day , tried to take differential backup, Also failed, So then inspected machine event viewer for other problems, event viewer shows disk / controller errors.

While investigating the machine just shut down.

Critical live server, so got new server prepared.

Thought we'll just move the drives from old server to the new server.
removed 3 x 2 TB drives,
verified contents of each hard disk on my machine , data was intact,
marked drives so i know what order i removed so replace on orig server.
As soon as we placed the drives into the new machine, the Drives no longer showed my info , but rather the data that was on the new servers drives.
So i had X:\ , y:\ and z:\ on damaged sever,
I replaced x from old to position of y on new ,
I replaced y from old to position of z on new and then the z from old i placed using a usb to the new server.

X and y drives were now not showing my data , but rather showing the data that was originally on the new server. Z want not affected.

i dont know if sql could have done this, can sql overwrite files , i.e. write files onto y and z , say it was expecting the files to be in those locations , then it writes the files onto them thinking they missing.

Anyway , the drive sizes seem correct , but the files in them are missing and only has the files that was on the new server show.

so now i basically had teh following
x:\data1.mdf : lost file
y:\data2.mdf : lost file
z:\data3.mdf : not lost
x:\data1_log.ldf : lost file
z:\data2_log.ldf : Not lost

So now i had no option but to go back to a backup. i only had a backup from 3 days back as the backups were not going through for the last 3 days.

I restored to 3 days agos backup.
Now i require to get the info that was on the old database for the last 3 days.
Basically , data3.mdf and data2_log.ldg was only introduced into our database due to space limitation of x and y 2 weeks earlier. So my question ?

Is it possible to link somehow to the remaining files, i.e. data3.mdf and data2_log.ldf and recover my lost information.

i have restored the db from 3 days ago and all files have restored, so i have all files: data1.mdf, data2.mdf, data3.mdf, data1_log.ldf, data2_log.ldf.

can i somehow trick sql to get the orig data3.mdf and data2_log.ldf to be part of my database so that my lost 3 days wiull be included in the database.

Thank you

Imtiaz Mohamed

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-14 : 11:08:55
No, I do not think this is possible. Unless you have the capability to read pages from a hex editor and construct records!!
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 11:48:35
Not possible. You can't mix and match files in a database.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-11-14 : 12:16:02
Is the database in Full recovery model? and if so have you got any Log backups from that period?

(Just in case you were referring to having no Full or Differential backups ... in which case maybe, just maybe, you have some Log backups?)

Or, if you are in Full recovery model then maybe (I don't know, but Gail/others almost certainly will) if you have original, unmodified, LDF files, maybe you can take a tail log backup from them.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 12:31:30
DB's gone, one of the log files was lost. Unless the entire active portion of the log was within the file that is still around (I wouldn't take any bets), no log backups. And that's assuming it was in full recovery (properly) and had log backups, and OP said "Backup wasnt going through for 2 days"

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-11-14 : 13:07:23
"and OP said "Backup wasnt going through for 2 days""

Yeah. I was kinda hoping that the LDF had been growing, and storing the log, during those 2 days and the O/P could take a tail-backup, restore the last actual backup, and Bingo! magic some data out of thin-air !
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-14 : 13:49:26
If both log files were intact, maybe. With 2 days of log (assuming full recovery) and one file missing... Not so much.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

imtiaz mohamed
Starting Member

2 Posts

Posted - 2011-11-16 : 04:13:21
Thank you for your replies.
Basically , i lost some mdf's and ldf. only have 1 x ldf and 1x mdf.
i was on a full recovery model. i restored the database from 28/10/2011 then restored a differential from 7/11/2011, then 8th and 9th transactions logs were deleted due to clearing space for taking another differential: diff backup failed due to some disk errors. i do however have the transaction log backups for the 10/11/2011 and 11/11/2011. cant restore these souse there is a break in the log chain.

The mdf has all the data for the days that i lost, i cant be sure that all teh info is in the ldf i have , as there was 2 ldf files and 1 was lost.


i guess, ive learnt the hard way, never delete a transaction log file if the latest backups are not successfull. :-(

Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-16 : 05:09:22
If you deleted the log backups after the last full backup, then there is no chance of any recovery here. Those 3 days of data are gone. Even if we could take a tail-log backup it would be useless because of the missing log backups.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -