Backups in SQL Server are one of the most important responsibility of SQL Server Database Administrators (DBAs). There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.
What are SQL Server Backups: – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.
Although Backups and restores can be performed using SQL Server Management Studio (SSMS) and using T-SQL queries, but in many environments, to make the Backup and Restore process more manageable they use Third-Party Backup tools to perform backups and restores on SQL Server databases. Although there are many third-tools it is important to choose the tools which suits best for our environment.
Below are some of the parameters which can be used to determine the best Backup and Restore/Revoery tools for your environment.
Integration with TSM – One of the important part of a Backup/Testore product is its integration with the TSM which allows you to backup and restore directly to the TSM Server. Backing up directly to TSM allows saving disk space and IO bandwidth savings. Another benefit is that the direct restores can be performed on to other servers which saves time and space.
Backup Compression – Starting with SQL Server 2008, backup compression feature was introduced. However backup tools provide their own compression methods where in the backups can be further compressed and also backups are compressed on SQL Server versions which does not have this feature available.
Backup Encryption – Encryption of SQL Server Backups was introduced with SQL Server 2014, but there are many backup tools which provide custom encryption features which encrypts and stores the SQL Server backups.
Usability – Although various backup and restore tools provide variety of features, but it makes lot of difference when it comes to the ease at which DBAs can install, configure, manage and use these tools. If Backup tool installation or configuration is complicated then it will not be adopted by DBAs as they will not be comfortable using it or will have to spend lot of time on managing it.
Product Support – One of the key factor evaluated before making a decision to but the Backup or Restore tools is the level and kind of support option available for the product. Some of the tools which provide very good support often are mostly used in the organizations.
Price/Cost of the Product – Depending on the features provided the price or cost of the tools vary. This is a very important factor to decided weather to go ahead in buying the product. Over a period of time many new backup and restore tools were developed by various organizations which brought more competition this reduced the cost or price of these tools. Also various discounts are provided based on number of licenses brought and kind of servers the tools are being used in.
Degree of Restores – SQL Server provided Backups or restores to be performed at database, files or filegroup level, but the 3rd party backup and restore tools along with standard will also support object level restores, where in one can restore a particular table or stored procedure, which is more useful and helpful in Development or Test environments.
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.