Question:
How to implement an automatic backup for Microsoft SQL Databases?
Answer:
Please note that the steps below are prepared using SQL Server 15.0.2000.5 and it might differ from other versions. The configuration below should not necessarily be the main maintenance plan. In case there is another similar plan applied, keep the existing one. It is recommended that this maintenance plan is applied in a machine that is playing the server role and is running all the time. Please note that this maintenance plan does not work with MS SQL Express Version.
Prerequisites
1. SQL Server installed (in the current steps the version 15.0.2000.5 is used)
2. Microsoft SQL Server Management Studio is installed
3. SQL Server Agent is enabled (Step 1)
Step 1
Ensure the SQL Server Agent is enabled.
Step 2
Create a new Maintenance Plan in "Management" -> "Maintenance Plan". Choose "Maintenance Plan Wizard" from the context menu and click next.
Step 3
Provide a name for the Maintenance Plan and click change to set up a schedule. Here you can set up an appropriate reoccurring schedule and click "Next" and "OK".
Step 4
Select "Check Database Integrity" and "Back up Database (Full)" and click "Next". You can then choose the databases for you want to run the tasks.
Step 5
For the backup tasks you can define the backup location (Destination Tab) and other options like compressing the backup (options Tab). After clicking "Next", you can choose the output reports path and complete the wizard.