[Webinar] Learn how to a build a cloud-first strategyRegister Now


Get mssql database changes on the basis of backup logs

Posted on 2012-08-27
Medium Priority
Last Modified: 2012-08-28
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.
Question by:HidasiZs
  • 2
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38336401
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.

Author Comment

ID: 38339885
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?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38339909
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.

Author Closing Comment

ID: 38339989
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?)

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question