Mdf File Header Page Is Corrupted †A Process Of Sql Data Retrieval That You Can Try

A Possible Problem with SQL Server
This is a possible situation that while working with MDF files of SQL Server, you might encounter this problem where your hard disk is containing a database transaction log (*.ldf file) failed. On using sp_detach_db stored procedure, database can be de-attached successfully from the SQL Server Enterprise Manager console. After trying to replace the hard drive and running the command - sp_attach_single_file_db for specifying the primary data file, corruption still prevails and you encounter following error messages:

• Msg 945, Level 14, State 2, Line 1
  
  Database ‘<database Name? cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


• Server:Msg 5105, Level 16, State 10, Line 1
  
  Device activation error. The physical file name 'path to primary data file' may be incorrect.


• Server: Msg 945, Level 14, State 1, Line 1
  
  Database your 'database name' cannot be opened because some of the files could not be activated.


• Server: Msg 1813, Level 16, State 1, Line 1
  
  Could not open new database ’your database name'. CREATE DATABASE is aborted.

• Server: Msg 945, Level 14, State 2, Line 1
  
  Database 'your database name' cannot be opened because some of the files could not be activated.


• Server: Msg 1813, Level 16, State 2, Line 1
  
  Could not open new database 'your database name'. CREATE   DATABASE is aborted.

• Shift the de-attached file to a safe location


• Delete suspect database by SQL Server Enterprise Manager console


• Make new database with same logical name and physical *.mdf and *.ldf file names as in suspect database and place all files in same directories as suspect database


• Stop the services of SQL Server


• Change a valid *.mdf file with suspected one (created in step 1 and 2)


• Start SQL Server services


• Run SQL Server Query Analyzer and then run the following script:
  
  use mastergo sp_configure 'allow updates', 1reconfigure with overridego (Use ‘allow updates’ option to specify if direct updates can be made to system tables).


• Run the following script:
  
  Select status from sysdatabases where name = 'your database name'


• Run the following script:
  
  update sysdatabases set status= 32768 where name = 'your database name'


• Restart SQL Server services


• The database you trying to recover should be visible in SQL Server Enterprise Manager console (having ‘emergency mode’ status). Enter the database and SELECT the data or use BCP to get it out. (You can encounter errors while doing this, but in most cases a large part of the data can be regained.)


• Run the following script:
  
  dbcc rebuild_log ('your database name', 'full path to a new transaction log file')


• After this command, SQL Server should sow an output: Warning: The log for database 'your database name' has been rebuilt. Pay attention that DBCC REBUILD_LOG is undocumented and unsupported SQL Server command.


• Run the following script:
  
  use 'your database name'go sp_dboption 'your database name',
  
  'single_user', 'true'go dbcc checkdb ('your database name',
  
  repair_allow_data_loss)go
  
  DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS
  
  clause performs:
  
  [list=1]


• Execute the following script:
  
  update sysdatabases set status= 0 where name = 'your
  
  database name'
  
  Execute
  
  DBCC CHECKALLOC ('your database name')
  
  and
  
  DBCC CHECKDB ('your database name')
  
  to verify database integrity.


• Execute the following script:
  
  sp_dboption 'your database name', 'single_user', 'false'go
  
  use mastergo sp_configure 'allow updates', 0go
  
  [/list]



This process can be performed in the case where primary database file contains logical errors (which cannot be recovered even by DBCC commands).
One Final Suggestion: This procedure is somehow the last chance to recover SQL data. After this, you might damage your database. Usually database backups help to solve these tricky problems. In the case, where all these efforts and procedures fail to recover your SQL data, then one viable solution in that case would be to use a third-party MS SQL Repair tool to repair corrupt MDF and to recover corrupt SQL database contents. One simple and intuitive software solution for MDF recovery is SysTools SQL Recovery tool. It can repair corrupt SQL database and perform SQL data recovery in almost all types of corruption scenarios.



Article Source: http://www.eArticlesOnline.com

