SQL Server Backup and Restore

In order to understand the importance of SQL Server backup and restore, imagine a situation in which one of your colleagues creates a SQL statement without a WHERE clause and executes it. The result of this is all the data is wiped off.

SQL Server Backup and Restore

If such a query is executed in a TEST environment, then you are fortunate enough but what if it was executed in a Production Environment? A disaster would hit your organization and all management and clients would rush to the IT people to solve the issue.

In such a situation, having SQL Server Backup and Restore is a critical aspect of any DBA’s career. It is the difference between having and losing your job. For a DBA, it is extremely crucial to restore a database to a normal state without any errors or corruption in case the database has been corrupted and data is wiped out, dropped or the database is offline.

This does not mean that DBAs are supposed to take backups of their databases each day. In fact, a conscious DBA should be able to handle any kind of disaster regardless of when and how it occurs, by using a strong SQL Server Backup and Restore Strategy.

SQL Server Backup and Restore – Backup Categories and Types

The main part of SQL Server Backup and Restore Strategy of any DBA should involve backing up the data file or filegroups and log files of any database.

A well-designed SQL Server Backup and Restore Strategy will ensure that data availability is maximized  and while doing so also minimizing data loss, while also taking into consideration your particular business requirements.

Also, a good practice involves setting up the database and backups on separate devices. By choosing this approach, if the device which contains the database fails, then your backups will not be available. If you place the data and backups on separate devices also benefit from improved I/O performance for both writing backups and the production use of the database.

So, the best way to protect your database is to make backups according to your schedule. If you are working with the full version of SQL Server you can tune the job scheduler. Also, you can use some third-party backup tool if you need to perform SQL Express backup.

There are three broad categories of backups:

1. Database Backup:

This type of backup involves creating a copy of the data and objects of primary and any secondary data file. Also, different subcategories of database backups exist, for a different extent of purpose:

1. 1. FULL Database Backup: This is the most extensive method of backing up your data and involves creating a copy of your entire data and objects.
   2. Differential Database Backup: A differential backup is based on the most recent, previous FULL backup of the data that is included in the differential backup. A differential backup captures only the data that has changed since that FULL backup. This is known as the base of the differential. A differential backup includes only the data that have changed since the differential base.

Before you restore a differential backup, you must restore its base.

 2. Transaction log backup:

It backs up all changes to log file (.ldf file) that have happened since the last log file backup or if it is the first time the log file is being backed up then since the last FULL backup. The process is similar to the differential database backup, but in this case, it works only with log files.

Every record in the log file is issued a LOG Sequence Number which is incremental. This sequence number is marked as a baseline by the system during every log back. The next time log back is initiated it would be starting from the last log sequence number which has been set as a baseline.

 3. File Backup:

When a database starts increasing in size into the hundreds of Terabytes, then backing up the database by using a FULL Backup strategy will decrease performance drastically.

Thankfully, in SQL Server our work is easier due to a concept called File Systems. With this we can split the data files among different files and filegroups. Also, by using this method we can only back up the modified files and filegroups, thus saving us from backing up the entire database.

By doing so, we ensure that the performance of the database is not compromised and we are also ready for disaster situations:

1. 1. 1. FULL file backup: All data and objects of marked files and filegroups are backed up.
      2. Differential file backup: Similar to the other differential strategies, this process backs up the data and objects of the files and filegroups since the last full file backup.
      3. Partial Backup: This strategy will back up only the writeable portion of the database, and files and filegroups will not be backed up unless specified.
      4. Differential Partial Backup: All data and objects will be backed up since the last FULL partial backup.

SQL Server Recovery Categories and Types

A recovery model is a database configuration option which you choose when creating a new database. Option which determines whether or not you need to back up your transaction log. How transaction activity is logged and whether or not you can perform more granular restore types that are suitable for audit, such as file and page restores.

All SQL Server Backup and Restore operations occur within context of one of three available recovery models:

1. SIMPLE:

In such case, when data is not critical and transaction density is low or static, and this state does not change often. Here we can choose Simple Recovery model. In case of disaster, any changes from the last full backup need to be redone as there are no way to retrieve the changes. This option cannot be used in the following cases:

• • • Log shipping
    • DB Mirroring
    • Point in time Restore
    • Page Restore
    • No log backup is required

 2. FULL:

In this case, when data is critical and there is zero tolerance for data loss, choose this restore option. All restore operations are fully supported. No work loss issues, except if the tail of the log is damaged, changes since the most recent log backup must be redone.

 3. BULK-Logged:

This model is similar to FULL recovery model, except this model can have a high impact on the performance of the system due to heavy loading of data files. This model does not support a Point in Time type of restore. No work loss issues are experience, except if the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

 Note: It is possible that a question might arise regarding the restoration of a single table from a database. Unfortunately, the answer is it is not possible. SQL Server does not have this feature unless we use third party tools which integrate with SQL Server.

Selecting the most appropriate SQL Server Backup and Restore Strategy for your organization

Before deciding which SQL Server Backup and Restore option suits your organization best please stop and think how much downtime is tolerable for your organization? 15 minutes, 30 minutes, 60 minutes or more?

               A graphical representation might help you in deciding which option would benefit your business.

1.        FULL Backup:

This type of plan is highly suitable for organizations with small databases where the data is less important and is easily recoverable.

Advantages:

• • No concept of log file backup, thus data can be easily recovered

Disadvantages:

• • Due to a full backup of the database, every time space required might develop into an issue.
  • In case of a disaster, recovery is only possible since the last FULL backup.

In case of using SSMS (SQL Server Management Studio) to do the backups before presented, it is possible to use scripts to back up your databases.

BACKUP DATABASE master
TO DISK = 'D:\backup_master.bak'
WITH FORMAT;

The WITH FORMAT option specified at the end should be used when creating the first restore of the database or when (caution!) intending to overwrite the previous backup file.

The general syntax of this script is:

BACKUP DATABASE [database_name] TO [backup_device] WITH (FORMAT)

Caution when using WITH FORMAT!!!

2.        FULL + Differential

This strategy is suitable for organizations which have large databases. But which have a low transaction density and in which some data loss is acceptable.

 

Advantages:

• • Compared to FULL backups, less space is required and data recovery is more precise.

Disadvantages:

• • In case of disaster, the backup since the last differential can be restored. Thus being more precise, but restoration is slower than when using FULL backups

Also, in case of using SSMS (SQL Server Management Studio) to do this type of backup, it is possible to use scripts.

-- First, we create a full database backup
BACKUP DATABASE master
TO master_1
WITH INIT;
GO

After a time we decide to do a DIFFERENTIAL backup and for that purpose we use the following script

-- Creates a differential database backup, appending the differential backup
-- to the backup device which contains the FULL database backup
BACKUP DATABASE master
TO master_1
WITH DIFFERENTIAL;
GO

The general syntax of this script is: 

BACKUP DATABASE [database_name] TO [backup_device] WITH DIFFERENTIAL

3.        FULL + Differential + Transaction log

This SQL Server Backup and Restore strategy is highly suitable for businesses which have databases that have a high degree of transaction density and where data loss is unacceptable.

Advantages:

• • Recovery of data is extremely precise. So this is the best method to pick if the integrity of your data is crucial to your organization

Disadvantages:

• • Due to large sizes of the databases, restoring data through this process is extremely complex and requires a lot of effort