连续数据库备份？
我有以下场景：
我们的系统正在本地运行 SQL Server Express 2005 数据库（如果您愿意，可以在每个用户的桌面上）。 该系统存储来自机器的大量生产数据。 对数据的安全性要求很高，每晚甚至每小时备份是不够的。 我们需要一种备份策略来确保数据库几乎即时/连续的备份。
是否有人已经成功实现了与此类似的系统，和/或对如何实现它有一些想法？ 我现在唯一能想到的就是使用镜像驱动器（raid）来保存数据，但这会很复杂且昂贵。
我很感激对此的任何想法，因为这对我和我的公司来说是一个真正的问题。 提前致谢！
更新：
我对场景的描述不够清楚。 该系统将数据存储在与任何东西都没有连接的车辆中。 因此，集中式数据库是不可能的。 我们也不能使用 SQL Server 的标准/企业版本，因为它太昂贵了（每辆车都需要许可证）。 感谢您的输入！
I have the following scenario:
Our system is running a SQL Server Express 2005 database locally (on each users desktop, if you will). The system is storing a lot of production data from a machine. There are high demands on the safety of the data, and doing a backup each night, or even each hour is not enough. We need a backup strategy that will ensure almost instantaneous/continuous backup of the database.
Is there anyone out there that has successfully implemented a system similar to this, and/or has got some ideas of how to accomplish it? The only thing I can think of right now is to have mirrored drives (raid) to hold the data, but that would be complicated and expensive.
I would appreciate any and all thoughts on this, since it is a real issue for me and my company. Thanks in advance!
Update:
I was not clear enough in my description of the scenario. The system is storing data in a vehicle that has no connection to anything. A centralized database is therefor not possible. Neither can we use a standard/enterprise version of SQL Server, since it would be to expensive (each vehicle would need a license). Thanks for your input!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
将数据库切换到“完整”恢复模式。 每晚进行完整备份，并在主要用户操作后进行增量备份。 可以对闪存或不同的硬盘进行增量备份，并且所有数据可以在在线时与服务器同步。
另一种简单的方法是在存储在单独驱动器上的文本文件中跟踪所有用户更改和重要数据。 如果 SQL 数据库崩溃，用户或其他操作员可以重复步骤来恢复数据。
Switch your database into "Full" recovery mode. Do full backup every night and do delta backup after major user action. The delta backups can be done to the flash memory or different hard-drive, and all data can be synchronized with server when online.
Another simple way is to trace all user changes and important data in a text file that stored on a separate drive. If SQL database crashes the user or other operator can repeat steps to restore data.
我看到完成此操作的一种方法是使用 DoubleTake 。
One way I've seen this done is by using DoubleTake.
我假设服务器上的中央数据库是不可行的，因为您的系统是独立运行的并且没有连接到任何东西。 这就是我
在计算机上设置 RAID 的方法。 这可以确保您免受简单的磁盘故障的影响。
如果您有完整的数据库备份和一组可用的事务日志，则任何 SQLSever 数据库都可以恢复到最后提交的事务。 基本上，您只需恢复最后一个完整备份，然后应用事务日志即可。 请参阅这些链接。
http://www.enterpriseitplanet.com/storage/features/article.php/ 11318_3776361_3
https://web .archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=132
因此，您需要做的是设置数据库和事务的定期完整备份日志，以及更定期的事务日志备份（并确保您的事务日志永远不会用完空间）。
如果发生故障，您可以恢复上次完整备份，然后应用事务日志。
就我个人而言，如果这些是关键系统，我会倾向于向系统添加额外的驱动器，并确保将备份复制到该系统。 这是因为，与 raid 一样好，它有时也会出现问题 - raid 控制器失败、磁盘并行意外擦除、磁盘故障未被注意到，因此您只在一个磁盘上运行等等。如果您确保将备份复制到单独的磁盘上，那么您就可以始终可以恢复到最后的事务日志备份。 当然，您还应该确保磁带备份，但它们通常是出现问题时的最后手段。
如果由于某种原因您无法设置 raid，那么您仍然应该安装第二个磁盘，但将数据库文件放在一个驱动器上，将事务日志放在另一个驱动器上，并将备份复制到两个磁盘。 如果 C 驱动器出现故障，或者某些其他软件问题导致数据库崩溃，您仍然可以恢复到最后提交的事务。 D 驱动器上的故障将您限制为最后一次事务日志备份（Oracle 过去允许您从数据库镜像事务日志，这又会完全覆盖您，但我认为 SQL Server 中不存在此功能）
如果您正在寻找 SQL Server Express 的调度程序（它没有附带），那么我一直在使用 SQLScheduler 很高兴没有出现任何问题，而且它是免费的。
I will assume that a central database on a server is not feasible because your systems are running standalone and are not connected to anything. So this is what I would do
Set up RAID on the computer. This insures you against simple disk failure.
Any SQLSever database can be recovered to the point of the last commited transaction if you have a full database backup and a set of transaction logs available. Basically you simply restore the last full backup then apply the transaction logs going forward. See these links.
http://www.enterpriseitplanet.com/storage/features/article.php/11318_3776361_3
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=132
So what you need to do is set up a periodic full backup of both the database and transaction logs, and more regular transaction log backups (and ensure that your transaction log can never run out of space).
In the event of failure you restore the last full backup, then apply the transaction logs going forward.
Myself, if these are critical systems, I would be inclined to add an additional drive to the system and make sure that the backups are copied over to that. This is because as good as raid is it does sometimes have issues - raid controllers fail, disks get wiped accidentally in parallel, disk failures go unnoticed so your just running on one disk etc. If you ensure backups are copied to a separate disk then you can always recover to the last transaction log backup. You should also ensure tape backups of course, but they are generally a last resort in the event of trouble.
If for some reason you cannot set up raid then you should still install a second disk, but place the database file on one drive and the transaction log on the other and copy backups to both disks. In the event of failure of the C drive, or some other software issue crashing the database you can still recover to the last commited transaction. Failure on the D drive limits you to the last transaction log backup (Oracle used to allow you to mirror the transaction log from the database, which again would completely cover you, but I don't think this facility exists in SQL Server)
If you are looking for a scheduler for SQL Server Express (which doesn't come with one) then I've been using SQLScheduler quite happily without problems, and it's free.
最明显的答案是放弃本地运行的 SQL Server Express，并使用单一数据源（例如安装在中央存储位置上的标准 SQL Server）。 除非您的系统需要对每个人自己的 SQL Server Express 实例进行单独备份。
如果您的要求非常严格，以至于需要对每个操作进行即时备份，那么您绝对应该考虑采用与 SQL Server Express 本地实例不同的存储方法。
The most obvious answer would be to ditch SQL Server Express running locally and use a single source for your data (such as a standard SQL server install on a central storage location). Unless your system requires individual back ups of every single person's own individual instance of SQL Server Express.
If your requirements are so stringent as to call for instantaneous backups on every operation, you should definitely think about a different method of storage than local instances of SQL Server Express.
仅使用一个集中式 SQL Server 并每小时左右备份一次不是更容易吗？ 如果你确实需要即时备份，你的公司（似乎不想花钱在每台机器上安装 Express）将需要购买两台服务器和两个 SQL Server Enterprise 许可证来实现镜像。
Wouldn't it be easier to just use one centralized SQL Server and back that up every hour or so? If you truly need instantaneous backup, your company (which seems not to want to spend money by installing Express on each machine) will need to spring for two servers and two SQL Server Enterprise licenses to implement Mirroring.
Raid 并不那么昂贵，但它也不是最好的选择。 如果您确实想要高可用性数据，您应该在远程服务器上升级到 sql server 标准，其中每个用户连接到另一台计算机上的 sql server (express) 实例并使用基于事务的复制。 Raid 并不总能保护您免遭数据丢失。 如果数据对您来说很重要，那么成本就不应该是一个大问题。
更新响应问题更新。
如果您无法使用远程服务器，那么有几个选择：
Raid isn't that expensive, but it is also not the best option. If you really want high availability data you should upgrade to sql server standard on a remote server where each user connects to and use transaction based replication to an sql server (express) instance on another machine. Raid doesn't always protect you from dataloss. If the data is that important for you then the costs should not be that much of an issue.
Update in response to the question update.
If you can't use remote servers then there a couple of options:
RAID 并不昂贵。 使用 RAID 来防止硬盘故障。 不过你还需要监控。 如果您让两个驱动器都发生故障，那么这样做就没有意义。
此外，实施每小时增量备份，然后是每日增量备份，最后是每周完整备份。
您需要所有这些策略一起工作，因为它们可以防止不同的事情。 RAID 不能防止人为错误或编码错误破坏数据。 每小时和每周备份并不能防止硬盘故障。
RAID is not expensive. Use RAID to protect against hard drive failure. You also need monitoring though. No point in having this if you let both drives fail.
Also, implement hourly incremental backups, then daily incremental backups and finally weekly full backups.
You need all of these strategies working together because they protect against different things. RAID does not protect against human or coding errors destroying data. Hourly and weekly backups don't protect against hard drive failure.