SQL Maintenance Plan and Backup Compression
Posted on 2011-10-24
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