SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How to attach recoverd mdf!!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ali asghar
Starting Member

Iran
2 Posts

Posted - 01/30/2012 :  08:09:09  Show Profile  Reply with Quote
Hello every body
i recoverd mdf and ldf and bak files of damaged hard.
but i can't attach or restore this dates to my sql server 2008.
when i attach mdf files i see this error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
D:\Data\karaf90old.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)
------------------------------
i create a fake database and stop the sql after that rename my damage database to fake but it also didn't work!
Also when i want to restore backup this message shows!
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The media family on device 'D:\Data\rma.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
Could someone help me?

Edited by - ali asghar on 01/30/2012 08:40:18

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/30/2012 :  08:43:16  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
With that error, the file is likely damaged beyond recovery. Get out your last working database backup (proper database backup) and restore it.

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

ali asghar
Starting Member

Iran
2 Posts

Posted - 01/30/2012 :  11:17:24  Show Profile  Reply with Quote
the problem is that i need latest data because of financial problems.plz help me,i'm in trouble.
quote:
Originally posted by GilaMonster

With that error, the file is likely damaged beyond recovery. Get out your last working database backup (proper database backup) and restore it.

--
Gail Shaw
SQL Server MVP

Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/30/2012 :  12:05:01  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
With that error, that file is not going to attach. There's no magical commands, it's too badly damaged (gut feel is that the file header is damaged).

Restore from backup is about the only option here unless you want to try one of the data recovery tools that promise miracles (be careful, they don't always do as they advertise and they're not usually cheap)

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

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/30/2012 :  17:45:23  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
assuming that the file header is trashed could you:

1) Restore a backup (of the same database but older).
2) Stop sql server service.
3) Interrogate new MDF file's header
4) Write yourself a new header for the broken MDF file using (3)'s information?

I'm not suggesting this course of action by the way. I know nothing of sql server mdf headers...... I'm just curious to learn more.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/31/2012 :  04:08:35  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Good luck with that... You'd need a hex editor, an incredible knowledge of the page structures in SQL and probably a few weeks to get it right (there's the LSN to get right, the checksum to figure out, etc, etc). I know maybe 10 people who are capable of that kind of repair (I'm not one of them)

That's also assuming that it's only the first 8kb of the file that's damaged and not more.

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

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/31/2012 :  04:31:45  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Thanks for that. I was wondering however if you could get the file into a state that was attachable and then run tools to validate the db.... I didn't know how dumb or smart the header was and how much you'd need to get 100% right or not before sql server would attach.

Sounds like the aim of the game here is to try and get some data out of the db that is recent. Full recovery to a working system is a completely other bag of aquatic animals.



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/31/2012 :  07:08:22  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Again, there are probably 10 or so people in the world capable of doing what you suggest - manually fixing the header to the point the DB will attach and be repairable or queryable (assuming the first set of allocation pages and critical system tables are intact, and they are right at the beginning of the file)

Probably the only practical option here is to evaluate the data recovery tools that promise the world and hope that one lives up to its promise.

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

julialloyds45
Starting Member

USA
3 Posts

Posted - 04/15/2014 :  00:36:25  Show Profile  Reply with Quote
To avoid such a situation database expert recommend to attach a single file, but when user apply sp_attach_single_db Stored Procedures database display the given below error message gets displayed:
“Server: Msg 5172, Level 16, State 15, Line 1 The header for file path……\.mdf is not a valid database file header. The PageAudit property is incorrect.”
This error messages is occurred due to occurrence of file header of master database that gets damaged or corrupted. The error message is also occurred due to invalid value that gets inserted in the master database header file. Above all this error messages makes the database completely inaccessible and lead to corruption. This can easily be restored using unspammed. It is highly effective and efficient tool that can easily fix all types of MDF error.

Edited by - julialloyds45 on 04/15/2014 00:38:35
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 04/22/2014 :  01:15:39  Show Profile  Reply with Quote
hi Ali asghar

MS SQL Server provide 2 way to attach SQL database files to the Server. You can locate your file trough SQl Server Management Studio or through T-SQL scripts. My point of view attaching MDF file using SQL Server management studio is the simple and easy way. You have to just open SQL server Management studio and select the database option of the object explorer. Right click on it and then select the attach database option. Then suddenly a window opens and you need to click on add button and now you are able to see the list of database files select it which you want to attach and goes to Ok button. As on confirming and after and just clicking on Ok button the process complited checkout in the Databases elements list.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000