3rd Party Backup and Restore/Recovery Tools for SQL Server

SQLServerF1

One of the most important responsibility of SQL Server Database Administrators (DBAs) is planning, implementing and testing Backups and Restore/Recovery in SQL Server. 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.

SQL Server ships with database backup capabilities, but it is on server to server basis and there is no central solution where backups and restores can be managed from a central server. Thus there are various Backup, Restore and Recovery tools for SQL Server are available in the market with various features. More information regarding choosing best backup/restore tool can be found in this article

Below are some of the popular Backup and Restore/Recovery 3rd party tools for SQL Server.

NetVault LiteSpeed from Quest – LiteSpeed tool is one of the most mature, feature-rich SQL Server backup products available. It is very widely used around the world by many organizations in almost all the industries. They also provide an excellent support and regular updates to their product. It provides integration with TSM, Adjustable Backup compression ratios, backup Encryption, • Central backup repository, supports Object level restores, etc. There are some discounts of about 20-20% offered for bulk licenses. Price is on higher side, but has many good features and is a reliable product.

SQL Safe from Idera – SQL Safe is cost effective with the features provided. This tool supports TSM integration, and is capable of object-level restores, central management for backups, etc. Object level recovery is not supported with this tool.

SQL Backup and HyperBac from RedGate – RedGate has two backup/restore products which are SQL Backup and HyperBac. same licensing applies for both the products. These are highly cost effective backup products available in the market and can be bundled with other tools in your environment. There are some limitations to the tools as it does not provide TSM integration.

There are other backup/restore tools available from CommVault which is Simpana, “SQL Server Backup” from DataBK.

What does SQL Server Backups Consists of? – 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.

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Choose Best SQL Server Third-Party Backup and Restore Products or Tools

SQLServerF1

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.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Backup and Restore Frequently Asked Question and Answers (FAQ) in Interviews Part 1

Backups in SQL Server are considered as one of the most important thing which need to be performed regularly to get our data back in case of unexpected issues or a disaster. As a DBA it is very important to be very familiar with the Backup and Restore concepts. Almost in every SQL DBA interview, there will be questions on Backup and Restore.

Below are some of the top SQL Server Backup and Restore Frequently Asked Interview Question and Answers.

What are different types of Backups in SQL Server?
In all versions of SQL Server, below are the type of backups that can be performed.

SQL Server FULL Database Backups – A Full database backup contains the whole database at the time the backup finished. These backups contains all the data in the databases and active portion of the transaction log file too.

SQL Server Differential Backups – Differential Backups contains the data extents which has been changed after a Full Backup.

SQL Server Transaction Log Backup – Transaction Log backups can only be performed on databases whose recovery model is set to either FULL or BULK-LOGGED. Transaction log backups consists of log records inside a transaction log file(.ldf), since last log backup. This allows a databases to be recovered to a point in time, incase of a failure.

SQL Server File Backups – File Backups in SQL Server allows to backup one or more important database files, rather than backing up entire database. There are various rules in place on how you can perform a File backup and how to restore a file backups.

SQL Server Filegroup Backups – Filegroup Backups in SQL Server allows us to backup one or more FILEGROUPS, which contains one or more database files. By default each database has one FileGroup which is PRIMARY filegroup and we can create additional filegroups and add one or more data files to it. If there are Read_Only filegroups, then one can just backup any Read_Write Filegroups instead of backing up the entire database.

SQL Server PARTIAL backups – PARTIAL backups in SQL Server was introduced with SQL Server 2005 which allows us to backup the PRIMARY filegroup, all Read-Write filegroups and any optionally specified files. This is a good option when there are Read-Only filegroups in the database and we do not want to backup the entire database all of the time. PARTIAL backups can be performed for only a Full or Differential backup, but cannot be used with Transaction Log backups.

SQL Server Copy-Only backups – Copy-Only backups are special purpose backups which do not disturb regular backup sequence. Usually performing different types of backup affect the way the database is restored, if you do not want a certain backup to change or disturb a restore sequence, then a Copy-Only backup needs to be performed. A copy-only backup cannot be used as a differential base later. Copy-Only can be used with FULL, Differential and Transaction Log backups.

What are Recovery Models in SQL Server?
Recovery Models are part of database options and determines the allowed possible restore sequence. Recovery model basically dictate SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models as mentioned below.

Full Recovery Model in SQL Server – SQL Server FULL recovery is very commonly used recovery model allows SQL Server to store all transactions data in the transaction log until a transaction log backup occurs or the transaction log is truncated. Full recovery model is the most complete recovery model which allows us to recover all of the data to any point in time, as long as all backups are available.

