Jason Tabor
asked on
SQL File Size backup when using Maintenance Plan vs. Manual Backup method
Hello,
Question on SQL 2008 backup files when using a manual backup process (right-click on database, choose "Tasks" -> "Backup") versus using a maintenance plan in SQL Server Management Studio.
I've noticed that when I use a SQL 2008 maintenance plan the backup files for a particular database, the .bak is only ~ 9 GB. But when I manually backup this same database by right-clicking on the database entry and choosing "Tasks" -> "Backup" that the .bak file is ~ 18 GB. I've confirmed the maintenance plan is targeting the correct database, and the MDF and LDF files are approximately 9 GB and 2 GB respectively. Screenshot of the maintenance plan structure is attached.
Could this be considered normal?
Thank you in advance.
Jason
Question on SQL 2008 backup files when using a manual backup process (right-click on database, choose "Tasks" -> "Backup") versus using a maintenance plan in SQL Server Management Studio.
I've noticed that when I use a SQL 2008 maintenance plan the backup files for a particular database, the .bak is only ~ 9 GB. But when I manually backup this same database by right-clicking on the database entry and choosing "Tasks" -> "Backup" that the .bak file is ~ 18 GB. I've confirmed the maintenance plan is targeting the correct database, and the MDF and LDF files are approximately 9 GB and 2 GB respectively. Screenshot of the maintenance plan structure is attached.
Could this be considered normal?
Thank you in advance.
Jason
As long as you are always using a new backup file, I can't explain that. However, if you are providing an existing file, your setting will append the new backup, enlarging it by 9 GB each time.
i am not sure, but it seems your maintenance plan backup doing Differential /Incremental Backup not the full backup. did you check this?
check the below URL may helps.
https://dba.stackexchange.com/questions/94136/incremental-backup-in-sql-server
check the below URL may helps.
https://dba.stackexchange.com/questions/94136/incremental-backup-in-sql-server
Based on the screenshots, the maintenance plans are using compression by default. When run manually, compression is not done. When you perform the next manual backup, try going to the "Options" pane and choose to compress the backup.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.