November 17, 2015 at 10:00 am
Hello,
Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent the disk to a data recovery place and looks like they can recover some of the mdf and ldf files. We are particularly interested in 1 particular database. My questions are:
1. Is this possible to restore a particular database using corresponding mdf and ldf files?
2. Do we need to have any mdfs from the system databases? Any other files we need to ask data recovery company t look for?
3. Steps and/or or best practices to restore that database into a new machine.
Thanks for your help in advance.
November 17, 2015 at 10:25 am
silas.perkins (11/17/2015)
Hello,Asking for expert opinion. A very unfortunate event happened. One of our PCs crashed few days ago. That PC had SQL Server 2008 installed. Unfortunately no backup was running on that server. We sent the disk to a data recovery place and looks like they can recover some of the mdf and ldf files. We are particularly interested in 1 particular database. My questions are:
1. Is this possible to restore a particular database using corresponding mdf and ldf files?
No, you can't restore. What you can do is ATTACH. But that's assuming these files are intact. If there were active transactions on the database when the disk crashed, the file might be corrupt even after you recover it.
2. Do we need to have any mdfs from the system databases? Any other files we need to ask data recovery company t look for?
Only if you also want to recover your jobs and your security. You can rebuild this stuff easily enough.
3. Steps and/or or best practices to restore that database into a new machine.
Thanks for your help in advance.
Follow the link above. ATTACH is the process you need to deal with.
Good luck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 17, 2015 at 10:33 am
Let me say this first. I have never heard of a case where a drive crashed, the mdf and ldf were recovered from the damaged drive and worked. Usually it's more a case that the files are recovered but won't attach, or attach but there's severe damage to the database.
So, first, prepare for potentially having lost everything that was on that machine. Assume that you're going to get nothing back. If you get anything at all back, you're lucky.
And now might be a good time to review the database backup processes on all your other servers. Not file backups, not disk images, database backups.
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
November 17, 2015 at 6:08 pm
I agree with Gail. I just didn't want to be too negative. If you have the MDF file, you can at least attempt an ATTACH. The likelihood is, everything is just gone.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2015 at 5:11 am
GilaMonster (11/17/2015)
And now might be a good time to review the database backup processes on all your other servers. Not file backups, not disk images, database backups.
I hope you don't just bypass this piece of advice. Backups are very important. If you had a backup of your databases and stored it on a different machine, this would likely be a much different conversation. If you had those backups and also picked out a random one for verification once in a while, then you would know they're viable and you would have experience with recovering from backup. In that case, this conversation wouldn't have happened and you would already have your database up and running.
Please take Gail's advice to heart. Look at your backup processes and get them in line before the next disaster occurs.
November 19, 2015 at 10:34 am
1)
You can attach the database files to another instance if you have the mdf' and ldfs ( even with mdf you will be able to attach )
System database master contains the user info, and msdb contains the jobs and other info; if you couldn't recover these files, you need to add them manually
ssms -> rightclick database -> attach ; choose the files and attach
OR
You may have to run a 'DBCC CHECKDB()' statement against the newly attached database
November 19, 2015 at 10:34 am
2)
You can copy the .mdf and .ldf files to a different server and just Attach them.
https://msdn.microsoft.com/en-us/library/ms190209.aspx
You will lose any server-wide logins created at the instance level but you will retain your database users.
November 19, 2015 at 10:41 am
3)
If the database is not open, then I think the files could be damaged. see this support article from microsoft.
If this does not help, then only have the option to try third-party program. Can in the Market to look for or can use like SQL Server Recovery Toolbox. I as the expert recommend it to you. Or can use any other tool known to you.
So I told you all the possible ways.)
November 19, 2015 at 10:43 am
Look at this resource, you might find more useful information. http://community.office365.com/en-us/f/172/t/266070.aspx
November 19, 2015 at 12:34 pm
KlaasTimmermans (11/19/2015)
( even with mdf youwillmay be able to attach )
May, not will. Without the ldf, the database might attach. Or might not. It's not guaranteed.
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
November 19, 2015 at 1:48 pm
GilaMonster (11/19/2015)
KlaasTimmermans (11/19/2015)
( even with mdf youwillmay be able to attach )May, not will. Without the ldf, the database might attach. Or might not. It's not guaranteed.
And like you pointed out yesterday, if the drive crashed, the MDF might be corrupted.
November 19, 2015 at 3:44 pm
Ed Wagner (11/18/2015)
GilaMonster (11/17/2015)
And now might be a good time to review the database backup processes on all your other servers. Not file backups, not disk images, database backups.I hope you don't just bypass this piece of advice. Backups are very important. If you had a backup of your databases and stored it on a different machine, this would likely be a much different conversation. If you had those backups and also picked out a random one for verification once in a while, then you would know they're viable and you would have experience with recovering from backup. In that case, this conversation wouldn't have happened and you would already have your database up and running.
Please take Gail's advice to heart. Look at your backup processes and get them in line before the next disaster occurs.
Backups are worthless, restores are priceless. Meaning just doing backups isn't enough, as Ed said you also need to complete a periodic restore of database backups.
November 29, 2015 at 4:34 pm
Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!
November 29, 2015 at 10:43 pm
So other people who will find this in the future and read it know, how did you solve the problem?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 30, 2015 at 3:27 pm
Grant Fritchey (11/29/2015)
So other people who will find this in the future and read it know, how did you solve the problem?
It seems like the answers marked "This worked for the OP" on page 1 of this thread must have worked for the OP, but you're right - would be good to have him post back with what exactly worked.
Thanks.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply