• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Get mssql database changes on the basis of backup logs

Hi you Guys,

I need to determinate the closest dates of the significant database changes in the past. What I have are the backup logs and the backup files of the last fortnight (the rest are available only from tape). The backup strategy is full backup fortnightly, differential backup fortnigthly (with one week sliding) and daily backup for transaction logs.

Log files contain only very few information, e.g. a DIFF backup contains:

Backup database [Test] in "Diff" mode has started.
Backup file: ...\Test_Diff_2012-06-11-230044.bak
Processed 296 pages for database 'Test', file 'Test' on file 1.
Processed 3 pages for database 'Test', file 'Test_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 299 pages in 0.425 seconds (5.492 MB/sec).
Backup database [Test] in "Diff" mode has finished.

Log note is almost the same in case of FULL backup, and of course slightly different in case of TLOG backups (since that ones contain only the number of LOG pages).

I tried to extract the number of pages from every FULL and DIFF backup logs and compare them. It's OK between two FULL backups but I'm surprisedly experienced, that the differential backup between two full backups has altering number of the processed pages even while the databases are untouched. E.g. one of our "unused" (i.e quasi-read only) db has the undermentioned backup history in its data pages :

05.21 FULL 9136 pages
05.28 DIFF 32 pages
06.04 FULL 9136 pages
06.11 DIFF 32 pages
06.18 FULL 9136 pages
06.25 DIFF 48 pages
07.02 FULL 9136 pages
07.09 DIFF 72 pages
07.16 FULL 9136 pages
07.23 DIFF 56 pages
07.30 FULL 9136 pages
08.06 DIFF 32 pages

Could somebody tell me how is it possible?

Is there any way to estimate the large-scale database changes on the basis of the number of saved pages (or extents, to be more precise) which are indicated in the log files?

Or possibly there is other way to estimate the database changes (e.g. on the bases the SQL server Logs and its Archives)?

Any help would be greatly appreciated.
  • 2
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, you cannot really predict this, as the pages updated, which depends almost 100% on really the data updated/inserted/delete is organized in the pages.
with clustered indexes, you eventually could do some predictions, but only vague stuff.

the only real way to get it is to have the database run with simulation of what it will run in a normal production day.
HidasiZsAuthor Commented:
Dear angelIII,

Thanks for your answer. There is just one more baffling thing. Why the differential backups show changes (at least in its number of pages) when the database is surely unchanged?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
settings like auto update statistics, auto close etc will make that sql server will query the db anyhow (internally) for things, which will update some internal values in the db. but that overhead will be more or less "constant", and can be neglected in terms of space compared to read database traffic/changes later in production.
HidasiZsAuthor Commented:
Thanks again. I supposed that something similar should happen in the background but I could not find any technical book or spec. where this feature of the backup (especially in case of the differential backup) is detailed exhaustively. (Would you so kind to suggest some annotation or online lecture concerning to this subject?)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now