我有一个每晚备份的 SQL Server 2005 数据库。 备份包括：
- 数据库的完整备份。
- 事务日志的备份。
目前这是两个独立的工作。
日志很大，我想进行一些设置，以便：
- 数据库每晚进行完整备份，
- 设置日志以便我可以从一个备份和下一个备份之间的任何点恢复数据库。
我该如何设置才能使日志文件易于管理？ 我怀疑日志从未缩小过，因为日志很大。
I have a SQL Server 2005 database that is backed up nightly. There backup consists of:
- FULL backup of the database.
- backup of the transaction log.
These are currently two separate jobs.
The log is huge and I'd like to set things up so that:
- the database is backed up in full nightly
- the log is set such that I can recover the database from any point between one backup and the next.
How can I set this up so that the log files are manageable? I suspect that the log has never been shrunk, as the log is huge.
发布评论
评论（3）
据了解，您目前正在实施完整恢复模型。 如果您有涵盖所需时间点（完整备份后）的事务日志备份，这将允许您恢复到某个时间点。
为了减少所需事务日志文件的大小，您应该增加事务日志备份的频率。 我建议每小时一次。 一旦您测量了日志文件的实际使用情况，您就可以将其缩小到更合适的大小。 这里要注意的关键点是，一旦事务日志备份完成，日志文件的非活动部分就可以再次使用。 事务日志文件持续增长的原因是事务日志备份根本没有进行或者频率不够。
我还建议您考虑混合执行差异备份和完整备份，以减少备份数据的总体大小。 一个示例计划是每周一次完整备份，例如每周日，以及每日差异备份。
我希望我所详细的内容是有意义的。 请随时直接与我联系，我将很乐意协助您为您的环境部署适当的备份策略。
基本参考：
文件避免意外增长
SQL服务器
You are currently implementing the FULL Recovery Model from the sound of things. This will allow you to restore to a point in time provided that you have a transaction log backup that covers the desired point in time (post full backup).
In order to reduce the size of your required transaction log file, you should look to increase the frequency of your transaction log backups. I would suggest hourly. Once you have gauged the actual usage of your log file, you can then look to shrink it to a more suitable size. The key point to note here is that once a transaction log backup has been completed, the inactive portion of the log file becomes available for use once again. The reason why a transaction log file grows continuously is if the transaction log backups are either, not being taken at all or their frequency is not sufficient.
I would also suggest that you consider performing a mix of DIFFERENTIAL and FULL Backups in order to reduce the collective size of your backed up data. An example schedule would be a weekly FULL Backup, say every Sunday, with daily DIFFERENTIAL backups.
I hope what I have detailed makes sense. Please feel free to contact me directly and I will happily assist you in deploying an appropriate backup strategy for your environment.
Essential References:
file from growing enexpectedly
SQL Server
我在备份方面发现的一件事是，人们通常不会足够频繁地运行备份 - 特别是在日志文件备份方面。 听起来你是对的，日志文件没有被定期截断（这意味着你可能浪费优质磁盘空间 [1]）。 但更重要的是，从可恢复性的角度来看，这会让您完全暴露在外。）
不过令人高兴的是，根据您的需要启动并运行并不是那么困难。 事实上，我推荐以下三个视频，因为它们应该为您提供所需的背景信息，然后是您需要遵循的分步说明，以使一切正常工作：
http://www.sqlservervideos.com/video/logging-essentials
http://www.sqlservervideos.com/video/sql2528-log-files
http://www.sqlservervideos.com/video/sqlbackup-best-practices
< a href="http://www.sqlservervideos.com/video/logging-essentials" rel="nofollow noreferrer">1 最大化存储性能：http://www.sqlmag.com/Article/ArticleID/100893/sql_server_100893.html
One of the things I find with backups is that people typically don't run them frequently enough - especially when it comes to log file backups. And it sounds like you're correct, that the log file isn't being truncated regularly (which means you're likely wasting premium disk space [1]). More importantly though, that's leaving you completely exposed from a recoverability standpoint.)
Happily though, getting things up and running as you need them isn't so hard. In fact, I'd recommend the following three videos as they should give you the background info you need, and then the step-by-step instructions you'll want to follow to get everything working correctly:
http://www.sqlservervideos.com/video/logging-essentials
http://www.sqlservervideos.com/video/sql2528-log-files
http://www.sqlservervideos.com/video/sqlbackup-best-practices
1 Maximize Storage Performance: http://www.sqlmag.com/Article/ArticleID/100893/sql_server_100893.html
您所做的实际上是简单模式备份，其额外缺点是不缩小日志。 同时备份两者是没有意义的。 如果您正在进行完整备份，则只需截断日志即可。
如果您要能够恢复到任何时间点，则必须每天（比如说）进行一次完整备份，并在一天中备份日志几次。 请参阅 http://msdn.microsoft.com/en-我们/库/ms191429(SQL.90).aspx
What you are doing is effectively a SIMPLE mode backup with bonus disadvantage of not shrinking the log. There is no point to back up both at the same time. If you're doing a full backup, you can just truncate the log.
If you're going to be able to restore to any point of time, you will have to do a full backup once a day (say) and back up the log few times during the day. See http://msdn.microsoft.com/en-us/library/ms191429(SQL.90).aspx