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, resulting in all of the data being destroyed.

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 their IT staff to solve the issue.

In such a situation, having an SQL Server backup and restore strategy is critical for a database administrator (DBA). It could be 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 conscientious 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 an SQL Server backup and restore strategy for 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 data loss is minimized, 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 that contains the database fails, then your backups will not be available. If you place the data and backups on separate devices, you 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 a third-party backup tool if you need to perform an 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 different extents and purposes:

   • 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.
   • 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 changed data 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, therefore 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. 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 writable 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 that you choose when creating a new database. It’s an 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 the context of one of three available recovery models:

1. Simple:

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

   • Log shipping
   • Database mirroring
   • Point in time restore
   • Page restore
   • When a 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 that 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 experienced, except if the log is damaged or bulk-logged operations have 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 that 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, 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 the 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 (careful!) intending to overwrite the previous backup file.

   The general syntax of this script is:

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

   Please use caution when using WITH FORMAT!!!

2. Full + differential backup

   This strategy is suitable for organizations that 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 the 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 backup

   This SQL Server backup and restore strategy is highly suitable for businesses that 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 the large sizes of the databases, restoring data through this process is extremely complex and requires a lot of effort

   Also, if you’re using SQL Server Management Studio (SSMS) to perform various types of backups, including full, differential, and transaction log backups, you can utilize scripts for seamless execution.

   -- First, we create a full database backup
   BACKUP DATABASE [database_name]
   TO [backup_device]
   WITH INIT;
   GO

   After a time, if a differential backup is required, we can 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 [database_name]
   TO [backup_device]
   WITH DIFFERENTIAL;
   GO

   For transaction log backups, we ensure continuous data integrity and recovery by executing the following script:

   -- Initiates a transaction log backup for the designated database
   BACKUP LOG [database_name]
   TO [backup_device]
   WITH INIT;
   GO

   The general syntax of this script is:

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