Multiple methods for scheduling a SQL Server backup automatically

SQL Server backups are an essential part of every good disaster recovery strategy. That is good. But setting up such backups to run effortlessly, is the goal. In this article, we’ll review the types of backups, recommended practices and three different methods for automatically setting up SQL Server backups on a schedule. Note: these solutions can also be used in combination with one another

Different types of SQL Server backups

Full. This is the most common backup type and it includes everything including objects, system tables data, and transactions that occur during the backup. With a full backup, you can restore your database to the state, when it was backed up. Full backups won’t truncate your transaction log but if your database is in full recovery you. should also consider transaction log backups

Differential. This type of backup offers a means to maintain a complete history of your database but without storing redundant data. A differential backup retains data since the last full backup. A differential backup is only useful if used in tandem with a full backup, but allows you to delete/remove previous differential backups as they are redundant

Transaction log. This backup type will backup all of the transactions that have occurred since the last log backup or truncation, then it will truncation the transaction log. This will capture all transaction information, both DML and DDL, that has occurred on the database. With a transaction log backup, you can restore a database to a particular point in time aka point-in-time recovery, like right before a data loss event

File-group and File: This type of backup is best for larger databases. This type of backup will store all related data in files or file groups (one or more). To use file backups to successfully restore a database, the transaction log also has to be backed up to cover all of the file groups from beginning to end.

Copy-only. This backup type is usually used on an ad-hoc basis and as not to disturb and existing process of database backups, since the transaction log copy-backup will ignore the copy backup. Otherwise, this is the same as a full database backup. Copy backups can’t be used for differential backups and transaction log backups

Backup best practices

Location

Separating the location where backups reside from the server where the database, itself, exists is critical, because otherwise, some failures that affect the database might also mitigate your ability to use the backups to recover from it, for example physical drive failure.

Without explicit guidance for the file location as to where to store the backups, SQL Server will use the default database location.

Please note that simply changing the default file location, will have no effect on previously saved backup files, it will only determine the location where new backups are stored.

Automating and scheduling backups

Getting a database successfully backed up, to the file location and with the configuration settings you want is just a first step. Once accomplished, you will want to be able to replicate this process over and over again, automatically and on a schedule. Scheduling automated backups is critical for ensuring database continuity while reducing the manual effort required to achieve this.

Regular database backups not only are a great insurance policy against accidental data loss and other disaster type scenarios, they also provide point-in-store capability (for transaction log backups) and reduced downtime, if you do have to restore (shorter backup windows > less data loss)

How often should I schedule backups? This depends on your business requirements and Recovery Point Objectives (RPO), for example, if your standard is no more than 15 minutes of data can be lost, then then backups need to be scheduled every 15 minutes

Note: If your database is in full recovery mode you can use ApexSQL Log to avoid full restores. ApexSQL Log can isolate and reverse rogue transactions, mitigating the damaged data without having to do a full restore. It can also replay previous transaction to restore data that was lost

You can also use ApexSQL Data Diff to compare the current database to the last database backup (without even restoring it), identifying different/damaged rows and surgically repairing them with a synchronization script, again, without having to restore the backup.

Restoring and testing backups

A backup is only good if it can be restored successfully and this must be verified, and continuously re-verified to ensure your backup and restore strategy can be executed successfully when needed. Backups must be restored in a test environment and fully verified to ensure they meet all business requirements and various permutations, contingencies and contexts of the company recovery strategy

When testing backups many variables existing including environment, data, recovery time frames and data loss windows, application downtime and maximum acceptable threshold for data loss

Verifying backups

SQL Server backup verification includes the following checks

The backup was created successfully
It is currently intact, physically and that all the files not only exist but are also readable
The backup can be restored, when it is needed
All the transactions are consistent

Note that verifying a backup doesn’t ensure the integrity or completeness of all of the data but if the original backup was created with WITH CHECKSUMS, that allows WITH CHECKSUMS to be used to verify the data, at an aggregate level, to give a reasonable confidence level that no data was lost. Let’s look at a few backup cases below to provide some practical examples

with T-SQL:

When we include the CHECKSUM statutement we can later ensure the integrity of the data, when the backup is complete and written to file. To accomplish this, see the following example:

BACKUP DATABASE [CurrencyExchange]

TO DISK = N'G:\DatabaseBackups\CE.bak'

WITH CHECKSUM;

SSMS provides the facility for backup verification using CHECKSUM when creating a backup task

Two options are available in SSMS to implement this functionality including Verify backup when finished and Perform checksum before writing to media. These options are meant to bullet-proof your database backup process and identify any failures

It is important to include this functionality in your automatically scheduled backups as well

We’ll demonstrate, in this article, how to automatically create and schedule a SQL Server backup using SQL Server Agent jobs and a Maintenance plans

Creating a scheduled backup using SQL Server Agent jobs

To schedule an automated backup with a SQL Server Agent job:

Right click Jobs and select New job from the context menu, in the object explorer panel, in SSMS, under the SQL Server Agent node.
Enter a name for the job in the New job dialog

Click on the New button, under the Steps tab, and create a new backup step by adding a SQL statement

USE CurrencyExchange

BACKUP DATABASE [CurrencyExchange]

