Maintenance Plan VS Dump Database

  • Hi All,

    Anyone knows why size of database backup is far larger when we use DUMP DATABASE command, when compared to size of the database backup using maintenance plan??

    Regards,

    Murali Damera.

    .

  • Maybe the maintenance plan is running the shrinkdb first?

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Check whether you have database optimization job scheduled to run prior the database backup job in maintenance plan.

  • Andy : I am sure Maintenance Plan is not using shrinkdb first. Because the database size is not changing. If we perform the same thing the next day, I see the same result differences.

    Allen: I have optimization jobs scheduled after the Database backups. If we perform the same thing the next day, I see the same result differences.

    .

  • I have tried this in various Servers I have got, Which spans thru SQL Server 6.5, 7.0 and 2000. I see the result differences in all three versions.

    .

  • I tested backup of "pubs" database with both maintenance plan and manually backup and both backup files have exactly same size.

    The only possible cause I could think in your case is data were inserted to your database or indexes changes between your two backups.

  • Let me run both ways in parallel on a user database now. I will update the page.

    .

  • Make sure there are no insert/delete/update activites to your user database during your test.

  • This is completely strange to me now. When I did this test just now, the sizes are exact. I remember and I still have one of the old backup copy (when I ran the same test 2 weeks back) the difference is few Hundreds MB morethan the maintenance job backup size.

    I apologise. I would keep monitoring this and will identify the exact time.

    Any idea what could be the reason for the diff. when I ran two weeks back??

    .

  • The maintenance plan is probably set up to backup the transaction log prior to db backup. This clears them from the db backup. The plain backup will include transaction log activity. It would be larger if data mods ocurr, but Pubs will probably be the same for both methods.

Viewing 10 posts - 1 through 9 (of 9 total)

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