CODEWITHSUNDEEP
sqlsql-server
Vicky
Vicky

Reputation: 61

How to edit SQL Server Primary Database File .mdf

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.

Upvotes: 6

Views: 55049

Answers (5)

H. Dawar
H. Dawar

Reputation: 1

If you aren't able to open the .mdf n .ldf files via SSMS and receiving errors

Use the below script in a 'new query' in SSMS to find the sql service account

Code Snippet

declare @sqlser varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

@value_name='objectname', @value=@sqlser OUTPUT

PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)

After getting the service account try the below steps to provide privilege

Right click on the .mdf/.ldf saved in your system and click on properties Click on security tab Click on Add button and add sql service account Provide modify privilege and click ok Verify both mdf and ldf have modify privilege Attach the database..

then you can easily work on the database tables.. To insert: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

To Delete: DELETE FROM table_name WHERE some_column=some_value

Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!

To Update: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value

Happy SQLing !!! :)

Upvotes: 0

ZEE
ZEE

Reputation: 3193

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

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294307

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.

Upvotes: 3

Ash
Ash

Reputation: 62106

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.

Upvotes: 10

Arnkrishn
Arnkrishn

Reputation: 30434

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

Upvotes: 1

Related Questions