One of the most important responsibility of SQL Server DBAs include planning and implementing proper Backup and Recovery. Although most of the times, the backups may not seem to be required, but its importance is known when there is an disaster or corruption with the database and we have to recovery as much of data as possible. Without database backups this may not be possible.
There are various methods or tools for performing SQL Server backups with each one having its own advantages and Dis-advantages. SQL Server has built-in mechanism to facilitate automating regular database backups through maintenance plans or custom jobs created on each SQL Server instance. Other methods include installing third party backup tools on central server and manage backups and restores of all SQL Servers from central repository.
Often when planning a proper backup and recovery strategy various options are considered and their advantages and disadvantages are taken into consideration and then finally the best method for their organization will be chosen.
Below are some of differences or advantages and disadvantages with SQL Server Native Backups and 3rd party backup/recovery tools.
– SQL Server Native backup and restore solution is free, meaning it comes built-in with SQL Server. But DBAs need to manually create maintenance plans or jobs on all SQL Server instances to perform backups. 3rd party backup tools are expensive and the licensing will be most often or not is for each server or instance which makes it very expensive for small organizations.
– Performing Backups through maintenance plans or jobs are considered more stable, where as the backups and restores performed by 3rd party tools cannot be fully trusted because they use their own way of performing backups and compressing the backups. 3rd party backups need to be tested very frequently to make sure that they can be recovered to avoid any surprises during disaster.
– Support for failures with the backups or restores from Microsoft is often limited to Native SQL Server backups. DBAs have to rely on the 3rd party support in case of failures with the tools.
– One of the very important advantage of 3rd party backups over native backups is managing the backups and restores from central server. For native solution, 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.
– If a new SQL Server version is released, then the backup tool may not work with the new SQL Server versions, so we have to wait for the vendor to release new version of backup tool to work on newer versions of SQL Server. Native SQL backups does not have this issue as we can just create maintenance plan jobs on new SQL Server instance.
– There can be bugs with the 3rd party backup solutions so need to work with vendor and fix the problems.
– Additional management is required for installing and configuring 3rd party backup tools and applying patches to the 3rd party backup tools.
– Need to learn about the 3rd party backup tools on how they work and what options are available and will require either DBAs or SysAdmins to learn and update themselves about the backup tools.
Considering all the above aspects, one has to choose which solution works best for their environment. Most of the medium and large enterprise organizations use 3rd party backup solutions, however there are many who develop custom solution for their own environment using native SQL backup solution.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.