不带时间戳的 SQL Server 数据库备份
我使用下面的行来备份 Microsoft SQL Server 2008 数据库：
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
鉴于数据库没有更改，重复执行此行会生成大小相同但内部差异很大的文件。
如何为同一未更改的数据库创建重复的 SQL Server 备份，以提供相同的字节精确文件？我想简单的 BACKUP DATABASE 调用会在备份介质中添加一些时间戳或其他一些元信息，有没有办法禁用或删除此添加？
或者，如果不可能，是否有一种相对简单的方法来比较两个备份，看看它们是否会恢复完全相同的数据库状态？
更新：我的备份比较观点是，我每天都会备份无数数据库，但大多数数据库不会经常更改。对于大多数人来说，每年更换几次是很正常的。因此，基本上，对于所有其他 DBMS（MySQL、PostgreSQL、Mongo），我使用以下算法：
- 执行新的每日备份
- 将新备份与最新的旧备份进行比较
- 如果数据库未更改（即备份）匹配），删除我刚刚创建的新每日备份
该算法适用于我们之前遇到的所有 DBMS，但是，可惜的是，由于不可重复的 MSSQL 备份，它失败了。
I'm using the following line to backup a Microsoft SQL Server 2008 database:
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION
Given that database is not changing, repeated execution of this line yields files that are of the same size, but are massively different inside.
How do I create repeated SQL Server backups of the same unchanged database that would give same byte-accurate files? I guess that simple BACKUP DATABASE invocations add some timestamps or some other meta information in the backup media, is there a way to disable or strip this addition?
Alternatively, if it's not possible, is there a relatively simple way to compare 2 backups and see if they'll restore of the exactly same state of the database?
UPDATE: My point for backup comparison is that I'm backing up myriads of databases daily, but most databases don't change that often. It's normal for most of them to change several time per year. So, basically, for all other DBMS (MySQL, PostgreSQL, Mongo), I'm using the following algorithm:
- Do a new daily backup
- Diff new backup with the most recent of the old backups
- If the database wasn't changed (i.e. backups match), delete the new daily backup I've just created
This algorithm works with all DBMSes we've encountered before, but, alas, it fails because of non-repeatable MSSQL backups.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
正如您所猜测的，备份目录的一部分包括备份的日期和时间。
WITH COMPRESSION选项压缩备份以节省空间，但由于压缩算法的工作方式，文件中的一点点更改都会导致整个文件发生更改。如果您不希望有如此多的差异，请删除压缩选项，但比较备份文件并不是正确的方法。
如果您的数据库变化很小，那么增量备份或差异备份可能更有用。
然而，您似乎陷入了一个名为 XY Problem 的经典陷阱，因为您询问您尝试的解决方案而不是您的实际问题。是什么促使您尝试比较数据库？
As you guess part of the backup catalog includes the date and time of the backup. The
WITH COMPRESSIONoption compresses the backup to save space but a little change in the file will cause changes throughout the file because of the way compression algorithms work.If you don't want so many differences then remove the compress option, but comparing backup files isn't the way to go.
If you have a database that changes little then incremental or differential backups may be of more use.
However you seem to have fallen into a classic trap called the XY Problem as you are asking about your attempted solution rather than your actual problem. What is prompting you to try and compare databases?