SQL 2016 – Creating a Backup Maintenance Plan
- Click on Start > Programs > Microsoft SQL Server 2016 > Microsoft SQL Server Management Studio
- Connect to the server using ‘sa’ credentials.
In the Object Explorer panel expand the Management folder, right-click on the Maintenance Plan folder, select Maintenance Plan Wizard.
In the Object Explorer panel expand the Management folder, right-click on the Maintenance Plan folder, select Maintenance Plan Wizard.
As a result, the SQL Server Maintenance Plan Wizard opens, click Next> button.
Select Plan Properties: Assign a name to the Maintenance Plan, click the Change button, to schedule when the Maintenance Plan runs.
Select Plan Properties: Assign a name to the Maintenance Plan, click the Change button, to schedule when the Maintenance Plan runs.
New Job Schedule: Change the Frequency from Weekly to Daily, set the Occurs once at time if the backup only occurs once a day, if the backup is to be create multiple time a day select the ‘Occurs every’ radio button and fill-in the interval.
New Job Schedule: Change the Frequency from Weekly to Daily, set the Occurs once at time if the backup only occurs once a day, if the backup is to be create multiple time a day select the ‘Occurs every’ radio button and fill-in the interval.
Click OK to close the New Job Schedule form, then click Next.
- Maintenance Tasks: select Back Up Database (FULL) and Maintenance Cleanup Task, click Next.
Maintenance Task Order: Re-arrange the task so that the Maintenance Cleanup Task occurs before the Back Up Database (Full) task.
Maintenance Task Order: Re-arrange the task so that the Maintenance Cleanup Task occurs before the Back Up Database (Full) task.
Maintenance Cleanup Task: Specify the path to where the backup files are written, be sure the extension = bak,
Maintenance Cleanup Task: Specify the path to where the backup files are written, be sure the extension = bak,
Define Back Up Database (Full) Task: On the General tab, select the Database(s) dropdown list: Check the MANITOU database, click OK.
Define Back Up Database (Full) Task: On the General tab, select the Database(s) dropdown list: Check the MANITOU database, click OK.
The Database(s) parameter will now show ‘Specific Databases’, select the Destination tab.
Accept the default backup folder path, be sure the Backup File Extension is bak.
Select the Options tab. Select the Verify Backup Integrity checkbox. Click Next.
- Report Options: Accept the default values and click Next.
Complete the Wizard: The Maintenance Plan Wizard has gathered all of the required information for the options that you have chosen, click Finish to build the Maintenance Plan.
Complete the Wizard: The Maintenance Plan Wizard has gathered all of the required information for the options that you have chosen, click Finish to build the Maintenance Plan.
Maintenance Plan Wizard Progress: Each Action has a status of Success, as well as the overall Maintenance Plan. If any Action fails the overall plan will also fail. Click Close to close the Maintenance Plan Wizard.
Maintenance Plan Wizard Progress: Each Action has a status of Success, as well as the overall Maintenance Plan. If any Action fails the overall plan will also fail. Click Close to close the Maintenance Plan Wizard.
The new Maintenance Plan will show under the Management node as shown below:
The new Maintenance Plan will show under the Management node as shown below:
Congratulations – you are done. Check tomorrow morning to ensure the backup was created successfully and look again in a few days to ensure you are only retaining the desire number of backup files.
