Main Differences Between MS SQL Server Native Backups And 3rd Party Tools

One of the key responsibilities of SQL Server DBAs is to plan and implement proper backup and recovery strategies.

Various methods and tools are available for performing SQL Server backups, each with its own set of advantages and disadvantages.

Here is an in-depth explanation of that section.

SQL Server native backups

SQL Server has a built-in mechanism to automate regular database backups through maintenance plans or custom jobs created on each SQL Server instance.

We typically recommend that our clients save native SQL backups to a dedicated drive, which can then be backed up by some backup tool to a location with long-term backup retention.

The most common way to set up backups is to perform:

• A full backup daily.
• A transaction log backup every 15 minutes, with a retention period of 72 hours.

This way, we can quickly restore a database if needed and can go back in time up to 72 hours. It’s not common to go back further than that.

When long-term retention is needed, we recommend using some backup tool to back up the drive or folder where the backups are placed to extend the retention and have a second copy.

The main advantages of using SQL Server to handle backups by itself:

• The MS SQL Server backup feature is free and included in the licensing.
• Microsoft’s support for failures with the backups and restores is often limited to native SQL Server backups.
• Performing backups through maintenance plans or jobs is considered more stable. They have been used for years. DBAs are well-educated on how to use them.

Disadvantages of using third-party tools to do SQL Server backups:

• DBAs have to implement backup jobs on each SQL Server instance and then work on each instance when restore is required and have additional tasks like copying the backup files to the new server where restore has to be performed, etc.
• DBAs have to rely on the 3rd party support in case of failures with the tools.

Third-party backup tools for MS SQL Server

Several third-party backup tools offer additional features not typically available in SQL Server’s native backups.

These include a user-friendly GUI, comprehensive enterprise and server-level reports, and much more.

Products from Redgate, Quest, IDERA, and other vendors promise to take your SQL Server strategy to the next level.

No doubt that there are excellent backup tools in the market.

When properly configured, combining it with native SQL backups can enhance its effectiveness even further.

Main advantages:

• One of the significant advantages of third-party backups over native backups is the possibility of managing the backups and restores from a central server.
• Third-party backup tools are generally much easier to use.
• These tools offer the possibility to generate enchanted reports, scheduling, better encryption, and compression.

Main disadvantages:

• Third-party backup tools are usually expensive, and the licensing is often per server or instance, making it costly for small organizations.
• It will require either DBAs or system administrators to learn and update themselves about the backup tools.
• If a new SQL Server version is released, the backup tool may not work, so we have to wait for the vendor to release a new version to work on more recent versions of SQL Server. 

SQL Server database backup best practices

Most medium and large enterprise organizations utilize third-party backup solutions.

However, many develop custom solutions tailored to their environment using native SQL backup solutions.

The rule of thumb for backups is:

If you have one copy, you have no backups.
If you have two copies, you have one backup.
If you have three copies, you have two backups. 

We have several clients who maintain both a local native backup and a third-party tool. This approach necessitates special attention to configurations to prevent conflicts, such as breaking the backup chain. However, it is perfectly acceptable.

Also, we had cases where clients were counting only on 3rd party tools for backup, and when a restore was needed, it failed to provide the expected RTO and RPO.

For this reason, when we see clients using these tools, we ask if they have it properly configured and if there is some business continuity plan (BCP) to test the expected RTO and RPO.

During a severity one incident is not the best moment to try it and figure out the RTO and RPO will not be achieved.