SQL Server: Set up a backup plan

Introduction

In this article, I will explain how to set up a backup plan with SQL Server.

In this tutorial, we will use the wizard available in SSMS to configure the database to save as well as the schedule and then we will modify the plan to indicate the location.

Prerequisites

  • SQL Server (no Express backup plans do not work)
  • A location to store backups, in the case of a network share, the computer object must have NTFS rights
  • The SQL Agent must be started.
  • Access to the server through the SQL Server Management Studio (SSMS) console.

Configuration of the backup plan

From the SSMS console, expand the Management folder 1 , then right click on Maintenance Plan 2 and click on Maintenance Plan Assistant 3 .
SSMS - lauch wizard

When launching the wizard, click Next 1 .
Wizard maintenance

Name maintenance plan 1 , it is also possible to put a description if necessary. Click Next 2 .
Name plan

Choose the maintenance plan tasks, check the box Save the database (complete) 1 and click Next 2 .
Select task

Click Next 1 . In the case where several tasks are configured one can choose the order of execution.
task order

Click Select one or more 1 , check the databases to save 2 and click OK 3 .
select db

Click Edit 1 to configure the schedule.
Config schedule
Configure the schedule to save 1 and click OK 2 .
Schedule

Now that the databases are selected and the schedule configured, click Next 1 .
Config OK

Configure reports as needed and click Next 1 .
Report config
Click Finish 1 to create the maintenance plan.
Plan configured

The maintenance plan is created 1 , click on Close 2 .
plan created

Now that the plan is created, we will configure the backup storage location. Open the plan by clicking on 1 and double click on the backup task 2 .
Edit plan

Go to the Destination tab 1 , choose the option Create a backup file for each database 2 . It is possible to create subfolders for each database. Then enter the UNC path of the network share 3 .
config share network

Then go to the Options tab 1 , configure the expiration of a backup set 2 and click OK 3 .
Expire delay

Save the maintenance plan by clicking on diskette 1 .
save plan

The databases will be saved according to the configured plan.



Related Posts


Sharepoint 2013 foundation: installation and configuration
In this tutorial, we will see how to set up Sharepoint 2013 foundation on Windows 2012R2. Although this version at the time of writing this article is more than 5 years old, it allows to discover Sharepoint before making its acquisition. SharePoint i

GLPI: add custom fields
In this tutorial, we will see how to add custom fields using the Fields plugin. Fields allows you to add custom field blocks in the different elements of GLPI to customize the information. To illustrate this tutorial, we will add fields to the comput

Veeam Agent 3 for Windows: Installation and Configuration
The Veeam Agent allows physical or virtual computer backup directly from Windows unlike Veeam Backup & Replication which manages the backup of virtual machines from the Hypervisor (VMware, Hyper-V). Version 3 of the agent is available since Janua

Leave a Comment