As a database administrator, one of your top priorities is to ensure the safety and integrity of your SQL Server databases. One way to achieve this is by implementing a robust backup and restore strategy. In this article, we will explore the features and capabilities of ApexSQL Backup, a powerful tool designed specifically for managing database backup and restore jobs in Microsoft SQL Server.

Automating Backup Jobs

Creating and maintaining full backup chains is essential for any disaster recovery plan. However, manually running these jobs on a daily basis can be challenging and time-consuming. ApexSQL Backup simplifies this process by allowing you to automate backup jobs through schedules or custom scripts in the command line interface. You can also automate other maintenance tasks such as deleting old backup files, rebuilding fragmented indexes, and verifying created backups.

Components of ApexSQL Backup

ApexSQL Backup consists of four main components:

1. Windows application: This is the main part of the program, managed via a graphical user interface (GUI). It is used for creating, managing, and executing database backup and restore maintenance jobs in SQL Server.
2. Command line interface (CLI): This console application enables users to automate backup, restore, and log shipping maintenance jobs by running custom scripts. All jobs in a script can be easily configured using simple CLI switches.
3. Central repository database: This SQL Server database stores parameters for all created jobs as well as configuration settings data for the application.
4. Agent: This simple Windows service is used for communication between the application and SQL Server instances.

How It Works

To use ApexSQL Backup effectively, all four components need to be installed and connected. The application and central repository database are typically installed on the machine used for managing and monitoring SQL Server instances. The ApexSQL Backup agent must be installed on each server machine hosting one or more SQL Server instances that need to be managed. Once all agents are running, each SQL Server instance can be added to the list of manageable servers.

Backup Job Management

Making a foolproof disaster recovery plan should be a top priority for every database administrator. ApexSQL Backup allows you to configure and automate the entire process of creating any type of SQL Server database backups. To make a database backup, simply select the SQL Server instance, choose the databases to back up, select the backup type (full, differential, transaction log, or filegroups backup), and set the backup destination. You can execute the backup job immediately or schedule it to run at specific intervals.

Backup Templates

Another useful feature of ApexSQL Backup is the ability to create backup templates. These templates allow you to save preferred settings for a backup job and apply them to one or more databases whenever necessary. Backup templates support all features available for regular backup jobs, including scheduling and advanced options like media, verification, compression, and encryption settings.

Restore Job Management

ApexSQL Backup also provides a comprehensive set of tools for configuring and managing restore operations. You can select the SQL Server instance, choose the database to restore, and specify the restore type (full, differential, or transaction log restore). Advanced options allow you to customize the restore process, including setting the location for data and log files, including DBCC operations, and running custom T-SQL scripts after the restore job is complete.

Log Shipping Jobs

Log shipping is a cost-effective way to implement a disaster recovery plan. ApexSQL Backup simplifies the configuration of log shipping jobs, allowing you to select the primary (production) server, source database, backup and shared folder paths, and restore schedules. You can also add multiple destination servers and databases to the log shipping configuration.

Reorganize and Rebuild Indexes

ApexSQL Backup includes basic index maintenance operations, such as rebuilding and reorganizing indexes. Regularly performing these operations can significantly improve the performance of high-traffic servers. The tool allows you to select the SQL Server instance, database, and tables or views for the reorganize or rebuild job. You can also schedule these operations to run automatically and configure email notifications for job status updates.

Monitoring Backup Jobs

All backup and restore jobs, as well as other maintenance tasks, can be easily monitored and managed in ApexSQL Backup. The Schedules view displays all scheduled jobs, allowing you to execute, enable, disable, or delete them. The Activities view provides detailed information about executed operations, including success and failure statuses. The History view shows the complete backup history for any selected database, allowing you to track backup chains and identify the best restore points in case of a disaster.

ApexSQL Backup is a powerful tool that simplifies the management of database backup and restore jobs in SQL Server. By automating these tasks and providing comprehensive monitoring capabilities, it helps database administrators ensure the safety and integrity of their databases.