SOLVED: How to Set SQL Backups & A full Maintenance Plan Using Only SQL Server Managment Studio

To backup SQL properly there are serveral easy items you need to setup:

1. double click on each datbase and set your BACKUP MODEL to FULL
2. go to MANAGEMENT > MAINTENANCE PLANS and setup the three plans shown below
3. go to SQL SERVER AGENT > JOBS and make sure your jobs appear here (and make sure the SQL SERVER AGENT service is running!)

MAINTENANCE PLAN 1: DAILY SQL MAINTENANCE

1. Drag out the BACKUP DATABASE TASK and set it to backup ALL DATABASES, FULL with DEFAULT COMPRESSION
2. Drag our the MAINTENANCE CLEANUP TASK and set it to delete backed up database files (by setting the FILE EXTENTION field to BAK – you do NOT enter .BAK) older than X days (I like 4 for X)
3. Drag our the MAINTENANCE CLEANUP TASK and set it to delete backed up logs (by setting the FILE EXTENTION field to LOG – you do NOT enter .LOG) older than X days (I like 4 for X)
4. Connect all of these in the order shown by dragging the green arrow at the bottom of one box to the other
5. Set this to run several times a day (like every TWO hours from 9:00am to 5:59pm)

MAINTENANCE PLAN 2: INTRADAY SQL MAINTENANCE

1. Drag out the BACKUP DATABASE TASK and set it to backup ALL DATABASES, FULL and set the CREATE A BACKUP FILE FOR EVERY DATABASE to what a location you like but set the BACKUP FILE EXT to TRN
2. Drag our the MAINTENANCE CLEANUP TASK and set it to delete backed up logs (by setting the FILE EXTENTION field to TRN – you do NOT enter .TRN) older than X days (I like 5 for X)
3. Connect all of these in the order shown by dragging the green arrow at the bottom of one box to the other
4. Set this to run daily at an off hour (like 10pm)

MAINTENANCE PLAN 3: WEEKLY SQL MAINTENANCE

1. Drag out the CHECK DATABASE INTEGRITY TASK, set to ALL DATABASES and INCLUDE INDEXES
2. Drag out the REBUILD INDEX TASK
3. Drag out the SHRINK DATABASE TASK
4. Drag out the UPDATE STATISTICS TASK
5. Drag out the HISTORY CLEAN UP TASK
6. Drag out the MAINTENANCE CLEANUP TASK
7. Connect all of these in the order shown by dragging the green arrow at the bottom of one box to the other
8. Set this to run once a week at some very off hour (like 4AM Saturday morning) AFTER you have completed a full backup.