如何编辑 SQL Server 主数据库文件 .mdf
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1972755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to edit SQL Server Primary Database File .mdf
提问by Vicky
I have a SQL Server Primary Database file. (.mdf)
我有一个 SQL Server 主数据库文件。(.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.
这样做是个好主意吗？因为我没有任何编程知识，所以我总是可以在玩之前备份 .mdf 文件。
回答by Ash
Download SQL Server Management Studio Expressand write an update query to change the required fields.
下载SQL Server Management Studio Express并编写更新查询以更改必填字段。
I can almost guarantee that editing the MDF file directly is risky to your data and not supported in any way by Microsoft.
我几乎可以保证直接编辑 MDF 文件对您的数据有风险，并且 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.
如果您没有编程知识，您应该找人为您编写更新查询。或者，您可以自己阅读基本 SQL。大多数人可以很快地使用简单的 SELECT 和 UPDATE 语句。
Here's a good simple introduction to the UPDATE statement.
这是对 UPDATE 语句的一个很好的简单介绍。
回答by Remus Rusanu
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.
您无法在 SQL Server 之外更新 MDF 文件中的数据。文件格式没有公开，即使您设法以某种方式对其进行更新，完整性检查充其量只会导致修改后的表被标记为损坏，更糟糕的是整个数据库脱机。
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 andLDF files.
只有一种工具可以打开和修改 MDF 文件：适当版本的 SQL Server 实例，正如 Ash 指导您的那样。在对数据库进行任何修改之前，我强烈建议您制作一份 MDF和LDF 文件的副本。
回答by ZEE
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...
毫无疑问，SQL Studio 对你想要的东西来说太过分了......还有一个巨大的英国媒体报道......而且只适用于 MSSQL......所以我建议......
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!!!
Query Express (http://www.albahari.com/queryexpress.aspx) 或 Query ExPlus (http://sourceforge.net/projects/queryexplus)... Joseph Albahari 提供的免费/优秀/小型/快速/便携式工具...哎呀，更好，这也适用于 Oracle、ODBC 和 OLEDB...太棒了！！！
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!!!
如果您想在（C#、F#、VB 或 SQL）中创建小代码片段并针对数据库执行它们，我的建议也是 Joseph Albahari 的一个免费工具，称为 LINQPad (http://www.linqpad.net/)。 . 这是一个用于测试/原型代码的 Excelente 小型 IDE...谢谢约瑟夫！！！
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).
如果你想要一个像 MSSQL Studio 这样具有一些高级功能（免费）的“辅助”IDE，我推荐 EMS SQL SQL Manager Lite ......很棒的工具......更好......你有一个 MSSQL 版本（http://www. snapfiles.com/get/emsmssqllite.html)...和一个 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)
附录... 2012.11.15, 17.54
之前忘了提DatabaseBrowser，一个免费/小巧/快速/便携的实用工具，我也时不时使用，可以编辑Oracle、MSSQL、ODBC、MySql、OleDB、PostgreSQL、SQLite 、Interbase 和 Firebird。我主要用它来编辑 Access/mdb、PostGRE、SQLite 和 Firebird。您可以在此处获得便携式版本（http://www.etl-tools.com/database-editors/database-browser/overview.html）
Have fun first... productivity next!!!
先玩得开心……接下来是生产力！！！
ZEE
ZEE
回答by H. Dawar
If you aren't able to open the .mdf n .ldf files via SSMS and receiving errors
如果您无法通过 SSMS 打开 .mdf n .ldf 文件并收到错误
Use the below script in a 'new query' in SSMS to find the sql service account
在 SSMS 的“新查询”中使用以下脚本查找 sql 服务帐户
Code Snippet
代码片段
declare @sqlser varchar(20)
声明@sqlser varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
@value_name='objectname', @value=@sqlser OUTPUT
@value_name='objectname', @value=@sqlser 输出
PRINT 'Account Starting SQL Server Service:' +convert(varchar(30),@sqlser)
PRINT '帐户启动 SQL Server 服务：' +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..
右键单击系统中保存的 .mdf/.ldf，然后单击属性单击安全选项卡单击添加按钮并添加 sql 服务帐户提供修改权限并单击确定验证 mdf 和 ldf 都具有修改权限附加数据库..
then you can easily work on the database tables.. To insert: INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
然后你可以轻松地处理数据库表..插入：INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
To Delete: DELETE FROM table_name WHERE some_column=some_value
删除： 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!
请注意 DELETE 语法中的 WHERE 子句。WHERE 子句指定应该删除哪个或哪些记录。如果省略 WHERE 子句，将删除所有记录！
To Update: UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
更新： UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
Happy SQLing !!! :)
SQLing 快乐！！！:)
