Why is SQL Server Backup File Occasional Larger?
Every week night (not Saturday and Sunday) a stored procedure is scheduled to run to backup our SQL Server database "Altek". Altek.mdf is about 5 GB. The stored procedure uses the BACKUP DATABASE statement to create a .bak file. The backup files have been about 3.8 GB for months. Last week on Monday night the backup file was approximately 7.7GB, then it went back down to 3.8 GB every week night for the rest of the week. Now, again last night (Monday night) the backup file went back up to 7.7GB. What could cause the backup file to be twice as big on certain nights?
strickdd
It sounds like you have an incremental backup going that will do an entire backup on a certain schedule and then only backup what has changed until the next full backup.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the BACKUP command does not specify INIT, and a backup file with that name already exists, by default SQL will write the new backup *after* the existing backup, *not* over it. You must explicitly specify INIT to get SQL to write over an existing backup file.
ASKER
Yes, something else was running. We recently took an image of our server and restored it to another box. Both boxes were on-line and both boxes were scheduled to run the backup. They both put backups in the same .bak file, in effect doubling the size of the normal backup.
Hi,
Thanks for the points.
But after reading Scott's comments, I feel that his more accurately answer your question.
Can I suggest instead a split of points between me and Scott, and suggest a grade of A instead of B?
Thanks
David
Thanks for the points.
But after reading Scott's comments, I feel that his more accurately answer your question.
Can I suggest instead a split of points between me and Scott, and suggest a grade of A instead of B?
Thanks
David
ASKER
Sure. I can do that. I assigned you the points because your comment about how something else might be running triggered me to think of the other Server and figure out the problem. But Scott's post had to do with more than one backup set stored in the .bak file so it was related to the problem also. Do you know how to reassign points after a posting has been closed already? I sent this question to experts-exchange but if you know how and an tell me that'd be great.
Hi,
I think you may have done it already - the basic thing is to post a question in community support asking moderators to reopen a closed question so you can reassign points.
When the question is open, select the comments and assign points to relevant comments.
Regards
David
I think you may have done it already - the basic thing is to post a question in community support asking moderators to reopen a closed question so you can reassign points.
When the question is open, select the comments and assign points to relevant comments.
Regards
David
ASKER
Thank you both.