Backup File size decreased

Question

Backup File size decreased

THolley

SSCommitted

Points: 1506
More actions
October 16, 2007 at 6:51 am

#119014

I noticed that over the weekend my backup files size decreased by almost 100 GB.
It went from 320GB down to 230GB.
The main database file size has not decreased at all.
I have made some indexing changes. Such as increasing the fill size on some of the bigger indexes. Would this causes a decrease in the backup file size?
How much should I be concerned with this decrease in backup file size?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

My guess is that there was a huge amount of free space in the pages comprising your indexes and that rebuilding them with a larger fillfactor got back a bunch of that space. I wouldn't be concerned about the drop in backup size - the backup size will be as large as the amount of data in the database, not necessarily the actual data file sizes.

To put your mind at rest, I would restore the database on another server and satisfy yourself that everything you expect is there. To be honest, you should be restoring your backups regularly anyway just to make sure that they're valid.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

THolley SSCommitted Points: 1506 More actions · Answer 2

Paul,

Thanks for your reply.

I did indeed restore the backup and all looks good.

I to think it's a good practice to restore a database, Do you restore nightly, weekly or monthy?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

I always advise people to restore all their backups - if you're taking a backup its because you think you may need to use it in the future. If that's the case, make sure it's valid as soon as you can.

I know this is a 7.0/2000 forum, but on 2005, I also advise turning on page checksums and taking backups using WITH CHECKSUM, which will test all the page checksums as the pages are read from disk into the backup. A good way to get an early warning of corruption before your regular CHECKDB runs.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Paul's a former MS guy, so he's used to a big budget.

I used to restore once a week to test servers for active databases in development. We'd do random restores on other servers for other databases, just to check that process was working.

The backup restore process in SQL Server (2000 and above) is rock solid. Other than media failure, I've never had an issue in restoring thousands of databases. If it got written out correctly, it will likely come back.

THolley SSCommitted Points: 1506 More actions · Answer 5

Steve,

Thanks for your reply.

We are on a budget here, and I have a job set up to restore the backup about once a week. When ever time may premit. We are a 24/7 shop, so I have to be selected when I can run things.

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

If you have the need (and most do) for a DR system, setup log shipping. Although this isn't a direct test of the backups, it is an indirect test, and it also gives you a quick recovery in the event that you need one.