As a database administrator, one of your primary responsibilities is to protect the data of your organization. Data is a valuable asset, and ensuring its safety is crucial. While there are various tools available for backing up data, some organizations prefer to use SQL native backups due to their cost-effectiveness and reliability.

In this article, we will explore how to automate the backup process of SQL databases using SQL Server database maintenance plans. These plans provide predefined maintenance tasks that can be used to perform various maintenance operations on both local and remote SQL Server instances.

Let’s consider a use case where we need to take full, differential, and log backups of all user and system SQL databases. The backup schedule is as follows:

• Full Backup: Every Sunday at 1 AM
• Differential Backup: Every day except Sunday at 2 AM
• Log Backup: Every 15 minutes

Additionally, the backups must be encrypted and their integrity must be checked. The backup location should be a network drive with specific subdirectories for each type of backup. The backup sets should expire after 10 days.

To create a maintenance plan for the full backup, follow these steps:

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
2. Expand the Management folder, right-click on Maintenance Plans, and select New Maintenance Plan.
3. In the New Maintenance Plan dialog box, specify a name for the plan.
4. In the Maintenance plan configuration pane, drag and drop the “Back up Database Task” from the toolbox and double-click to edit it.
5. In the general tab, select “All databases” from the Databases drop-down box.
6. Specify the backup destination path in the Folder textbox.
7. Enable the “Create a sub-directory for each database” option.
8. In the options tab, configure the backup compression, expiration, encryption, and backup integrity settings.
9. Save and close the Backup Database Task.
10. Click on the Save button in the menu bar to save the maintenance plan.
11. To schedule the full database backup, click on the calendar icon and specify the desired schedule.

Similarly, you can create maintenance plans for differential and transaction log backups by following the same process with appropriate configuration changes.

Once the maintenance plans are created, you can view them under the Management folder and the corresponding SQL Jobs under SQL Server Agent.

To test the maintenance plans, you can execute the tasks manually. For example, to generate a full backup, right-click on the “Database Full Backup.Subplan_1” job and click on “Execute”. Once the execution completes successfully, you can verify the backup files in the specified backup location.

In summary, SQL Server database maintenance plans provide a convenient way to automate the backup process of SQL databases. By following the steps outlined in this article, you can ensure the regular and secure backup of your organization’s data.