TO DISK = N'G:\DatabaseBackups\CE.bak'

WITH CHECKSUM;

Use this next SQL code to create a differential backup

USE CurrencyExchange

BACKUP DATABASE [CurrencyExchange]

TO DISK = N'G:\DatabaseBackups\CE.bak'

WITH CHECKSUM;

BACKUP DATABASE [CurrencyExchange]

TO DISK = N'G:\DatabaseBackups\CE.bak'

WITH DIFFERENTIAL;

WITH CHECKSUM;

For creating a transaction log backup, use this SQL statement

BACKUP LOG [CurrencyExchange]

TO DISK = N'F:\TLogBackups\CE.log';

A necessary pre-requisite to creating a transaction log or differential backup is that a full SQL Server database backup must already exist. So you must create a full database backup, if the database has never been backed-up before, before creating a differential.

Transaction log and differential backups play well together with full database backups and can be used in tandem. By taking a sophisticated approach to backups you can achieve a high level of database continuity and insurance/protection from data loss while minimizing backup file storage requirements. You can, for example, schedule a full SQL Server backup every 12 hours, but a differential, much more often, say every 4 hours and finally, back up your transaction log every 15 minutes. The key is finding the sweet spot between mitigating potential data loss and storage requirements that is optimal for your organization

Click OK to add a step and OK. again, to create a job:
To schedule a job, click New, in the New Job dialog, under the Schedule tab
In the Job Schedule tab, select a recurring frequency and a start date and click OK:

Right click the job and select Start job at step option, to check a created job in the SSMS Object Explorer pane, under the SQL Server Agent then Jobs node

To back up all databases, using SQL Server Agent, for one SQL Server instance there are two approaches; both will require some additional effort. One way is to create an SSIS package using the Backup Database Task option from the SSIS toolbar and create a SQL Server Agent job, then schedule it

The approach is to create a SQL script to backup all databases in the SQL Server Agent Job Step dialog.

Creating a scheduled SQL Server backup with SQL Server Maintenance Plans

You can either use SQL Server Maintenance Plans, manually, to create a scheduled backup or use the Maintenance Plan Wizard.

To create a scheduled SQL Server backup task manually:

Click Maintenance Plans, under the Management node in the SSMS Object explorer, and select New Maintenance Plan.
Select Back Up Database Task: from the Maintenance Plan Tasks toolbar

A Check database integrity task option exists, as an option in the Maintenance Plan Wizard, allowing this functionality to be included in your maintenance plan.
Click on the newly added plan to configure the backup settings:
Use the Sub plan scheduling option, to schedule a SQL Server Agent job
When a plan is properly configured click Save and this will create a new job under the SQL Server Agent then Jobs folder.

The Maintenance Plan Wizard is easier and more convenient but provides less granular control and fewer configuration options

To automatically schedule a SQL Server backup using the SQL Server Maintenance Plan Wizard see the following steps:

Right click Maintenance Plans and select the Maintenance Plan Wizard option, In the Object Explorer pane under the Management node
In the Select Plan Properties window provide a name for the plan. click the Change button to schedule the job:
Select the Back Up Database option, in Select Maintenance Plan Tasks, and the Check data integrity setting. Check data integrity task performs an consistency check of the data and index pages within the database:
Configure the maintenance task, in the next dialog, by specifying a database for backup and the backup configuration settings. Check the Verify backup integrity option, in the Define Back Up Database Task window also:
Click Finish once you are satisfied with the configuration of your job:

Maintenance plans, due to their value-added features and point and click interface (vs having to write scripts), are a good option for people who are less experienced. But the ease of use comes at the expense of an inability to customize many options or exercise much granular control

Furthermore, the extensibility of Maintenance Plans is extremely limited in that they are basically one-trick ponies, unable to process multiple tasks at once. One plan can support only one maintenance task, so by definition running one plan will result in executing only one task. Imagine creating a plan to delete older backup files but only being able to delete one file type at a time. The “solution” is to create more and more plans aka “Plan creep” to scale task processing and remember each Maintenance Plan requires its own SQL Server Agent job if it is to be scheduled. The result of this can quickly become an unmanageable ecosystem of plans, that requires a lot of effort and maintenance to organize

To learn how to create backups with SSIS see Creating Backups with SSIS
To learn how to backup with Docker containers see Understanding Backup and Restore operations in SQL Server Docker Containers

Here is a summary of both approaches on how to schedule a database backup compared side to side:

	Maintenance Plans	SQL Server Agent
Run jobs without SQL scripting required	X
Backup all databases without SQL coding	X
Execute a task with a single job		X

Robert Seles

Robert is a SQL Server and IT enthusiast with many years of experience as a computer repair technician.

He has a great passion for new technologies, science, video games, and medieval history. He also likes reading, writing, and learning new languages.

Robert is currently working for ApexSQL LLC as Software Sales Engineer where he specializes database backups and indexes.

View all posts by Robert Seles

Different types of SQL Server backups

Backup best practices

Location

Automating and scheduling backups

Restoring and testing backups

Verifying backups

Creating a scheduled backup using SQL Server Agent jobs

Creating a scheduled SQL Server backup with SQL Server Maintenance Plans

References

Related posts: