Get mssql database changes on the basis of backup logs

Posted on 2012-08-27
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
    LVL 142

    Expert Comment

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

    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 142

    Accepted Solution

    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

    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?)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now