Scheduling backups – SQL Server

Scheduling the backup operations should be one of the elements that are imperative in the work of any DBA. 

This simple action will save you lots of time and effort in the future. Scheduling backups can be done through the visual interface of SQL Server Management Studio (SSMS) as we will see next.

A well-defined and structured timetable for scheduling these backup operations can prevent the system from becoming offline while users are still working.

For example, doing dynamic backups is not something we would recommend you do. But there might be cases when the system has at least a few users using it at any time of the day, at which point dynamic backups might be a solution. Otherwise, using a well-planned backup schedule is the best way of doing backups.

Scheduling backups with SSMS

SQL Server Management Studio offers support for doing the planning of backup operations through an easy-to-use visual interface.

Scheduling can be done through the aid of a Maintenance Plan which will be recurrent. Another step that should be recurrent is to discard (remove) the much older backups from your backup location.

For starters, log into SQL Server Management Studio and connect to the database. Next, go to the Object Explorer window (left side) and make sure that your SQL Server Agent is running, otherwise right-click its icon and start it.

Expand the folder named Maintenance and select the Maintenance Plan node. In order to be able to schedule such a plan you will need to have already a SYSADMIN database role, otherwise, it is possible you won’t see the Maintenance node.

In case you don’t see it, please check permissions and/or ask how you can get access to it. But if you see the Maintenance node in the folder right-click it and select New Maintenance Plan.

Enter the name of the Maintenance Plan you are going to create.

The next page that you will see is the configuration page of your newly created maintenance plan. There are two sections you will need to look over when creating your maintenance plan, they are highlighted below.

The section that is marked at the top right of the image is the one you’re going to use to set up the time for when the plan will execute. Make sure you use a time when the database is least used.

The section in the bottom left of the image is the part where you choose what type of action will occur during the maintenance plan. You can either create your own T-SQL script to do the backup operation with (T-SQL Statement Task) or choose the Back Up Database Task for the standard walkthrough.

Start by clicking on the calendar icon on the top right highlighted section, which will bring up the job schedule screen, where you will set up the time when the job will run, the frequency, and other details that can be seen below.

Be sure to set up dates and times that best suit your organization’s requirements. Most organizations do daily backups of their database and they do it when their database is least used. Continue by clicking OK when you’ve set up all the details.

From the bottom left side of the screen choose the Backup Database Task to easily set up the backup process, and drag the element onto the right window as you can see below.

Next up you should double-click on the database task, which will open up a new window where you will be able to set up the configuration related to the database backup.

First, you should start by selecting the databases you want to back up from the dropdown list. You can also select the system databases to backup at this point, which you should not forget about or ignore for long if you decide not to select them in this plan.

After selecting the databases you want to backup you can pick if you want the files to expire after a certain number of days or on a specific date.

Also, choose where the backup file (.bak file) will be created and what to do in case a backup file with the same name exists (Append or Overwrite).

Take a look over all other available options and after you selected the best combination of options that will meet your requirements click OK.

This completes the backup plan configuration and the backup files will be created at the time specified and in the folder specified by you.

Now, because backups are done frequently, it is a good practice to remove (delete) your older backups after a certain amount of time. For this, a cleanup task would be the best way to do it, along with the maintenance plan.

You can check out the following article to see how to set up a cleanup task for the situation presented above.