locked
Help Me Recover My Database? RRS feed

  • Question

  • I had two different databases with the same name on different computers.  I can see now that's not a good idea.  I wanted to replace the database on my local computer (DB1) with the other database (DB2) from a backup copy - lets call that copy Backup2.  So I made a backup of my local database (DB1) and we'll call it Backup1.  I then dropped DB1 and restored DB2 on my local computer from Backup1.  That worked.  Then after I worked with DB2 I dropped it and tried to restore DB1 from Backup1.  That failed with an error that the mediaset is  incomplete.  It said it is missing family 2.  I know a lot about writing SQL, but not much about administering databases.  I didn't know that a backup had mediasets or families.  I hunted around to see if I had any other backups of DB1 and I found some old copies of the mdf and ldf files from that database.

    1 - Is there any way to recover DB1 from Backup1 which has the family 2 error?

    2 - Is there any way to recover DB1 from the mdf and ldf files?


    MCSD .NET developer in Dallas, Texas

    Friday, May 23, 2014 12:29 AM

Answers

  • For your second question, if your MDF and LDF files are healthy, then you can simply attach them to your SQL Server by following the article below:

    http://msdn.microsoft.com/en-us/library/ms190209.aspx

    If in case the MDF, associated NDF and LDF files are corrupt, then you need to repair them.

    The SQL Server MDF database file format is very complex. It starts with a file header containing the meta information of the whole file, followed by several parts containing the actual data. You can find more detailed information about the SQL Server MDF database file format by searching on Google with the keyword “SQL Server MDF database file format”. If the file header containing the meta information is corrupt, or any follow-up parts are damaged or corrupted, then you will not be able to open the file correctly with SQL Server. In such a case, the first step is to try to use the built-in repair or recovery function in the SQL Server, to see if it can repair or recover the corrupt file. If not, then you need to resort to a third-party SQL Server fix software to scan the corrupted file and retrieve all recoverable data for you. It is also possible to find a data recovery expert or company to do the task, but in most of the cases, they will also use a third-party tool to do the recovery for you.

    The main difference between using the software by yourself and by a third-party service company is:
    (1) The company is familiar with the data recovery industry so he knows which software is the best one. While you need to search and compare the software online by yourself, which is time-consuming.
    (2) The company purchases the software for multiple usages, while you purchase the software for one-time usage only. So normally the service fee may be cheaper than the license fee for the software.
    (3) DIY will keep your data confidential while do the recovery via a company may cause data breach.

    If you want to perform a DIY recovery, then first you may try DBCC CHECKDB or DBCC CHECKTABLE to recover and fix them.

    If that does not work, then you can try a third party tool called DataNumen SQL Recovery to do the task. I have used it in the past and it works well.  

    Hope this helps!

    Good luck!


    • Marked as answer by DallasSteve Monday, May 26, 2014 12:20 AM
    • Edited by hzsl Wednesday, April 5, 2017 9:50 AM
    Friday, May 23, 2014 2:24 PM

