Edit .mdf database

[mindphasr]

Greetings,

Are there any tools freely available to modify or edit a .mdf database
file? As far as seeing the actual tables?

Regards,
cd

[David Portas]

MSDE:
http://www.microsoft.com/sql/msde/
http://www.aspfaq.com/show.asp?id=2442

--
David Portas
SQL Server MVP
--

[Simon Hayes]

No, and I'm not sure why you'd want to do this - even if you managed to
change data pages in a .mdf file directly, you would corrupt the
metadata (indexes, allocation maps, statistics etc.), and the
transaction log would also need to be updated. As far as I know, MSSQL
itself is the only tool that can read and modify .mdf files (at least
outside Microsoft).

I'm not sure what your real goal is, but if you need to retrieve data
from an .mdf file, then you can try attaching it to an MSSQL
installation with sp_attach_single_file_db. If you don't have an MSSQL
installation, you can download MSDE for free, although it's limited to
2GB databases.

If this isn't helpful, I suggest you give some more details of what
you're trying to do.

Simon

[mindphasr]

I pretty much want to be able to look at a .mdf file to see what could
be mangled in it. If for some reason the application that writes to
the database gives an error because of invalid character or something
in a certain field i would like to see what field it is and what's in
it. Simply attaching it does me no good.

[David Portas]

If you are developing an application then you could attach the DB to
Developer Edition (cost $50) to test for this sort of problem with the
benefit of Query Analyzer, Profiler and the other tools.

Is this scenario something you have actually experienced? What makes
you think that you could have a problem caused by an "invalid
character"?

If you think a database is corrupt then the DBCC command can be used to
validate a table or database and fix these problems.

--
David Portas
SQL Server MVP
--

[Simon Hayes]

I don't really understand what sort of errors you're talking about. If
you can't insert data into a table, then MSSQL will return an error
message, which you need to handle in your client:

http://www.sommarskog.se/error-handling-I.html

Perhaps if you can give a more specific example of the sort of error
you're getting, someone can suggest a solution. It would also be good
to know which version of MSSQL you have, what client application or
library you're using, what query you're executing etc.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[Erland Sommarskog]

(mi*******@gmail.com) writes:

I pretty much want to be able to look at a .mdf file to see what could
be mangled in it.
If your database is corrupt so that you cannot easily repair it with
DBCC, you should open a case with Microsoft. Editing the internal
structures of database file will just mangle it even more.
If for some reason the application that writes to the database gives an
error because of invalid character or something in a certain field i
would like to see what field it is and what's in it. Simply attaching it
does me no good.

Your application cannot on its own corrupt the database. It would
need help from either a bug in SQL Server or bad hardware.

If your application produces an error message, you should first use the
Profiler to find where what statements the application is sending. Once
there you can run the queries in Query Analyzer. In QA you can run commands
like sp_help to see the definition of tables and constraints etc.

My guess is that you run a third-party app. In such, you should open a
support case with that vendor.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp