各位,
假设您收到 SQL Server 数据库(2005 或 2008)的断开连接备份,并将其恢复到您的 SQL Server 实例。
有没有办法(是否有系统目录或其他东西)来找出该特定数据库上最后一次写入操作发生的时间? 我希望能够找出特定数据库备份的日期 - 不幸的是,这并没有在任何地方明确记录,并且检查所有数十个数据表的最高日期/时间戳也不是真正的选择....
有任何想法吗? 当然 - 我可以查看 *.bak 文件的日期/时间戳 - 但我可以从 SQL Server (Management Studio) 中找到更准确的信息吗?
谢谢!
马克
Folks,
Assume you receive a disconnected backup of a SQL Server database (2005 or 2008) and you restore that to your SQL Server instance.
Is there a way, is there a system catalog or something, to find out when the last write operation occured on that particular database? I'd like to be able to find out what day a particular database backup was from - unfortunately, that's not really being recorded explicitly anywhere, and checking all dozens of data table for the highest date/time stamp isn't really an option either....
Any ideas? Sure - I can look at the date/time stamp of the *.bak file - but can I find out more precisely from within SQL Server (Management Studio) ??
Thanks!
Marc
发布评论
评论(4)
您可以按照 RESTORE HEADERONLY ="nofollow noreferrer">此处
应该会为您提供所需的信息。
You can try
RESTORE HEADERONLY
on your backup file, as described herethat should give you the information you're looking for.
如果您有权访问最初运行备份的 SQL Server 实例,则应该能够查询
msdb
:有多个与备份集相关的表:
通过查询这些表,您可以确定上次备份发生的时间、备份的内容发生的备份类型以及文件写入位置。
If you have access to the SQL Server instance where the backup was originally run, you should be able to query
msdb
:There are several table relating to backup sets:
By querying these tables you can determine when the last backups occurred, what type of backups occurred and where the files were written to.
有点晚了,但应该是你想要的。
对数据库的每次写入都是日志文件中的一个条目。 其中有一个 LSN。
这必须至少存储在日志恢复的备份中。
那么,如何将 LSN 与日期时间匹配呢?
我以前从未使用过这个(只是玩了一下这个答案)。 有些写入很可能是备份本身的一部分,但您应该能够通过一些探索来区分它们。
A bit late, but should be what you want.
Each write to the database is an entry in the log file. Which has an LSN.
This must be stored in the backup for log restores at least.
So, how to match LSN to a datetime?
I've never used this before (just had a play for this answer). Some writes are very likely part of the backup itself, but you should be able to distinguish them with some poking around.
据我所知,在主数据库中存在一个日志表,其中存储了每次写入的详细信息。 但我不确定您是否需要启用日志机制 - 因此默认情况下不记录,您必须启用它。
例如,在 Oracle 中,解决方法是存在一个可以查询的系统数据库表 Log。
如果情况并非如此 - 您仍然可以自己编写一个触发器并将其应用于所需的每个表/列并自己进行日志记录。
as far as I know in the master database there exists a Log-table where every write is stored with detailed information. BUT I'm unsure if you need to enable the Log-mechanism - so that the default is not to log and you have to enable it.
In Oracle for example it is the way around there exists a system-database table Log that you can query.
If that is not the case - you could still write yourself a trigger and apply that on every table/column needed and do the logging yourself.