Whenever a transaction is submitted for execution, the Database Engine is responsible either for executing the transaction completely and recording its changes permanently in the database or for guaranteeing that the transaction has no effect at all on the database. This approach ensures that the database is consistent in case of a failure, because failures do not damage the database itself, but instead affect transactions that are in progress at the time of the failure. The Database Engine supports both automatic and manual recovery, which are discussed next in turn.
1. Automatic Recovery
Automatic recovery is a fault-tolerant feature that the Database Engine executes every time it is restarted after a failure or shutdown. The automatic recovery process checks to see if the restoration of databases is necessary. If it is, each database is returned to its last consistent state using the transaction log.
During automatic recovery, the Database Engine examines the transaction log from the last checkpoint to the point at which the system failed or was shut down. (A checkpoint is the most recent point at which all data changes are written permanently to the database from memory. Therefore, a checkpoint ensures the physical consistency of the data.) The transaction log contains committed transactions (transactions that are successfully executed, but their changes have not yet been written to the database) and uncommitted transactions (transactions that are not successfully executed before a shutdown or failure occurred). The Database Engine rolls forward all committed transactions, thus making permanent changes to the database, and undoes the part of the uncommitted transactions that occurred before the checkpoint.
The Database Engine first performs the automatic recovery of the master database, followed by the recovery of all other system databases. Then, all user-defined databases are recovered.
2. Manual Recovery
A manual recovery of a database specifies the application of the full backup of your database and subsequent application of all transaction logs in the sequence of their creation. (Alternatively, you can use the full database backup together with the last differential backup of the database.) After this, the database is in the same (consistent) state as it was at the point when the transaction log was backed up for the last time.
When you recover a database using a full database backup, the Database Engine first re-creates all database files and places them in the corresponding physical locations. After that, the system re-creates all database objects.
The Database Engine can process certain forms of recovery dynamically (in other words, while an instance of the database system is running). Dynamic recovery improves the availability of the system, because only the data being restored is unavailable. Dynamic recovery allows you to restore either an entire database file or a filegroup. (Microsoft calls dynamic recovery “online restore”)
2.1. Is My Backup Set Ready for Recovery?
After finishing the backup process, the selected device (tape or disk) contains all data of the object you chose to back up. The stored data is called a backup set. Before you start a recovery process, you should be sure that
- The backup set contains the data you want to restore.
- The backup set is usable.
The Database Engine supports a set of Transact-SQL statements that allows you to confirm that the backup set is usable and contains the proper data. The following four statements, among others, belong to it:
- RESTORE LABELONLY
- RESTORE HEADERONLY
- RESTORE FILELISTONLY
- RESTORE VERIFYONLY
The following subsection describes these statements.
RESTORE LABELONLY This statement is used to display the header information of the media (disk or tape) used for a backup process. The output of the RESTORE LABELONLY statement is a single row that contains the summary of the header information (name of the media, description of the backup process, and date of a backup process).
NOTE RESTORE LABELONLY reads just the header file, so use this statement if you want to get a quick look at what your backup set contains.
RESTORE HEADERONLY Whereas the RESTORE LABELONLY statement gives you concise information about the header file of your backup device, the RESTORE HEADERONLY statement gives you information about backups that are stored on a backup device. This statement displays a one-line summary for each backup on a backup device. In contrast to RESTORE LABELONLY, using RESTORE HEADERONLY can be time consuming if the device contains several backups.
RESTORE FILELISTONLY The RESTORE FILELISTONLY statement returns a result set with a list of the database and log files contained in the backup set. You can display information about only one backup set at a time. For this reason, if the specified backup device contains several backups, you have to specify the position of the backup set to be processed.
You should use RESTORE FILELISTONLY if you don’t know exactly either which backup sets exist or where the files of a particular backup set are stored. In both cases, you can check all or part of the devices to make a global picture of existing backups.
RESTORE VERIFYONLY In contrast to the previous three statements, which display information about your backup set, the RESTORE VERIFYONLY statement verifies the backup without using it for the restore process. This statement checks the existence of all backup devices (tapes or files) and whether the existing information can be read.
RESTORE VERIFYONLY supports two specific options:
- LOADHISTORY Causes the backup information to be added to the backup history tables
- STATS Displays a message each time another percentage of the reading process completes, and is used to gauge progress (the default value is 10)
2.2. Restoring Databases and Logs Using Transact-SQL Statements
All restore operations can be executed using two Transact-SQL statements:
- RESTORE DATABASE
- RESTORE LOG
The RESTORE DATABASE statement is used to perform the restore process for a database. The general syntax of this statement is
RESTORE DATABASE {db_name | @variable}
[FROM device_list]
[WITH option_list]
db_name is the name of the database that will be restored. (The name of the database can be supplied using a variable, @variable.) device_list specifies one or more names of devices on which the database backup is stored. (If you do not specify the FROM clause, only the process of automatic recovery takes place, not the restore of a backup, and you must specify either the RECOVERY, NORECOVERY, or STANDBY option. This action can take place if you want to switch over to a standby server.) device_list can be a list of names of disk files or tapes. option_ list comprises several options that can be specified for the different backup forms. The most important options are
- RECOVERY/NORECOVERY/STANDBY
- CHECKSUM/NO_CHECKSUM
- REPLACE
- PARTIAL
- STOPAT/STOPATMARK/STOPBEFOREMARK
The RECOVERY option instructs the Database Engine to roll forward any committed transaction and to roll back any uncommitted transaction from the corresponding transaction log. After the RECOVERY option is applied, the database is in a consistent state and is ready for use. This option is the default.
NOTE Use the RECOVERY option either with the last transaction log to be restored or to restore with a full database backup without subsequent transaction log backups.
With the NORECOVERY option, the Database Engine does not roll back uncommitted transactions because you will be applying further backups. After the NORECOVERY option is applied, the database is unavailable for use.
NOTE Use the NORECOVERY option with all but the last transaction log to be restored.
The STANDBY option is an alternative to the RECOVERY and NORECOVERY options and is used with the standby server. (The standby server is discussed later, in the section “Using a Standby Server”) In order to access data stored on the standby server, you usually recover the database after a transaction log is restored. On the other hand, if you recover the database on the standby server, you cannot apply additional logs from the production server for the restore process. In that case, you use the STANDBY option to allow users read access to the standby server. Additionally, you allow the system to restore additional transaction logs. The STANDBY option implies the existence of the undo file that is used to roll back changes when additional logs are restored.
The CHECKSUM option initiates the verification of both the backup checksums and page checksums, if present. If checksums are absent, RESTORE proceeds without verification. The NO_CHECKSUM option explicitly disables the validation of checksums by the restore operation.
The REPLACE option replaces an existing database with data from a backup of a different database. In this case, the existing database is first destroyed, and the differences regarding the names of the files in the database and the database name are ignored. (If you do not use the REPLACE option, the database system performs a safety check that guarantees an existing database is not replaced if the names of files in the database, or the database name itself, differ from the corresponding names in the backup set.)
The PARTIAL option specifies a partial restore operation. With this option you can restore a portion of a database, consisting of its primary filegroup and one or more secondary filegroups, which are specified in an additional option called FILEGROUP. (The PARTIAL option is not allowed with the RESTORE LOG statement.)
The STOPAT option allows you to restore a database to the state it was in at the exact moment before a failure occurred by specifying a point in time. The Database Engine restores all committed transactions that were recorded in the transaction log before the specified point in time. If you want to restore a database by specifying a point in time, execute the RESTORE DATABASE statement using the NORECOVERY clause. After that, execute the RESTORE LOG statement to apply each transaction log backup, specifying the name of the database, the backup device from which the transaction log backup will be restored, and the STOPAT clause. (If the backup of a log does not contain the requested time, the database will not be recovered.)
The STOPATMARK and STOPBEFOREMARK options specify to recover to a mark. This topic is described a bit later, in the section “Recovering to a Mark.”
The RESTORE DATABASE statement is also used to restore a database from a differential backup. The syntax and the options for restoring a differential backup are the same as for restoring from a full database backup. During a restoration from a differential backup, the Database Engine restores only that part of the database that has changed since the last full database backup. Therefore, restore the full database backup before you restore a differential backup!
The RESTORE LOG statement is used to perform a restore process for a transaction log. This statement has the same syntax form and the same options as the RESTORE DATABASE statement.
2.3. Restoring Databases and Logs Using SQL Server Management Studio
To restore a database from a full database backup using Management Studio, expand the server, choose Databases, right-click the database, and choose Tasks | Restore | Database. The Restore Database dialog box appears (see Figure 16-3). On the General page, select a database to which you want to restore. Then check the backup set that you want to use for your backup process.
NOTE If you restore from the log backup, do not forget the sequence of restoring different types of backups. First restore the full database backup. Then restore all corresponding transaction logs in the sequence of their creation.
To select the appropriate restore options, choose the Options page of the Restore Database dialog box. In the upper part of the window you can choose one or more of the restore options, which are self-explanatory.
In the Recovery State box, you can choose between the following states:
- RESTORE WITH RECOVERY
- RESTORE WITH NORECOVERY
- RESTORE WITH STANDBY
Choosing the first option, RESTORE WITH RECOVERY (see Figure 16-4), instructs the Database Engine to roll forward any committed transaction and to roll back any uncommitted transaction. After applying this option, the database is in a consistent state and is ready for use. This option is equivalent to the RECOVERY option of the RESTORE DATABASE statement.
NOTE Use this option only with the last transaction log to be restored or with a full database restore when no subsequent transaction logs need to be applied.
If you choose the second option, RESTORE WITH NORECOVERY, the Database Engine does not roll back uncommitted transactions because you will be applying further backups. After you apply this option, the database is unavailable for use, and additional transaction logs should be restored. This option is equivalent to the NORECOVERY option of the RESTORE DATABASE statement.
NOTE Use this option with all but the last transaction log to be restored or with a differential database restore.
Choosing the third option, RESTORE WITH STANDBY, specifies the file that is subsequently used to roll back the recovery effects. This option is equivalent to the STANDBY option in the RESTORE DATABASE statement.
The process of a database restoration from a differential database backup is equivalent to the process of a restoration from a full database backup.
NOTE If you restore from a differential backup, first restore the full database backup before you restore the corresponding differential one. In contrast to transaction log backups, only the latest differential backup is applied, because it includes all changes since the full backup.
2.4. Recovering to a Mark
The Database Engine allows you to use the transaction log to recover to a specific mark. Log marks correspond to a specific transaction and are inserted only if the transaction commits. This allows the marks to be tied to a particular amount of work and provides the ability to recover to a point that includes or excludes this work.
NOTE If a marked transaction spans multiple databases on the same database server, the marks are recorded in the logs of all the affected databases.
The BEGIN TRANSACTION statement (see Chapter 13) supports the WITH MARK clause to insert marks into the logs. Because the name of the mark is the same as its transaction, a transaction name is required. (The description option specifies a textual description of the mark.)
The transaction log records the mark name, description, database, user, date and time information, and the log sequence number (LSN). To allow their reuse, the transaction names are not required to be unique. The date and time information is used along with the name to uniquely identify the mark.
You can use the RESTORE LOG statement (with either the STOPATMARK clause or the STOPBEFOREMARK clause) to specify recovering to a mark. The STOPATMARK clause causes the recovery process to roll forward to the mark and include the transaction that contains the mark. If you specify the STOPBEFOREMARK clause, the recovery process excludes the transaction that contains the mark.
Both clauses just described support AFTER datetime. If this option is omitted, recovery stops at the first mark with the specified name. If the option is specified, recovery stops at the first mark with the specified name exactly at or after datetime.
2.5. Restoring the master Database
The corruption of the master system database can be devastating for the whole system because it comprises all system tables that are necessary to work with the database system. The restore process for the master database is quite different from the same process for user-defined databases.
A damaged master database makes itself known through different failures. These failures include the following:
- Inability to start the MSSQLSERVER process
- An input/output error
- Execution of the DBCC command points to such a failure
Two different ways exist to restore the master database. The easier way, which is available only if you can start your database system, is to restore the master database from the full database backup. If you can’t start your system, then you must go the more difficult route and use the sqlservr utility. (The sqlservr utility is described in detail in Chapter 15.)
To restore your master database, start your instance in single-user mode. Of the two ways to do it, my favorite is to use the command window and execute the sqlservr utility (from the command prompt) with the option -m. Although the use of this utility is more difficult, this approach allows you to restore the master database in most cases. In the second step, you restore the master database together with all other databases using the last full database backup.
NOTE If there have been any changes to the master database since the last full database backup, you will need to re-create those changes manually.
2.6. Restoring Other System Databases
The restore process for all system databases other than master is similar. Therefore, I will explain this process using the msdb database. The msdb database needs to be restored from a backup when either the master database has been rebuilt or the msdb database itself has been damaged. If the msdb database is damaged, restore it using the existing backups. If there have been any changes after the msdb database backup was created, re-create those changes manually. (You can find the description of the msdb system database in Chapter 15.)
NOTE You cannot restore a database that is being accessed by users. Therefore, when restoring the msdb database, the SQL Server Agent service should be stopped. (SQL Server Agent accesses the msdb database.)
3. Recovery Models
A recovery model allows you to control to what extent you are ready to risk losing committed transactions if a database is damaged. It also determines the speed and size of your transaction log backups. Additionally, the choice of a recovery model has an impact on the size of the transaction log and therefore on the time period needed to back up the log. The Database Engine supports three recovery models:
- Full
- Bulk-logged
- Simple
The following sections describe these recovery models.
3.1. Full Recovery Model
During full recovery, all operations are written to the transaction log. Therefore, this model provides complete protection against media failure. This means that you can restore your database up to the last committed transaction that is stored in the log file. Additionally, you can recover data to any point in time (prior to the point of failure). To guarantee this, such operations as SELECT INTO and the execution of the bcp utility are fully logged too.
Besides point-in-time recovery, the full recovery model allows you also to recover to a log mark. Log marks correspond to a specific transaction and are inserted only if the transaction commits. The full recovery model also logs all operations concerning the CREATE INDEX statement, implying that the process of data recovery now includes the restoration of index creations. That way, the re-creation of the indices is faster, because you do not have to rebuild them separately.
The disadvantage of this recovery model is that the corresponding transaction log may be very voluminous and the files on the disk containing the log will be filled up very quickly. Also, for such a voluminous log, you will need significantly more time for backup.
NOTE If you use the full recovery model, the transaction log must be protected from media failure. For this reason, using RAID 1 to protect transaction logs is strongly recommended. (RAID 1 is explained in the section “Using RAID Technology” later in this chapter.)
3.2. Bulk-Logged Recovery Model
Bulk-logged recovery supports log backups by using minimal space in the transaction log for certain large-scale or bulk operations. The logging of the following operations is minimal and cannot be controlled on an operation-by-operation basis:
- SELECT INTO
- CREATE INDEX (including indexed views)
- bcp utility
Although bulk operations are not fully logged, you do not have to perform a full database backup after the completion of such an operation. During bulk-logged recovery, transaction log backups contain both the log and the results of a bulk operation. This simplifies the transition between full and bulk-logged recovery models.
The bulk-logged recovery model allows you to recover a database to the end of a transaction log backup (that is, up to the last committed transaction). Additionally, you can restore your database to any point in time if you haven’t performed any bulk operations. The same is true for the restore operation to a named log mark.
The advantage of the bulk-logged recovery model is that bulk operations are performed much faster than under the full recovery model, because they are not fully logged. On the other side, the Database Engine backs up all the modified extents, together with the log itself. Therefore, the log backup needs a lot more space than in the case of the full recovery. (The time to restore a log backup is significantly increased, too.)
3.3. Simple Recovery Model
In the simple recovery model, the transaction log is truncated whenever a checkpoint occurs. Therefore, you can recover a damaged database only by using the full database backup or the differential backup, because they do not require log backups. Backup strategy for this model is very simple: restore the database using existing database backups and, if differential backups exist, apply the most recent one.
NOTE The simple recovery model doesn’t mean that there is no logging at all. The log content won’t be used for backup purposes, but it is used at the checkpoint time, where all the transactions in the log are committed or rolled back.
The advantages of the simple recovery model are that the performance of all bulk operations is very high and requirements for the log space are very small. On the other hand, in the case of failure, this model requires the most manual work because all changes since the most recent database (or differential) backup must be redone. Point-in-time and page restore are not possible with this recovery model. Also, file restore is available only for read-only secondary filegroups.
NOTE Do not use the simple recovery model for production databases.
3.4. Changing and Editing a Recovery Model
You can change the recovery model by using the RECOVERY option of the ALTER DATABASE statement. The part of the syntax of the ALTER DATABASE statement concerning recovery models is
SET RECOVERY [FULL | BULK_LOGGED | SIMPLE]
If you want to edit the current recovery model, before changing it, you can use the following two alternatives:
- Using the databasepropertyex property function
- Using the sys.databases catalog view
If you want to display the current model of your database, use the recovery value for the second parameter of the databasepropertyex function. Example 16.2 shows the query that displays the recovery model for the sample database. (The function displays one of the values FULL, BULK_LOGGED, or SIMPLE.)
Example 16.2
SELECT databasepropertyex(‘sample’, ‘recovery’);
The recovery_model_desc column of the sys.databases catalog view displays the same information as the databasepropertyex function, as Example 16.3 shows.
Example 16.3
SELECT name, database_id, recovery_model_desc AS model
FROM sys.databases
WHERE name = ‘sample’
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.