How to edit SQL Server Primary Database File .mdf

Question

I have a SQL Server Primary Database file. (.mdf)

I require a program which can open the file and let me edit some data.

Since there are many fields, it tedious to open all the records and modify it.

Would it be a good idea to do so? I can always take backup of .mdf file before playing with it, since I do not have any programming knowledge.

Answer 1

Download SQL Server Management Studio Express and write an update query to change the required fields.

I can almost guarantee that editing the MDF file directly is risky to your data and not supported in any way by Microsoft.

If you have no programming knowledge you should get someone who does to write the update query for you. Alternatively you could read up on basic SQL yourself. Most people can get working with simple SELECT and UPDATE statements quite quickly.

Here's a good simple introduction to the UPDATE statement.

Answer 2

You can't update the data in an MDF file outside of SQL Server. The file format is not disclosed, and even if you'd manage to somehow make updates in it the integrity checks would at best cause the modified tables to be marked as corrupted, at worse place the entire database offline.

There is only one tool that can open and modify MDF files: a SQL Server instance of the appropriate version, as Ash has directed you. Before doing any modification to the database, I would highly recommend making a copy of the MDF and LDF files.

Answer 3

I share your pain here ;-))... nothing like a small/lean/mean/free utility to do the the job. I'm always hunting for them. Preferably free+portable tools!!!

Definitively SQL Studio is overkill for what you want... and a huge bloatware... and only for MSSQL... so I would suggest...

Query Express (http://www.albahari.com/queryexpress.aspx) or Query ExPlus (http://sourceforge.net/projects/queryexplus)... both Free/Excellent/Small/Fast/Portable tools from Joseph Albahari... Oops, better, this works also with Oracle, ODBC and OLEDB... Great!!!

If you want to create small code snippets in (C#, F#, VB or SQL) and execute them against the database my suggestion is also a free tool from Joseph Albahari called LINQPad (http://www.linqpad.net/)... It's an Excelente small IDE for testing/prototyping code... thanks Joseph!!!

If you want a "Assisted" IDE like MSSQL Studio with some advanced features (Free) I recomend EMS SQL SQL Manager Lite... great tool... Better... You have a version for MSSQL (http://www.snapfiles.com/get/emsmssqllite.html)... and a version for MySQL (http://www.snapfiles.com/get/emsmysqllite.html).

Addenda... 2012.11.15, 17.54
Previously I forgot to mention DatabaseBrowser, a free/small/fast/portable utility that I also use from time to time, which can edit Oracle, MSSQL, ODBC, MySql, OleDB, PostgreSQL, SQLite, Interbase and Firebird. I use it mainly to edit Access/mdb, PostGRE, SQLite and Firebird. you can get a portable version here (http://www.etl-tools.com/database-editors/database-browser/overview.html)

Have fun first... productivity next!!!

ZEE

Answer 4

I think it would help if you attach the .mdf file as a database in SQL Server and then play with the records in it.

cheers