All replies

  • If you have a valid DB SQL backups of DB1 and DB2., you can restore them with any name.

    If you dropped the database with out valid SQL Backups, recovery is not possible from that SQL Backup.

    To check the Validity of SQL Backup file.., with the below example.

    restore verifyonly from disk = 'D:\SQL_Test_Backups\Backup\mytest_11.bak'

    Please make sure that your backup files are valid.

    If you have old copies of MDF and LDF files, please try to attach the database from MDF and LDF files.

    EG:

    CREATE DATABASE MyAdventureWorks
        ON (FILENAME = 'C:\MySQLServer\AdventureWorks_Data.mdf'),
        (FILENAME = 'C:\MySQLServer\AdventureWorks_Log.ldf')
        FOR ATTACH;

    Thanks,


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)


    Friday, May 23, 2014 3:50 AM
    Answerer
  • Hi , I hope this article helps you....

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/abf50e00-c9b0-4809-9e61-43ed8a53e968/the-media-set-has-2-media-families-but-only-1-are-provided?forum=sqltools


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Shanky_621MVP Friday, May 23, 2014 8:07 AM
    Friday, May 23, 2014 7:32 AM
    Answerer
  • For your second question, if your MDF and LDF files are healthy, then you can simply attach them to your SQL Server by following the article below:

    http://msdn.microsoft.com/en-us/library/ms190209.aspx

    If in case the MDF, associated NDF and LDF files are corrupt, then you need to repair them.

    The SQL Server MDF database file format is very complex. It starts with a file header containing the meta information of the whole file, followed by several parts containing the actual data. You can find more detailed information about the SQL Server MDF database file format by searching on Google with the keyword “SQL Server MDF database file format”. If the file header containing the meta information is corrupt, or any follow-up parts are damaged or corrupted, then you will not be able to open the file correctly with SQL Server. In such a case, the first step is to try to use the built-in repair or recovery function in the SQL Server, to see if it can repair or recover the corrupt file. If not, then you need to resort to a third-party SQL Server fix software to scan the corrupted file and retrieve all recoverable data for you. It is also possible to find a data recovery expert or company to do the task, but in most of the cases, they will also use a third-party tool to do the recovery for you.

    The main difference between using the software by yourself and by a third-party service company is:
    (1) The company is familiar with the data recovery industry so he knows which software is the best one. While you need to search and compare the software online by yourself, which is time-consuming.
    (2) The company purchases the software for multiple usages, while you purchase the software for one-time usage only. So normally the service fee may be cheaper than the license fee for the software.
    (3) DIY will keep your data confidential while do the recovery via a company may cause data breach.

    If you want to perform a DIY recovery, then first you may try DBCC CHECKDB or DBCC CHECKTABLE to recover and fix them.

    If that does not work, then you can try a third party tool called DataNumen SQL Recovery to do the task. I have used it in the past and it works well.  

    Hope this helps!

    Good luck!


    • Marked as answer by DallasSteve Monday, May 26, 2014 12:20 AM
    • Edited by hzsl Wednesday, April 5, 2017 9:50 AM
    Friday, May 23, 2014 2:24 PM
  • It looks to me like a regular blog.

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Friday, May 23, 2014 3:36 PM
  • It looks to me like a regular blog.

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    HI Kalman,

    If you see resposne there is no need to post Datanumen recovery tool here absolutely no need. If also you did little deep you will find in each answer posted by Hzsl he is giving information about Datanumen tool. Have a look you will guage it eaisly.

    I am have already created a thread and will include this thread in consideration for blocking any such user who just posts for advertiesment


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, May 23, 2014 3:42 PM
  • Hi Kalman,

    Only we (Moderators) can see the Flagged abusive messages, for all the other users it is just like other response, therefore now your response to Shanky look like a response to the message it self:-)

    * We can use this option to pass messages to other Moderators: write the message and then delete it. we can still see the deleted messages.

    Shanky,

    I don't see any abuse in this message! It is look like useful link which is relevant to the question. By the way the word "Datanumen" dose not include in the blog one time.

    * Even if someone has a tool and that tool help in solution specific question, then what is the problem with posting it as part of other options? In this case I only see a link to a relevant link. Please check it! I check the user's history as well and sew that he already help people several time and he is look like legitimate member of the community.

    * Marking a message as offensive, should be done very carefully! 
    If the intention was positive or there is 1% chance that he actually wanted to help, then by doing so, we hurt him for no reason. There is always an option of response and ask the user not to do it again.

    ** I am not talking about offensive message that people get hurt by them! In this case it need to be delete in order to make sure no one get offensive by.


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Friday, May 23, 2014 4:38 PM
    Friday, May 23, 2014 4:11 PM
  • Thanks Ronen for looking into this thread. In my humble opinion link posted by Uri is sufficient to answer OP's question. There is no need of any recovery tool as there is no corrupt database even on posts where OP has unrecoverable corruption and someone posts recovery tool I don't mark it as Advertisement.But here question is OP is just not able to do backup restore where has recovery tool scenario come into picture. Also in some posts I have seen some promoters post questions about corrupt database and his friend posts about recovery tool and OP marks that as answer.

    I appreciate anybody who help but what is use of unnecessary comment and that to having link to some foreign tool which has no relevance to post.

    I also agree Kalman should not have posted his comment its like he says I support Numen tool.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, May 23, 2014 5:20 PM
  • For your second question, if your MDF and LDF files are healthy, then you can simply attach them to your SQL Server by following the article below:

    http://msdn.microsoft.com/en-us/library/ms190209.aspx

    If in case the MDF, associated NDF and LDF files are corrupt, then you may try DBCC CHECKDB or DBCC CHECKTABLE to recover and fix them, see the article below:

    http://www.datanumen.com/sql-recovery/articles/dbcc-checkdb.htm

    Hope this helps!

    Good luck!

    That worked!  I couldn't get the restore verifyonly to work.  It said the same error:

    Msg 3132, Level 16, State 1, Line 1

    The media set has 2 media families but only 1 are provided. All members must be provided.

    Msg 3013, Level 16, State 1, Line 1

    VERIFY DATABASE is terminating abnormally.



    MCSD .NET developer in Dallas, Texas

    Monday, May 26, 2014 12:22 AM
  • >The media set has 2 media families but only 1 are provided. All members must be provided.

    That means, in plain English, the database was backed up into 2 files.  You need to find the other file for proper database restore.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012




    Monday, May 26, 2014 12:40 AM
  • SQL server error message 3132  appears when a SQL Backup Pro backup file has been created using more than one file.

    To find out how many file were used to create a backup file,

    RUN RESTORE LABELONLY FROM DISK = '<backup_path>'.

    This command will return information about the backup file. 

    Monday, May 26, 2014 5:13 AM
  • Mark

    I ran that code and it says the backup has 2 families.  I only see one BAK file.  Would there be 2 files in the same folder?  Would they both have a BAK extension?  If there is only one file does that mean I didn't complete the backup process?  Maybe I missed a CONTINUE button on the restore wizard.

    Thanks

    Steve


    MCSD .NET developer in Dallas, Texas

    Monday, May 26, 2014 2:16 PM
  • Hi,

    Using several files in backup is done mostly for faster backup operation. In this case it is obviously better to use several files in different disks, therefore it is probably will not be in the same folder :-)

    You need to find the other file location in order to restore the database. Check if this blog help you in that:
    http://www.mssqltips.com/sqlservertip/2960/sql-server-backup-paths-and-file-management/

    Or just execute this query ON THE SERVER THAT MAKE THE BACKUP:

    select b.database_name,a.physical_device_name 
    from msdb.dbo.backupmediafamily a
    join msdb.dbo.backupset b
    on (a.media_set_id=b.media_set_id)
    where database_name='AdventureWorks2012' 


    [Personal Site] [Blog] [Facebook]signature

    • Edited by pituachMVP Monday, May 26, 2014 8:31 PM
    Monday, May 26, 2014 8:02 PM
  • I ran that query and it came up empty.  I tried just viewing the backupset table and it is empty (the other table, too).  So I guess the backup failed, but it seems odd to em that it did generate 1 unusable .BAK file.

    MCSD .NET developer in Dallas, Texas

    Monday, May 26, 2014 8:59 PM
  • Would there be 2 files in the same folder?  Would they both have a BAK extension?  

    It may not in same folder but several files can have .BAK file extension.
    Tuesday, May 27, 2014 4:50 AM
  • I ran that query and it came up empty.  I tried just viewing the backupset table and it is empty (the other table, too).  So I guess the backup failed, but it seems odd to em that it did generate 1 unusable .BAK file.

    MCSD .NET developer in Dallas, Texas


    Is this still a issue you already marked the answer

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Tuesday, May 27, 2014 10:18 AM
  • Yes, and No.  I was able to restore an old copy of my database by attaching the MDF, LDF files.  Still I lost the latest copy of the database in the failed backup.  If someone can help me understand where the second backup file might be or why it failed, that would be good, too.  I did a search of my entire machine for all files with the extension .BAK and it appears that only one file was created by the backup process although now it is asking for a second family file.  I don't know where that file might be located.


    MCSD .NET developer in Dallas, Texas

    Tuesday, May 27, 2014 1:27 PM
  • Hi,

    We are groping in the dark in many ways, when we are using the forum. We can not see and monitor anything, but only give you information based ob your information. There is a chance that supporters from the forum were already found the missing file, if they could monitoring your server :-) but here lot of times the solution is more complex. There is an option that the file is gone,  and there is no option to restore it :-(

    It is not that we dont want to help you :-)
    But according the thread's information I am not sure I have any other idea, and I will recommend to start mourning on the file :-)

    You can learn from this to next time and remmember that backups a database are not declared as succes till you check that you can restore it.


    [Personal Site] [Blog] [Facebook]signature

    Tuesday, May 27, 2014 6:01 PM
  • Ronen

    "I will recommend to start mourning on the file"

    That made me laugh.  Yes, I think there is a better chance that we will find flight MH370 at this point. 

    "database backups are not declared as succes till you check that you can restore it" 

    Lesson learned.

    Steve


    MCSD .NET developer in Dallas, Texas


    Tuesday, May 27, 2014 6:35 PM
  • They used to talk about this flight all the time at CNN, but for about a month they didn't mention it a single time. I am wondering if any new development occurs.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, May 27, 2014 6:43 PM
  • Thanks :-)

    * You can close the thread I think (by marking aswers and voting helpful responses)


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, May 28, 2014 8:31 PM
  • Just today, in the Microsoft BI User Group Meeting, The lecturer mentioned the way black box work in airplanes, and someone mentioned it :-)


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, May 28, 2014 8:35 PM
  • I did mark an answer, several days ago, and it's still marked.  I think it was the third reply.

    MCSD .NET developer in Dallas, Texas

    Wednesday, May 28, 2014 8:36 PM