Chapter 3: Implementing Backup and Recovery

One of the many responsibilities of an administrator is to keep data backed up in case of any data failure. SQL Server helps administrators fulfill this responsibility via sophisticated backup and restore features. In this chapter, we will learn in detail what you need when choosing an appropriate database disaster recovery strategy in conjunction with the database's usage and configuration, as well as how to use stored backups to restore a database in case of failure.

We will cover the following topics in this chapter:

• Data structures and transaction logging
• SQL Server recovery models
• Designing a backup strategy
• Using database and log restore

Let's get started!

Data structures and transaction logging

We usually think of a database as a physical structure consisting of tables containing columns and rows of data and indexes. However, this is just a human point of view. From SQL Server's perspective, a database is a set of precisely structured files described in the form of metadata, also saved in the same database structures within the database. We are starting this chapter with an explanation of storage internals because a conceptual imagination of how every database works is very helpful when the database needs to be backed up correctly.

How data is stored

Every database on SQL Server must have at least two files:

• The primary data file with the usual suffix, mdf
• The transaction log file with the usual suffix, ldf

For most databases, this minimal set of files is enough. However, when the database contains big amounts...

Accelerated Database Recovery

The described recovery process ensures that every database is in its last known consistent state after SQL Server's startup. The recovery process could take a long time in some cases. A common situation would be when SQL Server is stopped (sometimes unexpectedly) when some long-running transaction is being executed. The recovery process takes almost the same time as executing the transaction. It leads to unacceptable database unavailability. SQL Server 2019 brings a new database-scoped feature that bypasses this issue. The feature is called Accelerated Database Recovery (ADR). ADR basically keeps track of changes in data using internal row versioning. When SQL Server stops working and is restarted, SQL Server does not recover all the transactions from the transaction log, but simply recovers the proper versions of the records from the in-database row version store.

To turn on ADR, we can use the following Data Definition Language (DDL) statement...

SQL Server recovery models

Each database hosted by SQL Server contains a property called a recovery model. This property basically affects which backup strategy can be designed. This short section briefly explains recovery models.

How to configure a database's recovery model property

Every database has a property called the recovery model. The recovery model determines how transactions are logged, and for what timespan the transactions will be stored in the transaction log. The recovery model is set by the ALTER DATABASE TSQL command:

-- setting full recovery model ALTER DATABASE <database_name> SET RECOVERY FULL

The recovery model has three possible options:

• SIMPLE
• BULK_LOGGED
• FULL

We'll take a look at these now.

Using the SIMPLE recovery model

When the recovery model is set to SIMPLE, SQL Server clears transactions from the transaction log at every checkpoint. This approach leads to a...

Designing a backup strategy

A backup can be understood as a copy of used data pages or a copy of transaction log records. Backups should be done regularly. Backups are needed not only to restore databases in the case of physical or logical failure but for when, for example, we want to make a copy of the database or migrate the database to another SQL Server instance. To have our backups proper and complete, we need to consider the following points:

• How to configure the database
• Which types of backups to combine
• How to use backup strategies in conjunction with database usage

Let's first look at the types of backup supported by SQL Server.

Backup types

SQL server basically supports three main types of backup:

• Full backup
• Transaction log backup
• Differential backup

Each of these types has several variants and we will go through each of them.

Full backup

A full backup is simply a backup ...

Using database and log restore

The restore feature in SQL Server is used for data recovery in case of corruption and heavily relies on how data is backed up. In this section, we will cover the following topics:

• Preparation steps before the restore process starts
• Restore scenarios, depending on backup strategies

We'll start with the preparation steps first.

Preparing for restore

Before a database is restored, we must decide on the type of corruption and which backup sets are already available for restore. If we have more backups to be restored (a full backup combined with other backup types), we need to handle the recovery process as well.

The recovery process was described in the Transaction log section but let's recall the recovery process one more time. SQL Server uses write-ahead logging (WAL) for very detailed transaction actions. These transaction log records are written before the action is...

Summary

When working with databases, we always need to know how to recover them when damage occurs. This chapter was intended mostly as a syntactical guide for correct backup planning and performing for several types of databases, such as OLTP user databases, big databases, and system databases. A GUI alternative is also possible, but using syntax is a better approach when automating backup tasks when some corruption occurs. Syntax is never lost in dialogs of SQL Server Management Studio.

In this chapter, we learned about internal data handling. This knowledge is an advantage not only for backups and restores, but also for a better understanding when we cover optimizing databases.

We also learned about the backup capabilities of on-premises SQL Server instances. Through examples, we understood how to use backups to restore databases in many scenarios. We also learned the impact of backup procedures already being used on restore abilities that are measured by the RPO and RTO criteria...