只需对源进行最少的配置更改即可将供应商的 SQL Server 数据库镜像到报告数据库
我们在服务器上托管供应商应用程序的 SQL Server 数据库，我们希望定期将其复制/镜像/复制到报告位置。夜间备份的完整恢复不起作用，完整备份太大，无法通过网络传输并及时恢复。
我知道复制和日志传送，但似乎这些选项需要“源”数据库来完成工作并以某种方式进行配置以允许这种情况发生。我们希望对生产数据库进行尽可能少的更改，因为它是由第三方供应商的应用程序“控制”的，并且我们所做的任何更改都会导致他们出现支持问题（这是可以理解的）。
所以..这一定是一个常见的场景，但搜索没有结果。是否有一种干净、简单的方法可以定期（例如每晚）将非常大的活动 SQL Server 2005 数据库增量镜像到报告位置，同时不对源数据库进行任何（或最少）更改？
We host on our servers a vendor application's SQL Server database that we'd like to copy/mirror/replicate to a reporting location on a regular basis. Full restores of nightly backups aren't working, full backups are too large to transfer over the network and restore in a timely manner.
I am aware of Replication and Log Shipping, but it appears that those options require the "source" database to do the work and be configured in some way to allow for this to happen. We'd like to make as few changes as possible to our production database since it is "controlled" by a 3rd-party vendor's application and any changes we make causes support issues for them (understandably so).
So .. this must be a common scenario but searching yielded no results. Is there a clean, simple way to mirror a very large, active SQL Server 2005 database to a reporting location incrementally on a regular basis (say, nightly) while making no (or minimal) changes to the source database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我过去曾使用日志传送来完成此操作。要启用此功能，您需要对源数据库执行的唯一操作是将恢复模式设置为“完整”，然后使用完整备份和事务日志备份的计划对其进行配置。更详细的概述：
*备用模式允许对数据库进行只读访问。请注意，如果备用数据库有任何打开的连接，则无法将后续恢复应用于备用数据库。
时机是这里的难点。假设您希望每天在午夜 YST（即“您的标准时间”）更新数据库一次，这意味着：
- 此时，确保没有与目标数据库的打开连接（即杀死您找到的任何连接，并确保您的用户知道您会这样做！）
- 应用当天的累积恢复，
几年前我就这样做了，我不认为内置日志传送实用程序支持这些精确的要求（或者它们过于复杂）。不管怎样，“自己动手”并不那么难；只需跟踪最后应用的 t-log，编写“Shoot Zem All”连接终止例程，然后提取并应用在备份文件夹中找到的任何后续例程。
I have done this in the past with Log Shipping. The only thing you need to do to the source database to enable this is to set the recovery model to "Full", and then configure it with a schedule of Complete and Transaction Log backups. A more detailed outline:
*Standby Mode allows read-only access to the database. Note that you cannot apply subsequent restores to a standby database if there are any connections open to it.
Timing is the hard part here. Presumably you want to update the database once per day at, say, Midnight YST (that's "Your Standard Time"), which means that:
- At that time, ensure that there are no open connections to the target database (i.e. kill any you find, and make sure your users know you'll be doing that!)
- Apply the day's accumulated restores at that time
I did this a few years back, and I don't think these precise requirements were supported by the built-in log shipping utilities (that, or they were overly complex). Whatever, it wasn't all that hard to "roll your own"; simply keep track of the last t-log applied, write a "Shoot Zem All" connection-killing routine, and pull and apply any subsequent ones found in the backup folder.