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
See also: Understanding SQL Server Backup Types
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
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
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
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'
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, Maintenance plans and a 3rd party tool for managing SQL Server backups, ApexSQL Backup
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 statement12345USE CurrencyExchangeGOBACKUP DATABASE [CurrencyExchange]TO DISK = N'G:\DatabaseBackups\CE.bak'WITH CHECKSUM;
Use this next SQL code to create a differential backup12345678910111213USE CurrencyExchangeGOBACKUP 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;GO
For creating a transaction log backup, use this SQL statement123BACKUP LOG [CurrencyExchange]TO DISK = N'F:\TLogBackups\CE.log';GO
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, backup 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
Creating scheduled SQL Server backups with ApexSQL Backup
To summarize, scheduling an automated backup job with SQL Server Agent requires some SQL coding and at a minimum, we’ll have to make two jobs, one for the differential and one for the transaction log backup.
To reduce the requirement for T-SQL and for multiple jobs, which maintains all of the customization and granular configuration we may need, we can consider ApexSQL Backup, a 3rd party tool for SQL Server backup automation
ApexSQL Backup is a SQL Server backup manager that provides for automatic scheduling of SQL Server backup jobs, in one task, while preserving the backup chain for easy point-in-time restores (database rollback). ApexSQL Backup is also able to run multiple tasks at the same time and provides a turnkey, out-of-the box solution for scheduling database backups for one or all databases in just a few clicks.
To create a scheduled SQL Server backup using ApexSQL Backup:
Select the Backup option, in the Home tab of the main menu:
Specify a name for the Server and database as well as the backup type. All backup types are supported. There is an option, also, to select all databases.
Click on Add destination button to specify backup destination:, after the databases have selected,
paste the backup destination path in the Folder box, in the Add backup destination form. Alternatively, use the built-in folder browser and select the backup destination manually. Click OK, once you are done, to complete the process and save the backup destination.
To configure the backup job schedule, click on the Schedule ration button, and the schedule wizard will open automatically. Once the frequency has been configured click OK to save.
Specify backup options such as backup verification, compression, encryption etc in the Advanced tab. ApexSQL Backup includes both the Verify backup step when finished option, and the Perform checksum step before writing to media option when creating a backup task without the need for writing a separate SQL script:
Click OK button of the backup wizard to complete the process. If the task is configured properly, an info message will appear confirming the same. Click Finish to complete the wizard.
Here is a summary of all three approaches on how to schedule a database backup compared side to side:
|Maintenance Plans||SQL Server Agent||ApexSQL Backup|
|Run jobs without SQL scripting required||X||X|
|Backup all databases without SQL coding||X||X|
|Execute a task with a single job||X||X|
|Run multiple tasks at once||X|
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
Latest posts by Robert Seles (see all)
- Multiple methods for scheduling a SQL Server backup automatically - July 17, 2018
- Ola Hallengren’s SQL Server Maintenance Solution – Index and statistics maintenance - January 23, 2018
- Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check - January 10, 2018