如何使用生产数据刷新SQL服务器的测试实例，而不使用完全备份 [英] How to refresh a test instance of SQL server with production data without using full backups

问题描述

我有两个MS SQL 2005服务器，一个用于生产，一个用于测试，两个都有完全恢复模式。我将生产数据库的备份恢复到测试服务器，然后让用户进行更改。

I have two MS SQL 2005 servers, one for production and one for test and both have a Recovery Model of Full. I restore a backup of the production database to the test server and then have users make changes.

我想要能够：

• 回滚对测试SQL服务器所做的所有更改


• 应用自测试以来在生产SQL服务器上发生的所有事务服务器最初还原，以便两个服务器具有相同的数据

我不想从备份文件做完整的数据库还原

I do not want to do a full database restore from backup file as this takes far too long with our +200GB database especially when all the changed data is less than 1GB.

EDIT

根据下面的建议，我尝试用NoRecovery恢复数据库，但是你不能创建处于那个状态的数据库的快照。

Based on the suggestions below I have tried restoring a database with NoRecovery but you cannot create a snapshot of a database that is in that state.

我也尝试恢复到Standby只读模式，它工作，我可以拍摄数据库的快照，然后仍然应用事务日志到原始数据库，但我不能使数据库可写的，只要有快照对它。

I have also tried restoring it to Standby Read only mode which works and I can take a snapshot of the database then and still apply transaction logs to the original db but I cannot make the database writable again as long as there are snapshots against it.

运行：

restore database TestDB with recovery

导致以下错误：

Msg 5094, Level 16, State 2, Line 1 The operation cannot be performed on a database with database snapshots or active DBCC replicas

推荐答案

在尝试了这里提供的所有建议后，我还没有找到任何方法来完成我在SQL中提出的问题。如果有人可以找到一种方式和发布或有另一个建议，我会乐意尝试其他的东西，但在这一点似乎没有办法实现这一点。

After having tried all of the suggestions offered here I have not found any means of accomplishing what I outlined in the question through SQL. If someone can find a way and post it or has another suggestion I would be happy to try something else but at this point there appears to be no way to accomplish this.

这篇关于如何使用生产数据刷新SQL服务器的测试实例，而不使用完全备份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！