Unanswered: Backup size difference within a SQL Server agent job
I have an issue which I hope someone might be able to shed some light on.
For our developers to test fixes/new code in our application with current and reliable user data I have set up a SQL server agent job to nightly restore a backup of our production databases to a test database.
For reasons that I don't need to go into here, I then purge some of the tables within that test database which contain archived documents that are not needed in a test environment but which take up about 80-90% of the overall database size. Once this purge is completed, I then take a backup of that test database - the aim being to have a smaller backup file I can transmit across the network between offices.
However, the backup I take after purging the tables comes out at the same (well, close enough to the same) size as the original backup that was restored from where I would expect it to be around 10-20% of the original size.
If I add a second backup immediately following the first, within the same SQL server agent job I find that the second backup file has shrunk.
Eg, I restore a 35Gig backup of the production database to a test database, I then purge the necessary archive tables and take 2 backups immediately following each other. The first backup is 35 Gig, the second backup is around 5 gig.
I've tried truncating the log file on the test database after the tables have been truncated but this achieves nothing.
So, my question is what has changed between the first backup and the second backup that causes the difference in size, or more importantly what can I do before the first backup to ensure that it is of the smaller size that I am looking for?
When you delete data from a table those transactions are committed to your logfile. This means you have made free space in your MDF but your logfile (LDF) will grow by the same size as the data you've deleted.
When you do a full backup of the database you backup both your MDF and LDF data in your BAK file - this is why the size hasn't gone down at all.
The backup also sets a checkpoint in your database. Since all the deletes have been committed to a full backup those transactions are now cleared from the logfile. This means both your MDF and LDF have more free space in them which in turn means your second backup is much smaller.
Depending on your situation it may be worthwhile looking into changing your recovery model to simple when you restore the database to a non prod environment. The simple recovery model means that the deletions you do won't be logged and you can easily shrink your logfile.
I agree with Wilvis to change the recovery model...
And also shrink the data file (.mdf) before backup other wise where ever you restore, it may need the same amount space as production becasuse your data will be deleted when the tables truncated but the db shell will be the same size.