October 21, 2013 at 6:35 pm
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
Full backup file is being overwritten(append) by Differential backup so file size increases.
October 22, 2013 at 12:50 am
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
October 22, 2013 at 1:31 am
Thank you very much guys..that helped a lot 🙂
October 22, 2013 at 5:02 am
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