Attach mdf file

Question

Attach mdf file

SQL Learner-701409

SSC Journeyman

Points: 82
More actions
June 9, 2008 at 9:26 am

#208179

I have attached .mdf files successfully to my SQL2000 server from other SQL servers using:
sp_attach_db @dbname = N'DbName', @filename1 = N'DbFileName'
where DbName is the new database name;
DbFileName is the full path of the .mdf file.
Recently, I have come across a .mdf file that I could not attach to my SQL server using this technique. The message was:
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'D:\DbName\DbFileName.mdf' is not a valid database file header. The FILE SIZE property is incorrect.
Can anyone help?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

The file header page (first page in the file) is corrupt - you will not be able to attach this directly. You *might* be able to use the hack method of creating a similar database, shutting the server down, swapping in the corrupt files, and then extracting out the data from the database using emergency mode. I'm not sure if it will work for this particular file header corruption though - as the size is checked when the file is opened.

Do you have a backup you can use instead? Where did the database come from?

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

SQL Learner-701409 SSC Journeyman Points: 82 More actions · Answer 2

I have very little info on the file as we got it off an external client's SQL server. Do I not need some basic info about the source db in order to hack it? Would the corresponding ldf file be helpful?

Thanks...

Regards,

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Just the number of files.

Thinking about it more - even if you can hack it into the system, the database still won't startup as it will hit the same problem and then shutdown again.

Unfortunately, I think your client's database is toast.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

SQL Learner-701409 SSC Journeyman Points: 82 More actions · Answer 4

Hi Paul,

Just a simple one:

I tried to attach another mdf file to my SQL2000 server and got

ERROR 602: could not find row in sysindexes for database ID 9, object ID 1, Run DBCC CHeckTABLE on sysindexes.

I suspect it is a SQL2005 database file.

Thanks..

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

Yup - looks like it.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Manoj-485464 SSCrazy Points: 2717 More actions · Answer 6

Why can't you observe it before attaching it. Must remember you should not attach or restore from higher version to lower version like from 2005 to 2000 or from 2000 to 7.0 version.

Manoj

MCP, MCTS (GDBA/EDA)