25-05-2023, 12:05 AM
Auto backup SQL database via Maintenance Plan
1. Launch SSMS and connect to your server. Expand Management menu under an instance, then right-click Maintenance Plans to create NewMaintenance Plan, or open Maintenance Plan Wizard.
They are essentially the same, but the wizard will lead you through the whole operation. Here I will choose the former as an example.
2. You will be asked to give a name to this plan. After confirmation, you can click Toolbox on the left side and drag Back Up Database Task to the lower part of MaintenancePlan window.
3. Double-click the generated task, you can configure it in the pop-up window. First, choose Backup type from “Full”, “Differential” and “Transaction Log”. Then select one or more Databases(s) to backup. Click OK to confirm it. If you selected more than one database, you can also choose to Create a backup file for every database if you want.
Full backup includes all database objects, system tables, data, and transactions that occur during the backup.
Differential backup includes the data that has changed and transactions that occur during the backup process since the last full backup.
Transaction log backup includes transactions that have occurred on a database since the previous transaction log backup and then truncates the transaction log and then truncates a transaction log. A transaction log backup ensures database recovery to a specific point of time e.g. to a moment prior to data loss.
4. Now you can click on the calendar icon, namely Subplan Schedule to automate the database backup task. For a time based SQL backup schedule, you can keep the selection of “Recurring” as the Schedule type, and further configure the frequency and duration.
Alternatively, you can also make the task “Start automatically when SQL Server Agent starts”, or “Start whenever the CPUs become idle”.
5. Save the changes and now you’ve set up the SQL Server automatic backup. You can then find the task under SQL Server Agent and all your backups will be made according to the specified schedule.
This is how to automate SQL Server backup with GUI wizard. If you have some knowledge of T-SQL, you may consider the next method.
1. Launch SSMS and connect to your server. Expand Management menu under an instance, then right-click Maintenance Plans to create NewMaintenance Plan, or open Maintenance Plan Wizard.
They are essentially the same, but the wizard will lead you through the whole operation. Here I will choose the former as an example.
2. You will be asked to give a name to this plan. After confirmation, you can click Toolbox on the left side and drag Back Up Database Task to the lower part of MaintenancePlan window.
3. Double-click the generated task, you can configure it in the pop-up window. First, choose Backup type from “Full”, “Differential” and “Transaction Log”. Then select one or more Databases(s) to backup. Click OK to confirm it. If you selected more than one database, you can also choose to Create a backup file for every database if you want.
Full backup includes all database objects, system tables, data, and transactions that occur during the backup.
Differential backup includes the data that has changed and transactions that occur during the backup process since the last full backup.
Transaction log backup includes transactions that have occurred on a database since the previous transaction log backup and then truncates the transaction log and then truncates a transaction log. A transaction log backup ensures database recovery to a specific point of time e.g. to a moment prior to data loss.
4. Now you can click on the calendar icon, namely Subplan Schedule to automate the database backup task. For a time based SQL backup schedule, you can keep the selection of “Recurring” as the Schedule type, and further configure the frequency and duration.
Alternatively, you can also make the task “Start automatically when SQL Server Agent starts”, or “Start whenever the CPUs become idle”.
5. Save the changes and now you’ve set up the SQL Server automatic backup. You can then find the task under SQL Server Agent and all your backups will be made according to the specified schedule.
This is how to automate SQL Server backup with GUI wizard. If you have some knowledge of T-SQL, you may consider the next method.