SQL Maintenance Plan and Backup Compression

Posted on 2011-10-24
Last Modified: 2012-05-12
Some questions about backing up SQL using compression and SQL maintenance plans.

1. Can anyone explain how to get a report in the email using the maintenance task method? I want the subject line to say "success" or "failed" and then to put in the body of the email what was backed up, to where, with what options, when it started and completed, the size of the file, and whether it was successful or not. Can't figure this part out.

2. If I watch the destination folder of a SQL backup that uses compression it creates a file that is smaller than the actual mdf file its backing up, but then right at the end it seemingly compresses it yet again. for example our database file is 2GB with 800MB free. If I watch the folder, the initial file size of the backup file (bak) is 400MB and then right before it finishes the file shrinks even more to 178MB. Is it sending more data than it needs to?

3. Lastly, how do I make the SQL Server Agent Job Task that has a precedent of the SQL Backup Database Task, not execute if the backup fails? My agent job tasks deletes files older than 3 days, but I don't want it to do that if today's backup failed.

Using SQL Server 2008 R2 with Windows Server 2008 R2
Question by:MrVault
    LVL 14

    Accepted Solution


    For 1. -> I dont think it can be done within maintenance plans. You need to write scripts for this to be done the way you like. This will help for your 3rd question.

    For 3. -> This can be done as above or try to combine backup an cleanup in the same sub plan in maintenance plan. Create a precedent for cleanup after backup. This might help you.

    For 2.-> I need to check this. And I feel its fine. Backup or compress will not shrink the database. Shrink and then backup will definitely make a difference.


    Author Comment

    Thanks. I need a primer on the differences between steps within a SQL job, steps within a subplan, etc. Steps in a job have options like what to do if fails, passes, where to log info, etc. I don't see those within the parts of a subplan.

    Also, about 50% of the time that I save step information in a job, if I go back into it, the extra step information is gone, usually the extra steps too. Very odd stuff.

    for 3 - I don't think it's shrinking it. If I do a restore the whitespace is still there I believe.

    LVL 14

    Assisted Solution

    by:Jagdish Devaku
    Maintenance plans create an Integration Services package, which is run by a SQL Server Agent job.

    Or else you create your own SSIS packages as the way you require.

    For 3 - You are right.


    Author Comment

    Thanks.  I tested it and when it's running the backup the file size goes from 1TB to 480GB, but right when it finishes the final size of the file is 160GB. Very odd. I guess I have to watch this from time to time to see what the initial size for free space that it will need to do the backup, not the final size.

    By the way

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now