Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Restore from MDF and LDF files


  • Please log in to reply
1 reply to this topic

#1 strilorn

strilorn

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:08:04 PM

Posted 27 September 2015 - 12:06 PM

Hi everyone,

 

I am hoping that you can help me out.  I had a SQL that got destroyed by someone re-initializing the disks on a RAID array and destroying the data.  Here's the problem:

 

I have managed to restore the DB from a very old backup, and I tried replacing the MDF and LDF files with the ones that I recovered from a backup.  The problem is that the new MDF and LDF files were not detached in the copy that I have, so when I start up the SQL server EM shows the DB as Loading/Suspect.  I have tried restoring the database using sp_attach_db, sp_attach_single_file_db.  I get the error The log for database DBName is corrupt' when I use sp_attach_db, and when I use sp_attach_single_file_db I get the error 'Database 'DBName' cannot be opened because some of the files could not be activated'

 

I am at my wit's end!  I can't use the old backup (it is some months old), the RAID had the file backups (stupid place for them, same disk as the DB), and the last uncorrupted backup is the one I am using (a few months old).  I retrieved the MDF/LDF files from tape of 5 days ago, that is acceptable for loss.

 

What can I do?  Can a database be restored from an MDF file that wasn't de-attached first?

 

Help! I am putting all the points I have for this.



BC AdBot (Login to Remove)

 


#2 roccohowe

roccohowe

  • Members
  • 1 posts
  • OFFLINE
  •  
  • Local time:07:34 AM

Posted 01 October 2015 - 08:58 AM

Recommended actions for corrupt or suspect databases is a guide below and informative threads with bonus solution, which created for very complicated cases with .mdf files

 

http://answers.unity3d.com/questions/738158/how-to-repair-backup-of-mdf-file-database.html

http://www.sqlservercentral.com/Forums/Topic1602448-266-1.aspx

https://www.openfiletool.com/mdfopen.html  MDF Open File Tool

 

  1. Ensure you have a backup strategy that you can use to recover from hardware failures (including corruption). I recommend performing both database and log backup in most situations.
  2. Look in the ERRORLOG file for for your SQL Server. This will tell you what the problem is, if SQL Server encountered the problem during startup. Like for instance a missing data or log file. Don't skip this step. Say you have a problem with your car, and somebody can tell you exactly what the problem is. Wouldn't you like to know that before you start repairing the car? Same thing applies here.
  3. Do SELECT name, database_id, state_desc FROM sys.databases. State will tell you a bit more of what is the problem. You will typically see RECOVERY_PENDING if a database missing and hindering SQL Server from performing automatic recovery at startup. Some versions back, we would instead see suspect status for the database. For these situations (database file missing), you are likely not able to perform DBCC CHECKDB and if it a log file which is missing you will not be able to perform the last log backup.
  4. If you can, run DBCC CHECKDB against the database: Search Books Online and the Net for the error numbers returned. There might be specific recommendations for your error messages. The NO_INFOMSGS option of DBCC is helpful, it makes DBCC return error messages only. Example:

    DBCC CHECKDB(dbname) WITH NO_INFOMSGS
  5. Find out why this happened. Check errorlog, eventlog, do HW diagnostics etc.; search Books Online and KB for those errors.
  6. If there is a hardware problem, ensure the faulty hardware is replaced.
  7. Backup the log, if possible. This is what we sometimes call "tail-log backup", where I like to call it "the last log backup". This assumes that log backup schedule is in place. If the database is suspect or RECOVERY_PENDING, you must use the NO_TRUNCATE option for the BACKUP command. Also, you might want to do a file backup of the mdf and ldf files, for extra safety.
  8.  
    • Restore is the best thing to do now. If you managed to backup log in above step, then you will most probably have zero data loss. Then restore the latest clean database backup and the subsequent log backups, including the one taken in above step. As of SQL Server 2005, we have page level restore, meaning that we can restore only the damaged pages (instead of a full backup) and then the subsequent log backups.
    • If the database isn't suspect, then DBCC CHECKDB with a REPAIR option might be a secondary option but this will often result in loss of data. Additional solutions, depending on the errors, may be to manually rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on. In my opinion, these options are for the more experienced SQL Server DBA. If you feel uncertain, I suggest you get help.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users