SQL Maintenance Plan and Backup Compression

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
Who is Participating?
Jagdish DevakuSr DB ArchitectCommented:

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.

MrVaultAuthor Commented:
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.

Jagdish DevakuSr DB ArchitectCommented:
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.

MrVaultAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.