Maintaining Database Integrity

Introduction

The Maintaining Database Integrity module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with DBCC commands
• Working with suspect pages table
• Restoring a database

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Three exam objectives are covered in this lab.

• Performing a full database consistency check
• Managing the suspect pages table
• Recovering a Database Without Restoring Data

Exercise 1 - Working with DBCC Commands

You can perform a full database consistency check using a set of management commands known as DBCC or Database Console Commands. There are various categories for DBCC commands such as informational, maintenance, miscellaneous, and validation. DBCC commands accept input parameters and also return values like functions. The following table describes the validation category DBCC commands.

In this exercise, you will learn to use some of the validation DBCC commands such as CHECKDB, CHECKALLOC, and CHECKTABLE.

Exercise 2 - Working with Suspect Pages Table

When the SQL Server database engine encounters a logical consistency error or error number 824 while reading any page, that particular page is considered as a suspect page.

The database engine can encounter suspect pages during the backup process or DBCC CHECKDB command execution.

The table that manages the suspect pages is called the suspect pages table. The suspect pages table records page ID of every suspect page. This table resides in msdb database. The suspect pages table helps to decide if restoration of lost data is necessary.

In this exercise, you will learn to query the suspect pages table, back up the AdventureworksDW2016CTP3 database, and work with single_user and multi_user database modes.

Exercise 3 - Restoring a Database

You can restore a complete database backup using SQL Server Management Studio or Transact-SQL.

In this exercise, you will drop the AdventureworksDW2016CTP3 database and then restore the dropped database.