创造与使用 SQL Server 回滚测试数据
我正在创建一系列 Web 自动化测试，这些测试需要测试数据位于数据库 (SQL Server 2008) 中。为了生成每个测试所需的数据，我必须调用一些 C# 代码，它将正确的数据插入到数据库中（即我不能只编写 SQL 脚本来插入数据）。我的问题是我不想用这些自动化测试中的大量测试数据污染我的测试数据库。因此想回滚测试期间对数据库所做的所有更改。
谁能提出实现这一目标的明智方法？
I am creating a series of web automation tests which require test data to be in a database (SQL Server 2008). To generate the required data for each test I have to call some C# code which will insert the correct data into the DB (i.e. I can't just write SQL scripts to insert the data). My problem is I don't want to pollute my test db with lot's of test data from these automated tests. So would like to rollback all of the changes made to the DB during the test.
Can anyone suggest a sensible way of achieving this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
简单的方法是在运行测试之前创建数据库备份，然后在最后恢复。
Simple way would be to create a backup of the database before running the tests, and then just restore back at the end.
有两种方法可以做到这一点。
一种是将您的测试包含在事务中并将其回滚。另一种方法是使用清理脚本作为测试完成代码的一部分（我们对某些事务不起作用的集成测试执行此操作）。
Two ways of doing this.
One is to enclose your test within a transaction and roll it back. Another is to use a cleanup script as part of your test completion code (we do this for some of our integration tests where a transaction does not work).
当我无法控制测试的事务范围时，我通常每次都会删除并重新创建数据库。
显然，只有当测试可以针对裸模式（或在创建脚本中插入硬编码的查找值）运行时，这才是可行的。
当我对预先填充大量数据的快照数据库进行测试时，我之前使用过清理脚本，例如删除每个表中高于基准快照最大 ID 的所有记录。
还没有尝试像 AdaTheDev 建议的那样自动化备份/回滚，但如果您不想维护潜在复杂（且有缺陷）的清理脚本（取决于快照数据的复杂性/您的频率），这听起来可能是您的最佳选择可能会更改您的快照并必须相应地修改您的清理）。
您是否考虑过模拟数据访问，以便您的 Web 测试针对内存数据存储运行？然后在内部测试数据访问过程，您仍然可以回滚事务范围吗？
When I don't have control over the transaction scopes of my tests, I usually drop and recreate the database from scratch each time.
Obviously, this is only feasible if the tests can run against the bare schema (or with hardcoded lookup values inserted in the create scripts).
When I test against a snapshot database pre-populated with lots of data, I have before used cleanup scripts, say delete all records from each table above the max id of my baseline snapshot.
Haven't tried automating the backup/rollback as AdaTheDev suggests, but it sounds like probably your best option if you don't want to maintain potentially complicated (and buggy) cleanup scripts (depends on the complexity of your snapshot data / how often you might change your snapshot and have to modify your cleanups accordingly).
Have you considered mocking out the data access, so that your web tests run against an in memory data store? Then test the data access procedures internally where you can still rollback the transaction scopes?
Red Gate 的向导刚刚发布了 SQL Virtual Restore，它实际上会将备份文件安装为实时、可读、可写的数据库 - 因此您可以在测试之前拥有一个代表系统基线状态的备份文件，获取以下副本将此备份挂载为测试数据库，运行测试，然后卸载并擦除该副本。
虚拟还原位于 http://www.red-gate.com/products/sql_virtual_restore /index.htm 如果您想尝试的话，有 14 天的试用期。
顺便说一句，我与 Red Gate 没有任何关系 - 我只是他们工具的热心用户。
The wizards over at Red Gate have just released SQL Virtual Restore, which will actually mount a backup file as a live, readable, writable database - so you could have a backup file representing the baseline state of your system before tests, take a copy of this backup, mount the copy as your test database, run the tests, and then unmount and wipe the copy.
Virtual Restore is at http://www.red-gate.com/products/sql_virtual_restore/index.htm and there's 14-day trial if you want to try it out.
I have no affiliation with Red Gate, btw - I'm just an enthusiastic user of their tools.
听起来很难使用事务，因为您将在一次测试中发出多个 Web 请求 - 但这将是我的第一选择，因为它比从备份恢复数据库更快。
如果您拥有正确版本的 SQL Server，则可以使用数据库快照而不是备份： http://msdn.microsoft.com/en-us/library/ms175876.aspx，只是因为它们更快:)
It sounds like it would be hard to use transactions, since you're going to be making multiple web requests in a single test - but that would be my first preference because it's faster than restoring a database from backup.
If you've got the right version of SQL server, you could use database snapshots instead of backups: http://msdn.microsoft.com/en-us/library/ms175876.aspx, simply because they are faster :)
显然，这完全取决于您如何调用测试，但是 MbUnit 中的“Rollback”属性是否有效？
Obviously it all depends on how you call the tests but would the "Rollback" attribute work that is in MbUnit?