ansaurus

Question

SQL Server: why are sizes of different backups the same?

Answer 1

+4 A:

The database files (*.mdf, *.ldf) are allocated in chunks - e.g. they're using a given number of megabytes, until that space is filled up, and then another chunk (several megabytes) is allocated.

It would be really bad for performance to allocate every single byte you ever add to the database.

Due to this chunk-based allocation, it's absolutely normal to have a given size for a certain period of time - even if your database is being used and data is being added and deleted.

marc_s 2010-07-16 16:06:00

Thus if you compress the backup files (use zip/7zip) you should see that they do actually vary as there is a varying volume of 'data' within them.

Joel Mansford 2010-07-16 16:42:05

yes, but the backup only writes the allocated pages. If the .bak file is identical in size it would indicate that the same number of pages is in use, which is possible, but unlikely in an active database. I would definitely double check the backup script, see if it actually replaces the old backup or the two backups obtained so far aren't two copies of the same file. And ultimately, do a restore of the backup on a different machine, see what it contains.

Remus Rusanu 2010-07-16 17:20:54

joel thank you very much very helpful cmoment

I__ 2010-07-16 19:05:02

Answer 2

+1 A:

A SQL Server backup only contains pages of data. A page is 8k. If your changes day to day do not add or remove pages (eg deleting, adding) then the number of pages to backup stays constant.

Try a CRC check on the backup files to see what changes...

gbn 2010-07-16 16:19:38

how do i perform this?

I__ 2010-07-16 16:26:29

3rd party tool: http://www.mindworkshop.com/crc.html

gbn 2010-07-16 16:31:16

ansaurus

tags:

views:

answers:

SQL Server: why are sizes of different backups the same?

related questions