SQL Server Backup File Significantly Smaller After Table Recreation
Posted
by
userx
on Server Fault
See other posts from Server Fault
or by userx
Published on 2012-08-29T02:17:26Z
Indexed on
2012/08/29
3:40 UTC
Read the original article
Hit count: 485
sql-server
|backup
We run automated weekly backups of our SQL Server. The database in question is configured for Simple Recovery. We backup using Full, not differential.
Recently, we had to re-create one of our tables with data in it (making 2 varchar fields a couple characters longer). This required running a script which created a new table, copied the data over, and then dropped the old. This worked correctly.
Oddly though, our weekly backup files now SHRANK by over 75%! The tables don't have large indexes. All data was copied over correctly (and verified). I've verified that we are doing full and not incremental backups. The new files restore just fine.
I can't seem to figure out why the backup files would have shrank so much? I've also noticed that they get about 10 MB larger every week, even though less than that amount of data is being added.
I'm guessing that I'm simply not understanding something. Any insight would be appreciated.
© Server Fault or respective owner