none
Question regarding database backups

    Question

  • I am learning about database backups in SQL server 2008.

    I took a full database backup using following query:

    BACKUP DATABASE TEST_DB TO DISK='D:\TEST_DB.bak'

    The above query created a BAK file on D drive whose size is 2.5mb

    Processed 304 pages for database 'TEST_DB', file 'TEST_DB' on file 1.
    Processed 2 pages for database 'TEST_DB', file 'TEST_DB_log' on file 1.
    BACKUP DATABASE successfully processed 306 pages in 0.039 seconds (61.110 MB/sec).

    I immediately ran differential backup query on the same database:

    BACKUP DATABASE TEST_DB TO DISK='D:\TEST_DB.bak' WITH DIFFERENTIAL

    now the BAK size increased to 2.95 mb

    Processed 40 pages for database 'TEST_DB', file 'TEST_DB' on file 2.
    Processed 1 pages for database 'TEST_DB', file 'TEST_DB_log' on file 2.
    BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.011 seconds (29.119 MB/sec).

    What I don't understand is that I did not make any changes to the database between the two backups, then how come differential backup increase the size of the BAK file?

    Can anyone please explain me..m confused


    • Edited by rockstar283 Tuesday, October 22, 2013 12:17 AM
    Tuesday, October 22, 2013 12:17 AM

Answers

  • Hi,

    The reason for the size differential backup is, using the same file for holding backups. You use "D:\TEST_DB.bak" for the first statement and size of the file becomes 2.5mb. Your next statement uses the same file for differential backup, hence it appends differences to the same file, making it 2.95mb. The actual size of the differential backup is 0.45mb.

    If you use "WITH DIFFERENTIAL, INIT", statement overwrites the file removing all old backup sets.

    ----------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    Please use Mark as answer (Or Propose as answer) or Vote as helpful if the post is useful.

    • Marked as answer by rockstar283 Tuesday, October 22, 2013 12:48 AM
    Tuesday, October 22, 2013 12:35 AM

All replies

  • Hi,

    The reason for the size differential backup is, using the same file for holding backups. You use "D:\TEST_DB.bak" for the first statement and size of the file becomes 2.5mb. Your next statement uses the same file for differential backup, hence it appends differences to the same file, making it 2.95mb. The actual size of the differential backup is 0.45mb.

    If you use "WITH DIFFERENTIAL, INIT", statement overwrites the file removing all old backup sets.

    ----------------------------------------

    Dinesh Priyankara
    http://dinesql.blogspot.com/

    Please use Mark as answer (Or Propose as answer) or Vote as helpful if the post is useful.

    • Marked as answer by rockstar283 Tuesday, October 22, 2013 12:48 AM
    Tuesday, October 22, 2013 12:35 AM
  • how come differential backup increase the size of the BAK file?

    The 0.45MB may just be the overhead with differential backup: date, database size information, etc.

    You need to test on larger database.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, October 22, 2013 12:48 AM
  • Got it..Thank you so much :)
    Tuesday, October 22, 2013 12:48 AM