创建 SQL Server 回滚脚本的最佳方法？
我正在对现有数据库进行一些架构更改。
我备份了数据库以获得开发副本，并进行了更改。 我将创建一个滚动脚本来在单个事务中迁移生产计算机上的更改。
是否有创建回滚脚本以防止出现部署问题的最佳实践？ 在我使用以下模式手动编写之前：
- 删除新的约束和索引
- 更改表以删除新列
- 删除添加的表
- 提交事务
有没有更好的方法？
I am working on some schema changes to an existing database.
I backed up the database to get a dev copy, and have made my changes. I will be creating a single roll script to migrate the changes on the production machine in a single transaction.
Is there a best practice for creating a rollback script encase a deployment issue arises? Before I've written then by hand using the following pattern:
- Drop new constraints and indexes
- Alter tables to remove new columns
- Drop added tables
- Commit transaction
Is there a better approach?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您错过了第五步
更有效的方法是注册更改因为它们的发生就像 RoR 迁移 执行。 对于每个数据库更改，您创建一个脚本，该脚本将应用更改并将其回滚（当然，由您选择）。 然后，您可以将这些脚本置于版本控制之下，就像您的代码一样。
此外，如果您在数据库中保留版本号，则可以通过使用版本号标识每个脚本并根据执行的操作增加或减少数据库中的版本号，从而使其更加自动化。
You are missing the fifth step
A more efficient approach is to register the changes as they happen like RoR migrations do. For each DB change you create a script that will both apply the change and roll it back (at your choice, of course). You can then have those scripts under version control just like your code.
Additionally, if you keep a version number in the database you can automatize it a bit more, by identifying each script with a version number and having it increase or decrease the version number in the database according to the performed action.
基本上就是这样，除了你的方法之外，我认为没有什么可补充的。 这就是我们在公司中的做法，我们开发人员负责创建脚本和回滚脚本，并且我们负责使数据库保持应用初始更改之前的相同状态。 然后 DBA 在生产中运行它，如果出现问题，他们将运行回滚脚本，一切都会恢复正常。 只需记住依赖关系和更改对象的顺序，然后向后创建回滚脚本即可。
That's basically it, I don't think there's much to add, aside from what your approach. This is how we do it in our company, we developers are responsible for creating the script and the rollback script, and we are responsible for leaving the DB in the same state it was before the initial changes are applied. Then the DBAs run it in production, if there's ever a problem they'll run the rollback script and everything is back to normal. Just keep in mind the dependencies and the order in which you alter your objects and then create the rollback script backwards.
如果它是一个相对较小的数据库，只需在应用升级之前进行备份。 如果一切都失控了，你就进行恢复。
一旦它上线并输入新数据，您无论如何都无法真正将其回滚。 你只需要解决问题。
If it's a relatively small database just make a backup before you apply the upgrade. If it all goes haywire you do a restore.
Once it goes live and new data is entered you can't really roll it back anyways. You just have to fix the problems.
如果您使用 Redgate 的 SQL Compare 等工具，则可以以两种方式运行两个数据库之间的比较，并使用这两种方式创建您需要的脚本。 dev 到 prod 脚本将包含您所做的更改，prd 到 dev 脚本将返回到原始生产数据库状态。 这可以确保您也不会忘记两个脚本中的任何内容。
If you use a tool like Redgate's SQL Compare, you can run the compare between the two databases both ways and use both to create the scripts you need. the dev to prod script would have the changes you are making and the prd to dev script would return to the orginal production database state. This ensures you don't forget anything in either script as well.
使用 SQL Server 2005 或更高版本，您还可以创建数据库快照，以便您随时返回到该状态。 有关详细信息，请参阅此页面：
http://msdn.microsoft.com/en -us/library/ms175158.aspx
With SQL Server 2005 or newer you can also create a database snapshot, that allows you to go back to that state at any time. See this page for more information:
http://msdn.microsoft.com/en-us/library/ms175158.aspx