MrVault
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
By the way
ASKER
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.
Thanks!