数据库备份/恢复过程
sql server上的大型数据库或数据库集合的备份和恢复过程对于灾难和灾难恢复非常重要。 恢复目的。 然而，我还没有找到一个强大的解决方案来保证整个过程尽可能高效、100%可靠、易于维护和跨多个服务器进行配置。
微软的维护计划似乎还不够。 我使用过的最佳解决方案是我使用许多作业手动创建的解决方案，其中每个数据库在源服务器（备份）和目标服务器（恢复）上运行许多步骤。 这些作业使用存储过程来进行备份、复制和备份。 恢复。 它每天运行一次（完整备份/恢复），日内每 5 分钟运行一次（事务日志传送）。
尽管我当前的流程可以正常工作并通过电子邮件报告任何作业失败，但我知道整个流程不是很可靠，并且在不深入了解该流程的情况下，非 DBA 无法在我们所有的服务器上轻松维护/配置。
我想知道其他人是否有相同的备份/恢复过程以及其他人如何克服这个问题。
The backup and restore process of a large database or collection of databases on sql server is very important for disaster & recovery purposes. However, I have not found a robust solution that will guarantee the whole process is as efficient as possible, 100% reliable and easily maintainable and configurable accross multiple servers.
Microsft's Maintenance Plans doesn't seem to be sufficient. The best solution I have used is one that I created manually using many jobs with many steps per database running on the source server (backup) and destination server (restore). The jobs use stored procedures to do the backup, copying & restoring. This runs once a day (full backup/restore) and intraday every 5 mins (transaction log shipping).
Although my current process works and reports any job failures via email, I know the whole process isn't very reliable and cannot be easily maintained/configured on all our servers by a non-DBA without having in-depth knowledge of the process.
I would like to know if others have this same backup/restore process and how others overcome this issue.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我使用了类似的步骤来让开发/测试/QA 数据库每晚保持“零步长”，供开发人员和 QA 人员使用。
文档是关键——如果你想消除 Scott Hanselman 所说的“总线因素”（即系统的创建者被总线撞到并且一切都开始糟糕的危险）。
也就是说，对于正常的数据库备份和灾难恢复计划，我发现 SQL Server 维护计划效果非常好。 只要你包括：
1) 体面的文档
2) 例行测试。
我概述了一些实现此目的的方法（对于任何对此问题感兴趣并正在寻找如何创建灾难恢复计划的示例的人）：
SQL Server 备份最佳实践（免费教程/视频）
I've used a similar step to keep dev/test/QA databases 'zero-stepped' on a nightly basis for developers and QA folks to use.
Documentation is the key - if you want to remove what Scott Hanselman calls 'bus factor' (i.e. the danger that the creator of the system will get hit by a bus and everything starts to suck).
That said, for normal database backups and disaster recovery plans, I've found that SQL Server Maintenance Plans work out pretty well. As long as you include:
1) Decent documentation
2) Routine testing.
I've outlined some of the ways to go about doing that (for anyone drawn to this question looking for an example of how to go about creating a disaster recovery plan):
SQL Server Backup Best Practices (Free Tutorial/Video)
您问题的关键部分是非 DBA 管理备份解决方案的能力。 任何本机 SQL Server 答案（例如备份脚本）都无法满足该需求，因为备份脚本需要 T-SQL 知识。
因此，您需要寻求第三方解决方案，例如 Mitch Wheat 提到的解决方案。 我在 Quest（LiteSpeed 的制造商）工作，所以我当然偏爱那一个 - 它很容易向非 DBA 展示。 在我离开上一家公司之前，我进行了一次十分钟的会议，向系统管理员和开发人员展示 LiteSpeed 控制台的工作原理，仅此而已。 从那以后他们就没有再打过电话。
另一种方法是使用与商店其他部分相同的备份软件。 TSM、Veritas、Backup Exec 和 Microsoft DPM 都具有 SQL Server 代理，可让您的 Windows 管理员以不同程度的易用性管理备份过程。 如果您确实希望非 DBA 来管理它，这可能是最简单的方法，尽管您牺牲了特定于 SQL 的备份工具为您提供的大量性能。
The key part of your question is the ability for the backup solution to be managed by a non-DBA. Any native SQL Server answer like backup scripts isn't going to meet that need, because backup scripts require T-SQL knowledge.
Because of that, you want to look toward third-party solutions like the ones Mitch Wheat mentioned. I work for Quest (the makers of LiteSpeed) so of course I'm partial to that one - it's easy to show to non-DBAs. Before I left my last company, I had a ten minute session to show the sysadmins and developers how the LiteSpeed console worked, and that was that. They haven't called since.
Another approach is using the same backup software that the rest of your shop uses. TSM, Veritas, Backup Exec and Microsoft DPM all have SQL Server agents that let your Windows admins manage the backup process with varying degrees of ease-of-use. If you really want a non-DBA to manage it, this is probably the most dead-easy way to do it, although you sacrifice a lot of performance that the SQL-specific backup tools give you.
我正在做完全相同的事情，即使在这个过程中，我也会定期遇到各种问题。
如何处理将文件从服务器 A 复制到服务器 B 与在服务器 B 上恢复事务备份之间的间隔。
每隔一段时间，事务备份就会比正常情况大，并且需要更长的时间来复制。 然后，还原作业会收到操作系统错误，表明该文件正在使用中。
这并不是什么大问题，因为该文件会在下次自动应用，但是如果有一个更优雅的解决方案并且专门解决这个问题，那就更好了。
I am doing precisely the same thing and have various issues semi regularly even with this process.
How do you handle the spacing between copying the file from Server A to Server B and restoring the transactional backup on Server B.
Every once in a while the transaction backup is larger than normal and takes a longer time to copy. The restore job then gets an operating system error that the file is in use.
This is not such a big deal since the file is automatically applied the next time around however it would be nicer to have a more elegant solution in general and one that specifically fixes this issue.