Devart Blog

Backup and Restore Databases in SQL Server

Posted by on February 17th, 2016

In this article, we will talk about the benefits of backing up SQL Server databases, different backup types, and backup strategies.

The immediate purpose of creating database backups is the ability to recover a damaged database and minimize the risk of catastrophic data loss. Thus, a reliable use of backup and restore for recovery requires a backup and restore strategy.

SQL Server Database Components

Before talking about database backup and restore, let’s start from exploring the files that SQL Server database contains. In the simplest case, a database consists of two files: a data file (*.mdf) and a log file (*.ldf). Both are created while executing the CREATE DATABASE statement.

Data files contain data-like tables, views, stored procedures, and triggers along with the system information that describes the database. Every database has strictly one primary data file and optionally may have several secondary data files (*.ndf). The primary data file is stored in the PRIMARY filegroup. A secondary data file may also be created in the PRIMARY filegroup. Alternatively, a user may define their own filegroup.

Complex databases consist of several data files that form the filegroup. Every filegroup contains multiple files. A filegroup is a logical group of one or several data files. When you execute the CREATE DATABSE statement, the PRIMARY filegroup is created by default.

A transaction log file contains a history of all the actions that have been done to your database.

Data files are the foundation for your backup and recovery strategy.

SQL Server Backup Types

There are three backup types:

Full database backup

The simplest type of backup is a full database backup. You can consider the full database backup as an archive of your database at the point of time when the backup was created. It includes a part of the transaction log, therefore the entire database can be recovered once a full database backup is restored.

The following code creates a full database backup by using the WITH FORMAT clause to overwrite any existing backups and create a new media set.

-- Back up the AdventureWorks database to new media set.
BACKUP DATABASE AdventureWorks
    TO DISK = 'D:\DatabaseBackups\AdventureWorks.bak' 
    WITH FORMAT;
GO

Differential database backup

The differential backup is very similar to the full backup. The only difference is that the differential backup file contains only the data that has been changed since the last full backup.

The following code creates a differential database backup.

-- Create a full database backup first.
BACKUP DATABASE AdwentureWorks 
   TO AdwentureWorks_1 
   WITH INIT;
GO
-- After some time.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE AdwentureWorks
   TO AdwentureWorks_1
   WITH DIFFERENTIAL;
GO

One of the main benefits of the differential backup is that creating a differential backup can be very fast as compared to creating a full database backup. It is also useful if a particular subset of a database is modified more frequently than the rest of the database.

File Backups

File backups allow you to backup each file individually instead of having to backup the entire database. You can indicate a whole filegroup instead of specifying each file individually. For a database that is broken into multiple filegroups, the file backup can decrease the time and disk space required for the backup strategy and make the recovery process faster. The main disadvantage of file backups is that maintaining a complete set of these backups can be a tedious task.

The following code creates a differential file backup of only the F1 file and the F2 file of the G1 filegroup.

--Backup the files in the G1 secondary filegroup.
BACKUP DATABASE AdwentureWorks
   FILE = 'F1', 
   FILE = 'F2'

-- For the filegroup:
-- FILEGROUP = 'SalesGroup1',
-- FILEGROUP = 'SalesGroup2'
 
   TO DISK = 'D:\ DatabaseBackups\G1.bck';
GO

Transaction Log Backups

Transaction log backup allows you to backup the active part of the transaction log. You must create at least one full backup before you can create a log backup. Then the transaction log can be backed up at any time unless the log has already been backed up. It is a good practice to make log backups frequently. It allows you to minimize work loss exposure and to truncate the transaction log file.
The following code creates a transaction log backup for the AdventureWorks database to the previously created backup, AdventureWorks_Log1.

BACKUP LOG AdventureWorks2012
   TO AdventureWorks_Log1;
GO

We have briefly reviewed the main types of database backups. However, there are other specific backup types that are not covered in this article: partial database backups, tail-log backups, copy-only backups, and mirror backups.

Recovery Models

SQL Server backup and restore operations occur within the context of the database recovery model. There are three basic recovery models available:

Simple

This recovery model does not support log backups as well as log shipping, media recovery without data loss, and point-in-time restores.

Full

This recovery model fully logs all operations. You will not lose your work in case you lose or damage a data file. You can recover to an arbitral point-in-time, for example, prior to a user error.

Bulk logged

This recovery model is very similar to FULL. It reduces log space usage by using minimal logging for most bulk operations.

Restoring Databases

The immediate goal of SQL Server backup strategy is to prepare ourselves to an emergency situation. However, there are some cases when you may benefit from using the SQL Server backup and restore feature. For example, you can restore a copy of a database to a development or test environment. Note that the restore options depend on the recovery model. The FULL recovery model has the most complete set of options.

In general, the backup and restore strategy consists of a backup stage and a restore stage. The backup part of the strategy defines the type and frequency of backups, how backups are to be tested, and where backups are to be stored. It is also important to care about database backup security to protect it from unauthorized access. The restore part of the strategy defines who is responsible for performing restores and how restores should be performed to meet the business goals.

The following code recovers the AdventureWorks database:

--recover the database:
RESTORE DATABASE AdventureWorks WITH RECOVERY;
GO

Backup tools

It is a good practice for DBA to use third-party tools to manage database backup and restore tasks. In the following part of the article, I would like to introduce to you the backup and restore functionality available in dbForge Studio for SQL Server. The built-in Backup and Restore wizard automates the backup and restore processes and allows you to perform it in few simple steps.

Starting Backup

To create a SQL Server backup, in the Database Explorer, right-click a required database, navigate to Tasks, and select Back Up… On the first page of the wizard, you can view or modify settings for a database backup operation, including:

  • Connection and SQL database for backup
  • Recovery model
  • The type of backup you want to perform for the selected database
  • Backup components

sql-backup-general

 

The wizard allows you to take a full control over the disk files to which a backup will be written. You can
overwrite media options, decide how to handle errors during the backup operation and what to do with the log file.

sql-backup-media

Every backup operation can be customized up to your needs. The Options page allows to:

  • Specify optional information about the backup set
  • Set up backup compression level
  • Encrypt a backup

sql-backup-options

When the backup is completed, the only thing you should do to restore an SQL Server database is to run dbForge Restore Wizard that will guide you through the restoration process. To start working with the SQL Server restore tool, in the Database menu, navigate to Tasks, and click Restore

sql-server-restore

Summary

Developing an effective backup and restore strategy requires detailed planning, implementation, and testing. Testing is an essential part. You do not have a good strategy until you have successfully restored backups.

Leave a Reply