Post reply

Question regarding database backups

  • October 21, 2013 at 6:35 pm

    #279049

    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

  • October 22, 2013 at 12:36 am

    #1660373

    Full backup file is being overwritten(append) by Differential backup so file size increases.

  • October 22, 2013 at 12:50 am

    #1660376

    You're writing two backups to the same file. You didn't specify to overwrite, so SQL appends. Hence that file now has two backups and is the size of the two backups combined.

    You generally wouldn't want to do this for a production system. One backup to one file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 22, 2013 at 1:31 am

    #1660391

    Thank you very much guys..that helped a lot 🙂

  • October 22, 2013 at 5:02 am

    #1660441

    SQL Show (10/22/2013)

    Full backup file is being overwritten(append) by Differential backup so file size increases.

    No, not overwritten, added to. As Gail said, it's adding the differential to the full within the same file.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply