Launch the SSMS (SQL Server Management Studio)
Once you are logged in got to the 'Maintenance Plans' folder under 'Management'. Right click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard'.
Click Next
Select 'Separate schedules for each task' radio button and click next.
Select backup Database Full, Differential and Transactional Log check boxes and click next.
Click Next
Once you are logged in got to the 'Maintenance Plans' folder under 'Management'. Right click on 'Maintenance Plans' and choose 'Maintenance Plan Wizard'.
Click Next
Select 'Separate schedules for each task' radio button and click next.
Select backup Database Full, Differential and Transactional Log check boxes and click next.
Click Next
Full Backup Settings
This is where you set-up the backup settings for the full backup task.- Select the database you want to backed up (All user database).
- Choose when you want the backups to expire. In case I choose (14 days - optional). This will overwrite the oldest backup file for rotation.
- Choose your backup media (disk).
- Choose a location for your backups. (Default or to you liking).
- Verify the integrity of the backup.
- Click the configure button at the bottom right to configure the scheduling options for this task.
- Choose the schedule type (Recurring).
- Choose the frequency of the backup. In this example full backups are running on Monday, Wednesday and Friday (Alter this to fit your backup plan).
- Adjust the Daily frequency (what time does your backup need to run)?
- Adjust the start and end date. In this example I left it at 'No End date'.
Click Ok when you are finished. Go back to the maintenance plan wizard and click next.
Differential Backup Settings
Next we will configure the differential backup task.
- Select the databases you want backed up (All users databases).
- Choose when you want the backups to expire. In this case I choose (14 days). This will overwrite the oldest backup file for rotation.
- Choose your backup media (disk).
- Choose a location for your backups (Default or to your liking).
- Verify the integrity of the backup.
- Click the configure button at the bottom right to configure the scheduling options for this task.
- Choose the schedule type (Recurring).
- Choose the frequency of the backup. In this example differential backups are running on Tuesday, Thursday, Saturday and Sunday (Alter this to fit your backup plan).
- Adjust the daily frequency (What times does your backup need to run)?
- Adjust the start and end date. In this example I left it at "No End Date".
Click OK when you are finished. Go back to the maintenance plan wizard and click next to configure the transaction log backup.
Transaction Log Backup Settings
Next, set-up the transaction log backup.
- Choose your database (All user databases).
- Do not check 'backup set will expire'.(We will setup a cleanup task next).
- Choose your backup media (disk).
- Choose your location for your backups. (Default or to your liking). ****Copy and paste the path to notepad. You will need to reference this in later steps.****
- Verify the integrity of the backup.
- Click the configure button at the bottom right to configure the scheduling options for this task.
The transaction log backup intervals will need to be determined based on transaction logs growth. Therefore it may be necessary to run the transaction log backups in shorter intervals to prevent transactions logs from growing.
- Choose the schedule type (Recurring).
- Choose the frequency of the backup. In this example the transaction log backups are running daily.
- Adjust the daily frequency (I set mine to run every hour).
- Adjust the start and end date. In this example I left it at 'No end date'.
Click Ok when you are finished. Go back to the maintenance plan wizard and click next to finish the wizard.
Now that you are back to the SSMS main screen you need to refresh the maintenance plan. Press F5 to refresh the new settings. You should see the new maintenance plan listed.
Transaction Log Cleanup Task
Next, select the 'Sub-plan_2' in the maintenance plan. Drag over 'Maintenance Cleanup Task' onto the white space and place it under the transaction log backup.
Right click on the 'Maintenance Cleanup Task' and choose edit.
This section allows you to setup the maintenance cleanup task. This task will clean up the transaction logs after three days. This will keep the one hour transaction logs for three days until the maintenance clean up task deletes the old data. The transaction log clean up will need to include a series of 3 days, this will ensure if you need to revert back to the second differential you can apply the transaction logs from that period. The goal is to have enough transaction logs between the full and differential backups.
- Choose 'Backup files'.
- Choose 'Search folder and delete files based on extension'.
- **Paste in the copied path that you copied earlier**. Make sure you include the same path that your transaction logs are backing up to. And enter the File extension type (.trn).Also include the first-level sub-folders. Use trn and not .trn, it will confuse SQL.
- Set the file age to '3 days'.
- Click 'Ok' to return to the SSMS main screen.
Drag the green arrow from the differential backup task to the Maintenance cleanup task. Double click on the connected green line.
Configure the value to 'Completion'.This allows the task to become conditional. "If the differential backup did not run then do not run the transaction cleanup task.If the backup did run proceed with the cleanup task."
Click 'OK' to return to the SSMS main screen. You should now see that the green line has become blue.
You are now finished with you maintenance task setup. You can adjust your backup plan to your needs. Be sure to save your work. ****Save your work by going to File >> Then Save All****
Test your Setup
After you are done setting up your maintenance plan you will need to verify that it works. You can choose to wait a few days to see if the job completes or you can force the job to run.
Browse down to 'Jobs' under 'SQL Server Agent' and right click on the first maintenance plan and choose 'Start job at This Step'.
This will run the first section of the maintenance plan you created.
At this step if the job completes continue on to the next step of the maintenance plan and test run the setup. Do this for all sub plans you created in the maintenance plan. If all your steps run without error you are finished.
Errors
If your job fails when you are performing a test run you will need to view the history to see what failed. Right click on the failed sub plan and select 'View History'
This will bring up new window that shows the history
If your job failed you will see a red X next to the time you ran the job. Drag up the dialog box below to see the details of the error.
Troubleshoot the error and repeat the test-job to see if it cleared up. Once your maintenance plan is solid it is a good practice to check back in a few days to see if it is running the way you want. Check to see if the .bak files are being removed after the expiration. Check to see if the transaction logs are being clean up after 3 days.
