One of the essential responsibilities of SQL Server DBAs includes planning and implementing proper Backup and Recovery. There are various methods and tools for performing SQL Server backups, with each one having its advantages and disadvantages. Here is a bunch of text explaining that section. Here is a bunch of text explaining that section. Here is a bunch of text explaining that section. Here is a bunch of text explaining 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 usually recommend that our clients have native SQL backups saved to a dedicated drive, and this drive can then be backed up by some backup tool to long backup retention location.
Most common way to get backups setup is do
- FULL backup – daily
- Transaction log every 15 minutes, and the retention is 72 hours.
This way, we can quickly restore a database if needed and have the possibility to 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 backup the drive or folder where the backups are placed to extend the retention and have a second copy.
Main advantages of using SQL Server to handle backups by itself
- The MS SQL Server backup feature is free, 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 tool to do SQL Server backups
- DBAs have to implement backup jobs on each SQL instance and then work on each instance when restore is required and has additional tasks like copying the backup files to the new server where restore has to be performed, etc.
- DBAs have to rely on 3rd party support in case of failures with the tools.
Third-party backup tools for MS SQL Server
Several third-party backup tools allow you to take advantage of additional features usually not found in the SQL Server native backups, such as user-friendly GUI, great enterprise and server-level reports, and much more.
Products from RED GATE, QUEST, VACUUM, 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, it can be even better if you correctly combine it with native SQL backups.
Main advantages:
- One of the significant advantages of 3rd 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:
- The 3rd party backup tools are usually expensive, and the licensing most often or not is for each server or instance, which makes it expensive for small organizations.
- It will require either DBAs or SysAdmins 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 backups best practices
Most medium and large enterprise organizations use 3rd party backup solutions; however, many develop a custom solution for their environment using native SQL backup solutions.
The rule of thumb of backups is: If you have one copy, you have no backups. If you have two copies, you have one backup, and if you have three copies, you have two backups.
We have a few clients that keep both a local native backup and a 3rd party tool, this requires some special attention to some configuration to avoid conflicts, like breaking the backup chain, but it’s perfectly fine.
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.
Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.
