减少 SQL 备份的大小？

Reducing Size Of SQL Backup?

提问by leen3o

I am using SQL Express 2005 and do a backup of all DB's every night. I noticed one DB getting larger and larger. I looked at the DB and cannot see why its getting so big! I was wondering if its something to do with the log file?

我正在使用 SQL Express 2005 并每晚备份所有数据库。我注意到一个 DB 越来越大。我看着数据库，不明白为什么它变得这么大！我想知道它是否与日志文件有关？

Looking for tips on how to find out why its getting so big when its not got that much data in it - Also how to optimise / reduce the size?

寻找有关如何找出为什么它在没有那么多数据的情况下变得如此之大的提示 - 以及如何优化/减小大小？

回答by marc_s

Several things to check:

要检查的几件事：

• is your database in "Simple" recovery mode? If so, it'll produce a lot less transaction log entries, and the backup will be smaller. Recommended for development - but not for production

• if it's in "FULL" recovery mode - do you do regular transaction log backups? That should limit the growth of the transaction log and thus reduce the overall backup size

• have you run a DBCC SHRINKDATABASE(yourdatabasename)on it lately? That may help

• do you have any log / logging tables in your database that are just filling up over time? Can you remove some of those entries?

• 您的数据库是否处于“简单”恢复模式？如果是这样，它将产生更少的事务日志条目，并且备份将更小。推荐用于开发 - 但不用于生产

• 如果它处于“完整”恢复模式 - 您是否定期进行事务日志备份？这应该会限制事务日志的增长，从而减少整体备份大小

• 你DBCC SHRINKDATABASE(yourdatabasename)最近玩过吗？这可能有帮助

• 您的数据库中是否有任何日志/日志表随着时间的推移而填满？你能删除其中的一些条目吗？

You can find the database's recovery model by going to the Object Explorer, right click on your database, select "Properties", and then select the "Options" tab on the dialog:

您可以通过转到对象资源管理器找到数据库的恢复模型，右键单击您的数据库，选择“属性”，然后选择对话框中的“选项”选项卡：

Marc

马克

回答by Michael P

If it is the backup that keeps growing and growing, I had the same problem. It is not a 'problem' of course, this is happening by design - you are just making a backup 'set' that will simply expand until all available space is taken.

如果是不断增长的备份，我遇到了同样的问题。当然，这不是“问题”，这是设计使然——您只是在制作一个备份“集”，它会简单地扩展，直到所有可用空间都被占用为止。

To avoid this, you've got to change the overwrite options. In the SQL management studio, right-click your DB, TASKS - BACKUP, then in the window for the backup you'll see it defaults to the 'General' page. Change this to 'Options' and you'll get a different set of choices.

为避免这种情况，您必须更改覆盖选项。在 SQL 管理工作室中，右键单击您的数据库，任务 - 备份，然后在备份窗口中，您将看到它默认为“常规”页面。将此更改为“选项”，您将获得一组不同的选择。

The default option at the top is 'Append to the existing media set'. This is what makes your backup increase in size indefinitely. Change this to 'Overwrite all existing backup sets' and the backup will always be only as big as one entire backup, the latest one.

顶部的默认选项是“附加到现有媒体集”。这就是使您的备份大小无限增加的原因。将其更改为“覆盖所有现有备份集”，备份将始终只有一个完整备份，即最新备份那么大。

(If you have a SQL script doing this, turn 'NOINIT' to 'INIT')

（如果您有执行此操作的 SQL 脚本，请将“NOINIT”转为“INIT”）

CAUTION: This means the backup will only be the latest changes - if you made a mistake three days ago but you only have last night's backup, you're stuffed. Only use this method if you have a backup regime that copies your .bak file daily to another location, so you can go back to any one of those files from previous days.

注意：这意味着备份只会是最新的更改 - 如果您在三天前犯了错误，但您只有昨晚的备份，那您就吃饱了。仅当您的备份机制每天将 .bak 文件复制到另一个位置时才使用此方法，这样您就可以返回前几天的任何文件。

回答by John Sansom

It sounds like you are running with the FULL recovery model and the Transaction Log is growing continuously as the result of no Transaction Log backups being taken.

听起来您正在使用 FULL 恢复模型运行，并且由于没有进行事务日志备份，事务日志正在不断增长。

In order to rectify this you need to:

为了解决这个问题，您需要：

• Take a transaction log backup. (See: BACKUP(TRANSACT-SQL))
• Shrink the transaction log file down to an appropriate size for your needs. (See:How to use DBCC SHRINKFILE.......)
• Schedule regular transaction log backups according to data recovery requirements.

• 进行事务日志备份。（参见：备份（TRANSACT-SQL））
• 将事务日志文件缩小到适合您需要的大小。（参见：如何使用 DBCC SHRINKFILE ......）
• 根据数据恢复要求安排定期事务日志备份。

I suggest reading the following Microsoft reference in order to ensure that you are managing your database environment appropriately.

我建议阅读以下 Microsoft 参考资料，以确保您正确管理您的数据库环境。

Recovery Models and Transaction Log Management

恢复模型和事务日志管理

Further Reading: How to stop the transaction log of a SQL Server database from growing unexpectedly

进一步阅读：如何阻止 SQL Server 数据库的事务日志意外增长

回答by Arrabi

as you do a daily FULL backup for your Database , ofcourse it will get so big with time . so you have to put a plan for your self . as this 1st day: FULL / 2nd day: DIFFERENTIAL / 3rd day: DIFFERENTIAL / 4th day: DIFFERENTIAL / 5th day: DIFFERENTIAL

当您每天为您的数据库进行完整备份时，当然它会随着时间的推移变得如此之大。所以你必须为自己制定一个计划。第一天：FULL / 第二天：DIFFERENTIAL / 第三天：DIFFERENTIAL / 第四天：DIFFERENTIAL / 第五天：DIFFERENTIAL

and then start over .

然后重新开始。

and when you restore your database , if you want to restore the FULL you can do it easily , but when you need to restore the DIFF version , you backup the first FULL before it with " NO-recovery " then the DIFF you need , and then you will have your data back safely .

当你恢复你的数据库时，如果你想恢复 FULL，你可以很容易地做到，但是当你需要恢复 DIFF 版本时，你用“NO-recovery”备份它之前的第一个 FULL，然后是你需要的 DIFF，和然后您将安全地取回您的数据。

回答by Darknight

One tip for keeping databases small would be at design time, use the smallest data type that you can use.

保持数据库较小的一个技巧是在设计时，使用您可以使用的最小数据类型。

for Example you may have a status table, do you really need the index to be an int, when a smallint or tinyint will do?

例如，您可能有一个状态表，您是否真的需要索引为 int，而 smallint 或 tinyint 可以吗？

Darknight

黑夜

回答by Chaoix

7zip your backup file for archiving. I recently backed up a database to a 178MB .bak file. After archiving it to a .7z file is was only 16MB. http://www.7-zip.org/

7zip 您的备份文件以供存档。我最近将一个数据库备份到一个 178MB 的 .bak 文件。将其归档为 .7z 文件后只有 16MB。 http://www.7-zip.org/

If you need an archive tool that works with larger files sizes more efficiently and faster than 7zip does, I'd recommend taking a look at LZ4 archiving. I have used it for archiving file backups for years with no issues: http://lz4.github.io/lz4/

如果您需要一个比 7zip 更有效、更快地处理更大文件的归档工具，我建议您查看 LZ4 归档。我多年来一直使用它来归档文件备份，没有任何问题：http: //lz4.gi​​thub.io/lz4/