将更改从开发数据库传输到生产数据库
假设我有一个网站和该网站的数据库托管在我的计算机上本地（用于开发）和另一个数据库托管（用于生产）...即首先我对开发数据库进行更改，然后对产品进行更改D B。
将我在本地数据库上所做的更改传输到托管数据库的最佳方式是什么？
如果重要的话，我正在使用 MS Sql Server (2008)
Say I have a website and a database of that website hosted locally on my computer (for development) and another database hosted (for production)...ie first I do the changes on the dev db and then I do the changes to the prod DB.
What is the best way to transfer the changes that I did on the local database to the hosted database?
If it matters, I am using MS Sql Server (2008)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
当开发人员测试/验证了他们的更改后，我通过开发人员编写的更改脚本来迁移更改。 （移动大数据除外。）所有脚本都存储在源代码控制系统中。 并且可以由 DBA 验证。
这是手动的，有时耗时，但有效、安全且受控的过程。
数据库太重要了，无法从开发人员那里复制。
有一些工具可以帮助创建/验证这些脚本。
请参阅 http://www.red-gate.com/
我使用他们的工具来比较两个数据库来创建脚本。
布莱恩
I migrate changes via change scripts written by developers when they have tested/verified their changes. (The exception being moving large data.) All scripts are stored in a Source control system. and can be verified by DBAs.
It is manual, sometime time consuming but effective, safe and controled process.
Databases are too vital to copy from dev.
There are tools to help create/verify these scripts.
See http://www.red-gate.com/
I have used their tools to compare 2 databases to create scripts.
Brian
如果改动很小，我有时会手工制作。 对于较大的更改，我使用 Red Gate 的 SQL Compare 来生成更改脚本。 它们首先经过手工验证并在 QA 环境中运行，以确保它们不会破坏任何内容。 对于较大的更改，我们会在 QA 和生产中进行更改之前运行特殊的备份。
If the changes are small, I sometimes make them by hand. For larger changes, I use Red Gate's SQL Compare to generate change scripts. These are hand-verified and run in the QA environment first to make sure they don't break anything. For large changes, we run a special backup prior to making the change both in QA and in production.
我们曾经使用 Ron 提供的方法。 对于拥有专门的 DBA 团队的大型项目来说这是有意义的。 但是，如果您没有专门为数据库编写代码的开发人员，那么这种方法会耗费大量时间和资源。
使用RedGate DB比较的方法也不好。 您仍然需要做大量手动工作，您可能会错误地跳过一些步骤。
它需要更好的东西。 这就是我们构建“Agile DB Recreation/Import/Reverse/Export tool”的原因
该工具是免费的。
优点：您的开发人员可以使用任何首选工具来开发 DEV DB。 然后他们运行 DB RIRE，它对 DB（表、视图、存储过程等）进行逆向工程，并将数据导出到 XML 文件中。 您可以将 XML 文件保存在任何代码存储库系统中。
第二步是再次运行 DB RIRE，以生成 XML 文件和生产 DB 中的结构和数据之间的差异脚本。
当然，您可以根据需要进行尽可能多的迭代。
We used to use the approach provided by Ron. It makes sense for a big project with dedicated team of DBAs. But if you do not have a dedicated developers who write code only for DB this approach is time and resource expensive.
The approach to use RedGate DB compare is also not good. You still have a do a lot of manual work you can skip some step by mistake.
It needs something better. This is was the reason why we built the "Agile DB Recreation/Import/Reverse/Export tool"
The tool is free.
Advantages: your developers use any prefered tools to develop DEV DB. Then they run the DB RIRE and it makes reverseengeniring DB (tables, views, stor proc, etc) and export data into XML files. XML files you can keep in the any code repository system.
And the second step is to run DB RIRE one more time to generate difference scripts between structure and data in XML files and in Production DB.
Of course you can make as much iterations as you need.
使用 Visual Studio 和 SQL Server 执行此操作的正确方法是将数据库项目添加到 Web 应用解决方案。 数据库项目应该具有可以在新服务器上完全重新创建整个数据库以及所有必要的表、过程用户和角色的 SQL 文件。
这样，它们也包含在所有其余代码的源代码管理中。
数据库项目中有一个 Changes 子文件夹，我在其中放置了 SQL 文件，这些文件将任何新的更改或添加内容应用于后续版本的数据库。
文件中的 SQL 应该使用正确的“如果存在”块编写，以便可以在已更新的数据库上安全地多次运行而不会出现错误。
通常，您永远不应该直接在数据库中进行更改 - 而是修改项目中的 SQL 脚本并将其应用到数据库，以确保您的源代码（SQL 文件）始终是最新的。
The correct way to do this with Visual Studio and SQL Server is to add a Database Project to the web app solution. The database project should have SQL files that can recreate the entire database completely on a new server along with all the necessary tables, procedures users and roles.
That way, they are included in the source control for all the rest of the code as well.
There is a Changes sub-folder in the Database Project where I put the SQL files that apply any new alterations or additions to the database for subsequent versions.
The SQL in the files should be written with proper "if exists" blocks such that it can be run safely multiple times on an already updated database without error.
As a rule, you should never make your changes directly in the database - instead modify the SQL script in the project and apply it to the database to make sure your source code (the SQL files) is always up to date.
我们在（Ruby on）Rails 世界中通过编写“迁移”来实现这一点，它捕获您在每个点对数据库结构所做的更改。 这些是使用迁移工具（rake 的任务）运行的，该工具还会写入数据库表，以便了解特定的迁移是否已运行。
您可以为您的开发平台（.Net？）制作这样的结构，但我认为在这个问题的其他答案中，人们会建议用于在您的开发平台中处理数据库版本控制的可用工具，或者可能针对您的特定数据库。
我对这些都不了解，但请查看此列表。 我看到有很多付费的东西，但一定有免费的东西。 另请查看一下。
We do this in the (Ruby on) Rails world by writing "migrations," which capture the changes you make to the DB structure at each point. These are run with a migration tool (a task for rake), which also writes to a DB table so it knows whether a particular migration has been run or not.
You could make a structure like this for your dev platform (.Net?), but I think that in other answers to this question people will suggest available tools for handling database versioning in your development platform, or perhaps for your specific DB.
I don't know any of these, but check out this list. I see a lot of pay things out there, but there must be something free. Also check this out.