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.

When and how often should backups be performed?

When performing a backup of the entire database, large volumes of data are copied. In this case, the main load falls on the read/write operations of hard drives. Typically, this reduces the performance of all other hard disk operations, including the response speed of Microsoft SQL Server.

Simply stated, the smaller and faster the backup, the more frequently you can do it without negative consequences for the DBMS.

There are three types of backups:

1. Full — backup of the entire database
2. Differential — backup of what changed since the last full
3. Transaction log — backup of what changed since the last backup, including transaction log backups. This requires the database be switched to the full recovery model.

If your database is not small and a full backup is not instantaneous, then you should not bother with choosing the start time for a full backup. However, for a large database, choosing the optimal time to start a full backup is a task worth bothering with. For __full backups__, it is generally better to choose a time window of __minimum server load__. This can be either daily/nightly backups or weekly backups done every weekend.

You can analyze the server load by collecting server statistics and then compiling reports.

Differential backups usually grow over time (until a full backup is performed again), depending on the amount of data changes and the location of these changes. As a rule, the more differential backups grow, the less often they are made.

Transaction log backups typically operate with smaller data volumes, and the more frequent they are, the smaller the volume. That means that you can perform them as often as every five minutes. This allows for minimizing the amount of data lost in case of any issues.

However, remember that the less often you make full and differential backups and the more often you make transaction log backups, the more time it will take to restore them. Therefore, choosing the optimal backup plan is a matter of compromise between the space allocated for backups, recovery time and the amount of data that the business is willing to lose in case of a crash.

Example Backup Schedule

Here are some common backup plans. These are just templates intended to show what options there may be, and not specific recommendations. When creating a plan, look at your business requirements.

Lazy

• Full: every 168 hours (7 days)
• Differential: every 24 hours

This plan perfectly works with the Simple Recover Model and allows you to save space. That allows you to store backups for a longer period of time, and you will only need two files to restore your database. However, in case of a database crash, you can lose data within 24 hours.

Daily 4 times

• Full: every 24 hours
• Differential: every 6 hours

The given backup plan has a potential data loss time of 6 hours, but due to frequent full backups, it will occupy more space in storage. The restoration of such a backup is quick, as only two files need to be pulled from the storage.

Thorough

• Full: every 24 hours
• Differential: every 4 hours
• Transaction Log: every hour

This backup plan has a potential data loss time of only 1 hour, with a good recovery time.

To restore such a database, you will need to take the last full backup plus the last differential and all transaction log backups that have been performed since the very last differential backup. In this way, a maximum of 5 files will be required, which are not difficult to extract from the storage and restore.

Maniac

• Full: every 1 month
• Differential: every 24 hours
• Transaction Log: every 5 minutes

Let’s take a closer look at this backup plan, as it clearly illustrates the extremes in backup planning.

This backup plan has several advantages.

• The maximum data loss time is 5 minutes.
• Differential backups can be performed at the time of the lowest server load (since their frequency is every 24 hours).
• Takes up minimal space in the storage, as a full backup is done once a month.

Additionally, you can save even more space if you delete previous transaction log backups after creating a differential backup. This would result in a loss of data recovery precision in the past (it becomes 24 hours), but it retains the ability to restore the system to the last backed-up state.

But this plan has several important drawbacks:

• The recovery time increases. To restore a database, 290 files (1 full + 1 differential + 288 transaction log backups) may be required. These files can significantly exceed the size of the database, and you will spend a lot of time retrieving these files from the storage, unpacking them, and restoring them.
• Increased likelihood of errors in backups. The more files and the larger their size, the higher the probability of errors in these files. The failure of one transaction log backup makes all transaction log backups created after it non-functional.
• Inflating differential backups. Since differential backup can be performed 29 days after the full backup, all database pages that have been changed since the last full backup will be included in the differential backup. If there is high data variability in the database, then the size of the differential backup can grow to the size of a full backup.
• Difficult to manage. When restoring, you will need to operate on 100 files; in order not to get confused with them, it is important to have a well-thought-out file naming system.

Scheduling backups with SSMS

SQL Server Management Studio offers support for planning 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. To be able to schedule such a plan you will need to already have 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 be executed. 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. A cleanup task would be the best way to do this, 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.

Scheduling backups with Windows Scheduler

And while the SQL Server Agent is a great way to schedule backups, it is limited by the options you can choose in the interface. Instead of SQL Server Agent, you can directly use PowerShell scripts triggered using Windows Scheduler.

The advantage of a custom script is that you can do anything — send a notification to Slack, encrypt a backup with a symmetric key, save backups to several storages at once, etc.

For example, you can write a backup script that sends the backup to AWS S3, and then schedule it using Windows Scheduler. Here is an article about how to write such a script and how to schedule it.

Scheduled backup software

Scheduling backups with SQLBackupAndFTP

The popular SQLBackupAndFTP utility specializes in making a backup of a local SQL Server and sending it to cloud storage. This utility has a simple interface for scheduling backup jobs. Full, differential and transaction log backups are configured in one window.

Scheduling backups with SqlBak

SqlBak is a cloud service for managing backups on multiple servers. When creating a backup job, you can specify when and how often to run backups.