About the Author:
SysTools Group develops simple and intuitive software solutions for data recovery and associated data related needs of users across continents. SysTools products surely employ advanced technology to provide unmatched results but using the tools designed at SysTools is extremely easy. SysTools SQL Recovery tool is one such easy-to-use MS SQL recovery solution to recover SQL contents from corrupt MDF files.

Tags: sql recovery, mdf file recovery, recover sql, sql recovery tool, ms sql data recovery, repair corrupt mdf, recover corrupt sql database,



Please Rate this Article

5 out of 54 out of 53 out of 52 out of 51 out of 5 

Not yet Rated


Click the XML Icon Above to Receive Writing-Articles Articles Via RSS!


Recent Related Articles From Writing-Articles


• Mssql To Mysql Database Converter Tool
  By: Rashmi | Jun 30th 2009
  MSSQL to MySQL database converter application converts MSSQL database format into MySQL database format. Convert large number of records created in MS SQL database into My SQL database server. Software maintains consistency and integrity during conversion. Database Converter program supports Unicode architecture. MSSQL data ... Read
  
  
• So What's All This About Name Your Own Price Plane Tickets?
  By: Frank Johnson | Sep 5th 2006
  We've all seen and heard those commercials on TV and radio spouting forth the glory and amazement that are name your own price plane tickets, but do any of us really know how they work? How can these travel sites (such as Priceline and Hotwire) allow travelers to name their own price on a plane ticket without going bankrupt ... Read
  
  
• Convert Mssql Database
  By: Convert MySQL MSSQL | Nov 26th 2009
  MSSQL database converter software converts MSSQL records to MySQL and maintains integrity constraints Read
  
  
• Marketing Your Business? You Gotta Grow Your Database!
  By: Alex Greer | Aug 30th 2007
  These days, building your email database is a key strategy to growing your business, both online and offline. I don't mean just focusing on having BIG numbers on your list; what you want to build is a quality database. How to do that? Read
  
  
• Sql Server 2005 Restore Master Database From Backup †Get The Tool Now!
  By: sql recovery | Jan 14th 2011
  
  Even when there is an availability of intrinsically existing data defense and RAID in SQL Server system, still SQL corruption cannot be ruled out and the need for SQL recovery cannot be completely avoided. Apparently, such incidences are common and still evident because even the best configured systems fail when the ... Read
  
  
• The â€mysql†Way Of The World
  By: tylermoon | Jan 6th 2010
  Each enhancement of the again and again points out to the fact as to why the newest version of MySQL is the perfect choice for IT professionals who want to profit from the flexibility and cost savings of open source software, but not forfeit anything relating to database reliability, performance, and ease-usability features ... Read
  
  
• Mysql Database 2 Ms Access Database Converter Utility
  By: Rashmi | Jul 28th 2009
  MySQL to MS Access Database Converter convert My SQL database tables into Microsoft Access database. Database Migration tool either converts whole database records or overwrites the existing MySQL database records. Software Work with all major versions of My SQL and MS Access database. Tool converts individual tables or mor ... Read
  
  
• Sql Server Clustering - Sql Server Mirroring Solution
  By: RyanW | Sep 19th 2009
  A server cluster allows you to connect a number of physical serversâ€"or nodesâ€"that act as failover partners for each other. The redundancy a cluster provides spells more uptime for your critical operations. Read
  
  
• Mssql To Mysql Database Migration Tool
  By: Rashmi | Jul 23rd 2009
  MSSQL server to MySQL file Converter Software convert individual tables and easy-to-use wizard-style interface. MSSQL to MySQL database conversion software overwriting the database records into existing My SQL database or save them at new location. Database conversion application supports Unicode architecture set and mainta ... Read
  
  
• Recover Sql Master Database Showing Error 3417
  By: SysTools Software | May 14th 2010
  In a situation where your SQL master database becomes corrupted, you might see error 3417 after which you might be unable to run the Server. First and foremost, you should check the availability of an updated backup. However, if no such backup is available then you should use a third-party tool. One such efficient recovery ... Read