SIMPLE recovery model in SQL Server – SIMPLE recovery model is the most basic recovery model in SQL Server where every transaction is still written to the transaction log, but once the transaction is committed and the data has been written to the data file the space used for that transaction can be reused for new transactions. As the log file is getting reused, we cannot perform log backups and thus cannot recover data to point in time. We can only restore an FULL database in case of disaster and causes loss of data. This recovery model is used for databases which are not critical and where data loss is acceptable.

Bulk-Logged Recovery Model in SQL Server – In Bulk-Logged Recovery Model all transactions are logged similar to FULL recovery model except for transactions which involve bulk operations like BULK INSERT, CREATE INDEX, SELECT INTO, etc. These operations are not fully logged in the transaction log and therefore do not take as much space in the transaction log, but does not allow point-in-time recovery of database during the time when there are bulk transactions. This recovery model is used if there are regular bulk operations which happen on a database.

How to plan a backup strategy in SQL Server?
There are various types of backup options available in SQL Server and as a DBA we need to plan what backups need to be performed and how frequently those backups need to be performed.

The planning for database backups change based on server to server, or can be different in different environments depending upon Size of database, importance of data, amount of allowed data loss and available storage to store the backups.

Example1:
If the database is small in size and is not very critical and data loss of 24 hours is acceptable, then below would be the backup plan

– Set the database to SIMPLE recover model.
– Perform daily FULL Backups to local disk during the night.
– Copy the daily backups from local disk to Backup Server or Tape.
– At all times, store atleast 3 days of FULL backups on local disk and 1 month of Full backups on Tape.

Example2:
If the database is large in size(100 GB+) and is very critical and data loss of 10 minutes is only acceptable, then below would be the backup plan

– Set the database to FULL recover model.
– Perform weekly FULL Backups to local disk in the night.
– Perform Daily Differential Backup once in a day, except for the day when we are performing the FULL backup.
– Perform Transaction Log backup every 10 minutes.
– Copy the weekly backups from local disk to Backup Server or Tape.
– Copy the daily differential backups from local disk to Backup Server or Tape.
– Copy the transaction log backups from local disk to Backup Server or Tape.
– At all times, store atleast 1 FULL backup on local disk and 1 month of Full backups on Tape.
– At all times, store atleast recent 3 differential backups on local disk and 1 month of differential backups on Tape.
– At all times, store atleast 3 days worth of transaction log backups on local disk and 1 month of transaction log backups on Tape.

How do you recover a database which was crashed or corrupted using database backups?
This is most important questions and is asked almost in every interview. Failing to answer this question may reduce your chances of getting the job.

Normally, you will be give a backup scenario and will be asked on how you will you recover the database using the available backups. Below is the scenario.

Scenario: There is a backup strategy in place where you have weekly FULL database backup which is performed every Sunday at 12:00 AM. There are daily Differential backup which is performed at 12:00 AM, except on Sundays. There are transaction log backups which are performed every 1 hour. Database crashed on Tuesday at 06:10 AM, now you need to recover as much data as possible, how will you recover the data?

Answer: First, we need to restore FULL database backup which was performed on recent Sunday at 12:00 AM. This needs to be restored with NoRecovery option. Next restore latest differential backup which was performed on Tuesday at 12:00 AM with NoRecovery option. Restore all transaction log backups in sequence taken from Tuesday 12:00 AM till 05:00 AM with NoRecovery option and finally restore the last transaction log file taken at 06:00 AM with Recovery option. This should now bring the database online with all the data till Tuesday 06:00 AM. There will be 10 minutes worth loss of data which was stored between 06:00 AM to 06:10 AM.

How will database recovery model impact database backups?
Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE, DIFFERENTIAL and FULL.

– When a database is set to SIMPLE recovery model, only FULL and DIFFERENTIAL backups are allowed. Transaction Log backups cannot be performed.
– Under BULK-LOGGED or FULL recovery mode, all types of backups are allowed.

Can we perform a SQL Server compressed backup?
Starting SQL Server 2008, we can perform a compressed backups. All types of backups can be performed with compressed option. Prior to SQL Server 2008, backups may need to be compressed using third party tools and but need to regularly test to make sure that the backups are be uncompressed and restored successfully.

How can you automate performing database backups in SQL Server?
We can created maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups commands or create a Stored procedure to perform backups of databases on certain conditions and create jobs and add job step to to run the stored procedure and then schedule the job. Also, there are many third party tools available to automate performing database backup and restores.