6

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.

Ash
  • 60,973
  • 31
  • 151
  • 169
Vicky
  • 61
  • 1
  • 1
  • 2
  • 2
    Do you want something other than SQL Server to modify the file, because thats pretty much **what** sql server is for. – GrayWizardx Dec 29 '09 at 03:26
  • Yes Gray, I want something other than SQL Server since I do not have programming knowledge. I just want to update some data, instead of going to each and every record by software made using SQL Server. – Vicky Dec 29 '09 at 04:07

5 Answers5

10

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.

Ash
  • 60,973
  • 31
  • 151
  • 169
  • Ash, I suppose my file is in SQL Server 2008, SQL Server Management Studio Express (Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.). Would it be ok to use it? Secondly, can i install it on the same PC where my software and SQL Server 2008 is installed? btw, thanks for the answer...I will try I think it should help. – Vicky Dec 29 '09 at 04:06
  • Vicky, there is a SQL Server 2008 version available here: http://www.microsoft.com/downloads/details.aspx?familyid=08E52AC2-1D62-45F6-9A4A-4B76A8564A2B&displaylang=en – Ash Dec 29 '09 at 04:08
  • You can install it on the same PC without any problems. However if you already have SQL Server 2008, you should also already have the full version of SQL Management Studio 2008. Check for it in your start menu. – Ash Dec 29 '09 at 04:11
  • Thank you Ash. SQL Server 2008 came with the company who made my personalised software. In Start Menu, there is Import and Export Data (64 Bit), Configuration Tools -> SQL Server Configuration manager, Error and Usage Reporting, Installation Center. – Vicky Dec 29 '09 at 04:17
3

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
2

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

ZEE
  • 2,931
  • 5
  • 35
  • 47
  • Since it's an .mdf, I'd probably opt for SSMS. When in Rome, and all that. But it's good to know about alternatives, too. – MrBoJangles Nov 15 '12 at 17:27
  • The problem with SSMS is the size... and there is a version for SQL 2005, 2008 and 2012. In version 2008 I had really to struggle with the thing to instal SSMS, the blokes at MS really did nice job at complicating the install process... and It's only for MSSQL. But it works ok, sometimes I use it too... – ZEE Nov 15 '12 at 17:43
1

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

Arnkrishn
  • 29,828
  • 40
  • 114
  • 128
0

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 !!! :)

H. Dawar
  • 1