Defining SQL Server backup and restore

The SQL Server backup and restore technique involves steps for creating backups, restoring databases, and saving a valid backup copy in a different location. It is a built in SQL Server feature, useful for disaster recovery plans.

The backup and restore process in SQL Server involves three main steps:

1. Backing up the desired SQL Server database
2. Transferring the backup files to another location, usually a remote location to avoid risk of a hard drive failure along with possible software problems (e.g. virus attacks)
3. Restoring the database on SQL Server to ensure that the database backup is valid and to verify integrity.

Restoring a database backup will ensure that in case of a disaster, the restore process will be successful with no errors or problems in general. The SQL Server backup and restore process requires constant testing of backed up files to provide reliable disaster recovery plan. If the backups are not being tested, they might turn out to be bad files that can’t be used for data recovery.

Implementation examples

One of the common backup and restore configurations is an environment with two SQL Servers (SQLServer-1 and SQLServer-2), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one database named SQLDB-1 which will be backed up, transferred, and restored to SQLServer-2.

The second common configuration is an environment with one SQL Server (SQLServer-1), two SQL Server instances (SQLServer-1 and SQLServer-2), and one database named SQLDB-1. The major disadvantage of this configuration is that if SQLServer-1 goes down or there is a hardware error, both database copies will be unavailable. This solution is acceptable if there is a need for testing the copy of the database SQLDB-1.

Operating modes

SQL Server has several backup options available, the most important are:

Database backup – A database backup that can be full or differential. A Full database backup contains all data in the specific database and enough transaction log data to allow recovery. A Differential database backup contains only the data that has been changed since the last full database backup. A differential database backup requires a prior full database backup.

The second important backup option is Transaction log backup. A transaction log backup contains all transaction log records that were not backed up. It’s available only in Full and Bulk logged recovery models.

Recovery models

SQL Server comes with three different recovery models. The SQL Server recovery model determinates how the transaction log is maintained.

The Simple recovery model is used generally for the test and development databases and also for data warehouses with mostly read-only data. The transaction log is self-maintained and there is little chance for the transaction log growing. Old and backed up transactions are marked for reuse and will be overwritten with new ones automatically. There is a possibility for data loss due to automatic deletion of old transactions. Changes after the most recent database backup are exposed. In the event of a disaster, all changes made to a database after the last backup, must be redone. There is no option for a point-in-time recovery; data can be recovered only to the end of a backup. Transaction log backups cannot be created.

The Bulk logged recovery model is used for production systems where there is a need for large bulk operations such as index creation or bulk import. It minimally logs most of bulk operations, therefore saves the amount of needed space in the transaction log file. There is a possibility for data loss if the transaction log is damaged or bulk logged operations occurred since the most recent transaction log backup. There is no option for point-in-time recovery; data can be recovered to the end of the backup. Transaction log backups are necessary for minimal data loss.

The Full recovery model is used for productions systems where no data loss is allowed. All transactions are being logged into the transaction log file. When a transaction log file backup is created, the backed up transactions will be marked for reuse. In the Full recovery model, there is the biggest chance of transaction log growth because all transactions are logged, and not removed until a transaction log backup is created. Because of that, taking backups on regular basis is highly recommended. There is no data loss exposure. There must be a full chain of transaction log backups present in order for data to be recovered. If the tail of the transaction log is damaged, the changes that occurred after the last transaction log backup will be lost. A point-in-time recovery is possible if all backups are available up to that point in time. Transaction log backups are necessary for minimal data loss. The transaction log backups must be created regularly to maintain the online transaction log size.

Advantages and disadvantages of using SQL Server backup and restore

Using SQL Server backup and restore has multiple benefits:

• simple implementation
• ability to be scripted and scheduled
• ability to save copies of the on multiple locations
• inexpensive
• maintained current copy of data.

Since SQL Server version 2008, backups can be natively compressed which results in less space and time needed for creating a backup.

The backup and restore process has some disadvantages: it’s not possible to automatically backup and restore a database on a different SQL Server instance and it’s necessary to have a database in the Full recovery model.

Setting up the database backup and restore process

A SQL Server database can be backed up and restored via the SQL Server Management Studio wizard or T-SQL.

T-SQL for backing up the whole ACMEDB database:

T-SQL for backing up the transaction log of the ACMEDB database:

T-SQL for restoring the ACMEDB database:

The SQL Server Management Studio wizard offers options through the Back Up dialog.

In the Back Up Database dialog, the user can choose the source and backup type, along with the name of the backup set.

The restore process can also be managed through the wizard. If the full recovery model is used, there is also an option for choosing a specific point in time to restore the data.