Maintenance plans love story

re: Maintenance plans love story

Ian Yates — Fri, 06 Apr 2012 09:11:44 GMT

Great script. We're an ISV and tend to have a mix of Express & Standard edition around the place at our various clients. For those with standard edition I configure a maintenance plan but the customer's IT support company rarely knows more than how to spell SQL, and tend to like to see things in the Windows task scheduler. I had a very simple version of the above script, but yours is is a bit more elegant.

What sort of approaches do you tend to recommend for monitoring of success/failure of backups? Reindexing, etc? I tend to just script things in SQLCMD and dump the output to a file, but that needs someone to know what a 100% success looks like vs a 90% success or complete failure.

re: Maintenance plans love story

Jon Harn — Fri, 06 Apr 2012 12:48:44 GMT

Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.

re: Maintenance plans love story

Wiseman82 — Sun, 08 Apr 2012 20:32:32 GMT

I've never been a big fan of maintenance plans. They have probably got better with newer versions of SQL server, but certainly in SQL 2005 they were buggy. Also, in a dev environment you might have certain databases you want to exclude from backup but you might also want to ensure that any new databases created are included in the backup automatically. Scripts work well and they offer additional flexibility.

Thanks for sharing your solution. My own solution is posted here if it helps anyone:

http://www.wisesoft.co.uk/scripts/t-sql_backup_databases_script.aspx

Ideally you would receive an alert to inform you that the backup job has failed but I also like to check manually that the backup has completed without any issues. I use this SP to have each server send me a status email on a daily basis:

http://www.wisesoft.co.uk/articles/dba_daily_checks_email_report.aspx

It's also a good idea to test backups on a regular basis - particularly for production systems.

As Ian mentioned reindexing, here is my script based solution to re-indexing:

http://www.wisesoft.co.uk/scripts/t-sql_defrag_indexes_for_database.aspx

I find that it allows me to take a more targeted approach to index maintenance compared to index maintenance plans. I use a SSRS report based on the two tables created in the script for monitoring (plus a failure notification on the agent job).

I'm work with a much smaller number of servers and the links I've posted are solutions I've developed based on my own specific requirements. If they are useful to anyone else, great!

re: Maintenance plans love story

Steve — Mon, 09 Apr 2012 14:08:22 GMT

I'd recommend looking at Ola Hallengren's solution which can be found here. http://ola.hallengren.com