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.
Thus, everything is simple, 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:
- Full — Backup of the entire database
- Differential — Backup of what has changed since the last full
- Transaction Log — Backup of what has changed since the last backup, including transaction log backups, but this requires the database to 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 on the weekend.
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 5 minutes. This allows for minimizing the amount of data lost in case of any issues.
However, remember, the less often you make full backups (and differential) and the more often the transaction log, 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.
- Full: every 168h (7 days)
- Differential: every 24h
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 24h
- Differential: every 6h
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.
- Full: every 24h
- Differential: every 4h
- Transaction Log: every 1h
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.
- Full: every 1 month
- Differential: every 24h
- Transaction Log: every 5 min
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 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 diff 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 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 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. 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.
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, and so on.
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.