Chapter 2. Manage backup and restore of databases

An organization’s data is one of its most important assets and in the twenty-first century data loss is not an option. In Chapter 1, “Configure data access and auditing,” we looked at the skills required to protect sensitive data through encryption, control data access and configure auditing for data governance reasons. We continue this data protection journey in this chapter where we learn how to help minimize data loss through the development and implementation of a Disaster Recovery Plan (DRP). Since a DRP is typically not sufficient to prevent data loss by itself, SQL Server supports a number of High-Availability (HA) technologies. We will look at the different High-Availability (HA) technologies in Chapter 4, “Manage high availability and disaster recovery.”

Data loss comes in many forms, including hardware failure, database corruption, malicious activity, and user error, and you need to develop a DRP to protect against all of these eventualities. It is common for organizations to have data governance requirements, so you will need to factor these into your data disaster strategy.

Skill 2.1 starts with the development of your backup strategy. We will examine the different requirements and considerations that you must take into account before you are ready to design your backup strategy. In Skill 2.2 we will turn to how you restore databases. Although your restore strategy is determined by your backup strategy, there are commonly different paths that you can take to restoring your database. In the exam you will be tested on your ability to assess what data has been lost as a result of a disaster and the appropriate restore path that you should take to make the database available again in the least possible time. Finally, in Skill 2.3, we deal with how you manage database integrity and recover from database corruption. Although database corruption is rare, you need to be prepared with an action plan just in case.

Skills in this chapter

Develop a backup strategy

Restore databases

Manage database integrity

In Skill 2.1 we cover how you design your backup strategy by engaging your organization to determine a particular database’s data loss and recovery requirements. We will then look at the backup operations that can be used to translate the backup strategy into a set of scheduled backup operations. To minimize data loss you need to understand the internals of the backup operation and how a database’s transaction log operates, which we cover next. Finally, we will look at how to operationalize your backup operations and related tasks.

A backup strategy is typically made up of the following elements:

A set of backup operations.

There will potentially be different types of backup operations.

A schedule or frequency of when the backup operations will be performed.

Although it sounds counter-intuitive, a backup strategy is really determined by your restore strategy or specifically how long it will take to restore your database when a disaster occurs. That is why it is critical to engage your business to determine what the recovery objectives for the database are. Without these recovery objectives it is impossible for you to design a backup strategy that will meet your organization’s business requirements. Make sure you document your backup (and restore) strategies so that both yourself, and other database administrators, can recover the database in a timely manner.

Recovery Time Objective (RTO) The RTO defines the maximum allowable downtime following a disaster incident. The goal for the database administrator is to restore normal business operations within this time frame.

Recovery Point Objective (RPO) The RPO defines the maximum acceptable amount of data loss following a disaster incident. The RPO is commonly expressed in minutes.

Recovery Level Objective (RLO) The RLO defines granularity of the data that needs to be restored following a disaster incident. For SQL Server it could be a SQL Server instance, group of databases, a database, a set of tables, or a table.

Although there is an implied relationship between the RPO and RTO, the RPO is not always less than the RTO, as most people intuitively think. For example, one company might have a RTO of one hour, but a RPO of 24 hours. In other words, they need to restore normal business processes as soon as possible. They are willing to lose a day’s worth of data. Resumption of normal operations is paramount. Potentially, the lost data can be added after the resumption of normal operations through paper records, or other data sources.

All stakeholders need to understand that zero data loss is very difficult to achieve, if not impossible for most organizations. A backup strategy is not sufficient by itself. Typically, some sort of high availability technology also needs to be used. It is important to stress to management and other relevant stakeholders that your backup strategy is not a substitute for high availability. We will cover high availability in Chapter 4, “Manage high availability and disaster recovery.”

The RTO, RPO, and RLO form part of your service level agreements (SLAs). The SLAs represent an agreement between the business and IT operations on how your IT infrastructure is managed and what services are delivered.

Remember that the restore operation is typically asymmetric to the backup operation. For example, a backup that takes four hours to complete might take twenty hours to restore. As an alternative, a four hour backup might take two hours to restore. That is why it is you should periodically test your restore procedure. Yes, you need to ensure that it works. But more importantly you need to gather various metrics, such as how long it takes.

That is why the restore process drives your backup strategy. Once you have your RTO, RPO, and RLO defined, you can determine what backup operations and their frequency will make up your backup strategy.

Full A full backup captures everything in the database.

Differential A differential backup captures everything in the database since the last full backup.

Incremental An incremental backup captures everything since the last incremental backup. In SQL Server it is called a log backup. You need to perform a full backup before you can perform log backups.

To design a backup strategy, you need to take into account a number of factors including:

The size of your database A larger database will take longer to backup. Your database might grow to a size where the backup operation can no longer be completed within an appropriate maintenance window. At that stage you might have to use different hardware or redesign your database and backup strategy.

The structure of your database files A database that consists of a single primary data file will be difficult to back up within an appropriate maintenance window as it gets larger. You have no choice but to back up the database in its entirety. As an alternative, if the database consists of multiple secondary data files these files can be backed up individually at different frequencies.

The speed/throughout of the network and storage subsystems involved The underlying hardware and networking infrastructure can substantially impact the duration your backup and restore operations. This will in turn impact your RTO.

How heavily utilized is the processor subsystem If your SQL Server instance is using all of the processor subsystem you might not be able to take advantage of backup compression. Backup compression consumes additional processor resources.

The volume of data modifications in the database A database that is heavily modified might have substantial transaction log growth. This in turn might force you to perform log backups more frequently so that the transaction log does not grow out of control.

The size of the data modifications in the database Similar to the above point, large transactions might also result in the transaction log growing to an unacceptable size. Again, you might have to perform more frequent log backups to remedy this scenario.

The type of data modifications in the database, for example, if they are predominantly update, or insert operations This will impact the size of your differential and log backups. If the same records within a database are continually updated, your differential backups will not increase in size and represent a good candidate for your backup strategy. As an alternative, if all the records within a database get modified, the differential backup might be the same size as a full database backup. Understanding the types of data modifications will help you optimize your backup strategy.

How compressible the data in the database is Backup compression consumes additional processor resources. If the data within your database is not very compressible there will be no point in using backup compression in your backup operations.

Whether point-in-time recovery is required If your organization requires a database to be recovered to a specific point-in-time you will have no choice but to implement log backups. Furthermore, the database will have to use the full recovery model.

The recovery objectives defined by your organization Quantifying your RPO, RTO, and RLO are critical to your backup strategy.

How the transaction log is managed Some databases experience substantial transaction log growth. You might also have limited storage dedicated to the transaction log. As a result, you might have to take more frequent log backups than planned to manage the transaction log.

The database’s recovery model Different recovery models impact whether transaction log backups can be taken. If your database is using the simple recovery model, you will not be able to leverage log backups.

The importance of the data within the database Some databases might not be important to your organization, they may be used in a staging or development environment, or they can be a replica of a production system. In such cases there might be no business requirement to back up the database at all.

In the exam you might be given a scenario where you will have to determine the RTO and the RPO. Watch out for statements in the questions that stipulate that only “fifteen minutes’ worth of data can be lost,” or that “the database needs to be recovered in two hours.” Pay attention to the factors discussed above, and in particular the sizes of the databases and the implied duration of any backup operations, because this will help you in the formulation of the backup strategy.

The first scenario is for a data warehouse solution where a read-only database is used for analytical and reporting purposes. At the end of each day a process changes the database to read/write mode, populates the database with that day’s transactions, and then changes the database back to read-only mode. This process starts at midnight and takes 1-2 hours to complete. The database uses the simple recovery model. Users query the database between 08:00 and 18:00. Management has stipulated a RTO of 4 hours and a RPO of 0 minutes.

For this data warehouse solution a full database backup at 03:00 every day can be used as the backup strategy. Our full backup completes within 2 hours. There is no need for differential backups.

The second scenario is for a transactional database used by a customer relations management (CRM) solution. Users are continually modifying the database during business hours (08:00 and 18:00) by adding new customer, activity, and opportunity records. Management requires a RTO of 4 hours and a RPO of 2 hours. The database will also potentially need to be recovered to any specific point in time. A number of re-indexing jobs start at midnight and take 2 hours to complete. You notice that after 75 minutes, the transaction log fills up 95% of the storage allocated to it.

For this CRM solution the following strategy can be used. Although management requires a RPO of 2 hours, the transaction log will consume all of its allocated storage within that time frame. Differential backups cannot be used, because they do not support point-in-time recovery.

Full database backup at 03:00 every day

Incremental backups every hour during business hours from 08:00 and 18:00

The final scenario is for a large, mission-critical, 24x7 database used in a manufacturing context. The existing tables are continually being updated by a variety of sensors in the manufacturing process. The transaction log grows rapidly in size. Although new records are also added to the database, it does not grow substantially in size. A full database backup cannot be performed daily since there is insufficient storage. The database needs to be restored to any point-in-time up to 3 months back. Management has indicated a RTO of 5 minutes.

For this manufacturing database the following backup strategy can be used. Differential backups are used to minimize the amount of storage consumed by backups. Transaction log backups allow you to recover to a point-in-time and only lose 5 minutes’ worth of data.

Full database backup at 03:00 every Monday

Incremental backups every five minutes

A differential backup at the end of each day at 23:00

In this section we will cover the different types of database backup operations supported by SQL Server. We will also have a look at various techniques that can be used to back up larger databases where you might not have an appropriate maintenance window.

SQL Server supports the following types of backup operations:

Full This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.

Differential This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.

Log This contains all of the log records that were performed since the last log backup.

File This contains either a file or filegroup that makes up the database.

Partial This is similar to a full backup, but it excludes all read-only filegroups by default. For a read-write database, a partial backup contains the primary filegroup and all read-write filegroups. For a read-only database, a partial backup contains only the primary filegroup.

Tail-Log This contains all the transaction log records that have not been backed up since the last log backup. A tail-log backup operation is typically performed as the first task in a disaster recovery process. This ensures that no data is lost up to the point-in-time when the disaster incident occurred. For the tail-log backup to be successful the database’s transaction log file must be available.

The exam will most likely cover only full, differential, and log backups. However, you need to be prepared for some of the less commonly used backup operations that are designed more for edge and border cases. Make sure you understand the use cases of where file and partial backups are used.

When you perform a backup operation you need to specify a backup destination. Although tape devices are supported they are rarely used in the industry today due to their speed. Most organizations back up directly to a disk based destination and then potentially backup these database backups to tape.

Unfortunately, the legacy of using tapes as a destination still exists in the backup operation. Consequently, for the exam you will need to understand the following concepts used by SQL Server backup operations:

Backup device A disk or tape device to which the database engine performs a backup operation.

Media family A backup created on a single non-mirrored device, or a set of mirrored devices in a media set.

Backup set This represents a successful backup operation’s content.

Media set A set of backup media that contains one or more backup sets.

Log sequence number (LSN) An internal numbering sequence used for each operation within the transaction log. This is used internally by the database engine and typically not used by database administrators.

Sequence number This indicates the order of the physical media within a media family. Also, media families are numbered sequentially according to their position within the media set.

Every backup device starts with a media header that is created by the first backup operation and remains until the media is reformatted. The media header contains information about the backup device’s contents and the media family it belongs to. The information includes:

The name of the media

The unique identification number of the media set

The number of media families in the media set

The sequence number of the media family containing this media

The unique identification number for the media family

The sequence number of this media in the media family

Whether the media description contains an MTF media label or a media description

The Microsoft Tape Format media label or the media description

The name of the backup software that wrote the label

The unique vendor identification number of the software vendor that formatted the media

The date and time the label was written

The number of mirrors in the set (1-4); 1 indicates an un-mirrored device

Consider the following series of backup operations that perform a full, differential, and incremental backup to four backup devices concurrently as shown in Listing 2-1.

LISTING 2-1 Multiple backups to four backup devices

Click here to view code image

-- Initial full backup
BACKUP DATABASE AdventureWorks  TO
DISK = 'R:SQLBackupAdventureWorks_BackupDevice1.bak''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice2.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 3.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 4.bak '
WITH
FORMAT'
MEDIANAME = ' AdventureWorksMediaSet1';
GO
-- Differential backup
BACKUP DATABASE AdventureWorks TO
DISK = 'R:SQLBackupAdventureWorks_BackupDevice1.bak''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 2.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 3.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 4.bak '
WITH
NOINIT'
MEDIANAME = 'AdventureWorksMediaSet1''
DIFFERENTIAL;
GO
-- Incremental backup
BACKUP LOG AdventureWorks TO
DISK = 'R:SQLBackupAdventureWorks_BackupDevice1.bak''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 2.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 3.bak ''
DISK = 'R:SQLBackupAdventureWorks_BackupDevice 4.bak '
WITH
NOINIT'
MEDIANAME = 'AdventureWorksMediaSet1';
GO

These three backup operations result in the following media set, as shown in Figure 2-1.

Now that we have covered the different types of backup operations and destinations let’s look at these operations in more detail. Understanding how these backup operations work will help you design an appropriate backup strategy.

A full backup contains a copy of the database and the transaction log operations performed during the database backup phase. The backup operation only backs up the allocated pages within the database. Unallocated pages are not backed up. For a 100GB database that only has 10GB of data, the full database backup is only approximately 10GB in size uncompressed.

With a full database backup, the database engine performs the following high-level actions:

Checkpoint Performs a database checkpoint. The checkpoint process flushes all dirty data from the buffer pool to disk to minimize the amount of work required by the restore process.

Record backup start LSN Examines the transaction log and records the log sequence number (LSN) of when the backup operation started.

Backup data Backs up all the extents (unit of eight physically contiguous 8KB pages) from the data files in the database to the backup destination.

Record backup end LSN Re-examines the transaction log and records the LSN of the start of the oldest active transaction and re-examines the transaction log and records the LSN of when the backup operation started.

Calculate minimum LSN Determines the minimum LSN required for the log backup by taking the earliest in time between the backup end LSN and the oldest active transaction’s LSN. The oldest active transaction could have started before the backup statement was executed.

Backup log Backs up the transaction log between the calculated minimum LSN and the backup end LSN. This ensures that the recovered database will be consistent as of the time of the backup operation’s completion.

Consider the following sequence of events, as shown in Figure 2-2.

1. Transaction A begins

2. Transaction A changes page 100

3. A full backup begins

4. Backup checkpoint occurs

5. Backup reads page 100

6. Backup reads page 200

7. Backup reads page 300

8. Transaction B begins

9. Transaction B changes page 200

10. Transaction C begins

11. Transaction A modifies page 300

12. Transaction B commits

13. Transaction A commits

14. Transaction C modifies page 200

15. Backup database read ends

In this example, the Backup Data phase captures the modification to page 100 by transaction A, but not the subsequent modification to pages 200 and 300 made by transactions A, B, and C. The Backup Data phase neither re-reads pages that have been backed up but then modified, nor does it block transactions by reading the page before allowing the modification. The Backup Log phase has to read enough of the transaction log so as to be able to recover the database to a consistent state as of the end of the Backup Data phase. Consequently, the Backup Log phase would have to capture the modifications made to pages 200 and 300. If you were to restore this database backup it would not include the changes made to page 200 by transaction C, because it did not commit before the Backup Data phase completed. This modification would be undone (rolled back) by the recovery phase of the restore operation.

Figure 2-3 shows the internal operations that were performed during a full database against a timeline. You can see how the database engine initially allocates a number of resources to the backup operation. You can control some of these resources such as the maximum transfer size and buffer count. Note how the full backup operation clears the differential bitmaps before performing the initial backup discussed above. It then scans the allocation bitmaps so as to predict the size of the backup file. This estimation is used to create the backup destination file before it can start writing to it. At the very end, after the data copy phase it backups up the transaction log and closes off the backup set. The resultant backup set file can be shorter than the initial estimation.

Use the following statement to perform a full backup:

Click here to view code image

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ '...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH <general_WITH_options> [ '...n ] ] ]
[;]

Although a single row in a table might be modified, the differential backup will back up the entire 64KB extent. This is done for performance reasons, but results in backup sizes being larger than they strictly need to be. Figure 2-4 shows how a differential backup backs up Database Extents via the DCM.

Typically, differential backups get larger in size as more time passes between subsequent differential backup operations and the baseline full backup. At some point in time this size increase impacts your RTO. Don’t forget that it takes longer to restore the full and differential backups when compared to restoring a standalone full backup.

Perform differential database backups when any of the following apply:

You want to reduce the time taken by the backup operation as compared to the full backup and you do not need point-in-time recovery.

You want to reduce the size of the database set as compared to the full backup and you do not need point-in-time recovery.

Use the following statement to perform a differential backup:

Click here to view code image

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ '...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
WITH DIFFERENTIAL
  [ WITH  <general_WITH_options> [ '...n ] ] ]
[;]

The size, or potential size of the differential backup will be the determining factor in any potential exam question. So pay attention to how records are being modified in any exam questions. A lot of insert operations within the database imply that the differential backups will get larger in size as time progresses. A lot of update operations to existing data implies that a “steady state” will be achieved with respect to the size of the differential backups as time elapses. You will need to compare both the size and recovery impact of differential backups to transaction log backups, and we will look at them next.

Log backups are the primary mechanism used to manage the transaction log so that it does not grow too large. For busy databases, it is common to back up the transaction log more frequently. The log backup frequency directly impacts your RPO. Backing up the log every 15 minutes means that you lose a maximum of 15 minutes of committed transaction in the worst case scenario, where you cannot perform a tail-log backup. Tail-log backups are discussed in more detail later in the “Perform tail-log backups” section of this chapter.

Perform transaction log backups when any of the following apply.

Your database has smaller RPO than the frequency of your full/differential backups.

You need to recover the database up until the time that it crashed.

You need point-in-time recovery.

You need to ensure that the database’s transaction log does not grow too large.

Use the following statement to perform a log backup:

Click here to view code image

BACKUP LOG { database_name | @database_name_var }
  TO <backup_device> [ '...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { <general_WITH_options> | <log-specific_optionspec> } [ '...n ] ]
[;]

For most databases a combination of full, differential and log backups are sufficient. However, for more “exotic” use cases you can leverage file backups that do not backup the entire database. Such use cases make great exam questions although they are not commonly “seen in the wild.” Let’s have a look at how you can perform file and partial backups of your databases next. Remember that they typically all require log backups so as to synchronize the database to a consistent state.

Perform file backups in the following scenarios:

You have a very large database and plan to rotate the backups of the files that make up the database.

Your database is made up of different files, some of which might contain read-only data, or data that is rarely modified. In this case you can back up the different files at different frequencies.

Use the following statement to perform a file/filegroup backup:

Click here to view code image

BACKUP DATABASE { database_name | @database_name_var }
  <file_or_filegroup> [ '...n ]
  TO <backup_device> [ '...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ '...n ] } ]
[;]

File backups have been supported in SQL Server for a very long time and were probably designed for large databases. We will examine how you can backup very large databases in the”Backup VLDBs” section later in this chapter.

A typical scenario is a very large database that has a read-only filegroup used to store archive data. That archive data is substantial in size and modified only annually. As a result there is no need to back up the read-only filegroup at the same frequency as the full backups.

Use the following statement to perform a partial backup:

Click here to view code image

BACKUP DATABASE { database_name | @database_name_var }
  READ_WRITE_FILEGROUPS   [ ' <read_only_filegroup> [ '...n ] ]
  TO <backup_device> [ '...n ]
  [ <MIRROR TO clause> ] [ next-mirror-to ]
  [ WITH { DIFFERENTIAL | <general_WITH_options> [ '...n ] } ]
[;]

The BACKUP statement has a large number of WITH options. For the exam, make sure you understand what the following options do and when to use them:

CHECKSUM | NO_CHECKSUM These options help ensure that your backup can be successfully restored without errors. The CHECKSUM option specifies that the backup operation verifies that each page of the database does not have a checksum or torn page error, if those database options are enabled and available. It also generates a checksum for the entire backup. The default behavior is NO_CHECKSUM, which does not validate pages and does not generate a backup checksum. Page validation and checksum generation consumes more processor resources during the backup operation. But their value is important in disaster recovery scenarios.

CONTINUE_AFTER_ERROR | STOP_ON_ERROR These options tell the database engine what to do in the case of a backup operation encountering a page checksum error. The CONTINUE_AFTER_ERROR option tells the backup operation to continue on if it encounters any page validation errors. The default option is STOP_ON_ERROR, which instructs the backup operation to stop.

COPY_ONLY This specifies that the backup is a copy only backup, which means your backup sequence is not affected. For all intents and purposes the backup operation did not occur. For a full backup, a copy only option does not reset the DMC, so subsequent differential backups are not affected. For a log backup the copy only option does not truncate the transaction log, so the log chain is not broken. Copy only backups are typically used to refresh non-production environments with production backups when the existing backup sequence should not be impacted. A copy only backup is still recorded in the ERRORLOG.

COMPRESSION | NO_COMPRESSION Backup compression has been available since the SQL Server 2008 Enterprise Edition and the SQL Server 2008 R2 Standard Edition. The COMPRESSION option enables backup compression. The NO_COMPRESSION option explicitly disables backup compression. The compression level cannot be controlled. Backup compression can consume extra processor resources so be careful with its usage in high-performance environments. The default behavior of the database engine is not to use backup compression. Setting the backup compression default server configuration option can change this default.

DESCRIPTION This option allows you to describe the backup set using up to 255 characters.

ENCRYPTION | NO_ENCRYPTION These options controls whether the backup should be encrypted. The default behavior is the same as the NO_ENCRYPTION option, which is not to encrypt. When you encrypt a backup you need to specify which encryption algorithm to use. The following encryption algorithms are supported:

AES_128

AES_192

AES_256

TRIPLE_DES_3KEY

When you encrypt a backup you also have to specify the encryptor using one of the following options:

SERVER CERTIFICATE = Encryptor_Name

SERVER ASYMMETRIC KEY = Encryptor_Name

EXPIREDATE | RETAIN_DAYS These two options allow you to control when the backup set expires and can be subsequently overwritten. RETAINSDAYS takes precedence over EXPIREDATE.

FORMAT | NO FORMAT The FORMAT option is destructive as it causes a new media set to be created. All existing backup sets are unrecoverable. If you format a single tape that belongs to an existing striped media set, the entire media set is useless. The default option, NOFORMAT, preserves the existing media header and backup sets on the media volumes used by the backup.

INIT | NOINIT The INIT option specifies that all backup sets should be overwritten. The media header is preserved. The existing backup sets are not overwritten if the EXPIRYDATE/RETAINDAYSA have not expired, or if the backup set name provided does not match the one in the backup media. The NOINIT option, which is the default, specifies that the backup set be appended to the existing ones in the media set.

NAME This option gives the backup set a name. Up to 128 characters can be used.

MEDIADESCRIPTION This option allows you to describe the media set. It is limited to 255 characters.

MEDIANAME This allows you to use up to 128 characters to give the media set a name.

RETAINDAYS This option allows you to control when the backup set expires and can be subsequently overwritten.

SKIP | NOSKIP The SKIP option specifies that the backup operation should ignore the “safety check” that normally checks the backup set’s expiration date or name before overwriting it.

STATS This controls at what percentage intervals the database engine should display a message indicating the progress of the backup operation. The default value is 10, which means you are notified whenever another 10 percent of the backup operation completes.

Furthermore, the BACKUP statement supports the ability to back up the same data/log to a number of mirrored backup devices via the MIRROR TO clause. The MIRROR TO clause must have the same type and number of the backup devices as the TO clause. A maximum of three MIRROR TO clauses can be used, so a total of four mirrors is possible per media set. The primary reason for using mirrored media sets is to provide redundancy at the backup device level.

Listing 2-2 shows a full backup operation that has been mirrored to a number of different servers.

LISTING 2-2 Mirrored backups

Click here to view code image

BACKUP DATABASE WorldWideImporters
TO DISK = 'B:SQLBackupWorldWideImporters.bak'
MIRROR TO DISK = '\DEVSERVERSQLBackupSQLBackupWorldWideImporters.bak''
MIRROR TO DISK = '\TESTSERVERSQLBackupSQLBackupWorldWideImporters.bak''
MIRROR TO DISK = '\STAGINGSERVERSQLBackupSQLBackupWorldWideImporters.bak''
WITH FORMAT;

When backing up a large database it can be useful to know how far an executing backup operation has come and how long it will take to complete. You can query the [sys].[dm_exec_requests] dynamic management view (DMV) to monitor a backup operation’s progress. Use the query in Listing 2-3 to show the progress of a running BACKUP operation:

LISTING 2-3 Progress of backup operation

Click here to view code image

SELECT session_id' db_name(database_id) as database_name'
            start_time' command' percent_complete' estimated_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE 'backup %';

Database snapshots make great questions in the exam. If you don’t know how database snapshots work at the database engine level, you might easily choose them as an answer when they are clearly not the correct answer choice. Database snapshots work at the page level. Whenever a page is modified its pre-modified version is written to the snapshot file. Consequently, the database snapshot will consume more disk space as more data is modified within the database. A database can have multiple database snapshots. Although the predominant use case for database snapshots is for reporting purposes, they can be used for safeguarding against user mistakes. Reverting a database snapshot is quicker in most cases than restoring a database and replaying all log backups, up until the user mistake is made. For example, you might want to take a database snapshot before you execute some sort of end-of-day batch process. If an error occurs, or you decide you need to roll back this batch process, you can simply revert the database back to the time of the database snapshot. All data modifications after the database snapshot are to be expunged.

Listing 2-4 shows how to create a database snapshot. Ensure you provide a unique, meaningful name to the database snapshot.

LISTING 2-4 Create a database snapshot

Click here to view code image

CREATE DATABASE WorldWideImporters_20160917
ON (
    NAME = WorldWideImporters_Data'
    FILENAME = 'R:SQLDataWorldWideImporters_20160917.ss')
AS SNAPSHOT OF [WorldWideImporters];

Backup to URL

Backup to Microsoft Azure tool

Managed backup to Microsoft Azure

SQL Server 2012 Service Pack 1 Cumulative Update 2 introduced the capability to back up directly to (and restore from) Microsoft Azure Blob Storage. Both the Backup Task in SQL Server Management Studio and the Maintenance Plan Wizard support backups to Microsoft Azure Blob Storage.

Backing up databases to Microsoft Azure Blob Storage is great if your databases are hosted in Microsoft Azure. For on-premise database solutions, however, you need to take into account your database size, volume of modifications, and upload/download bandwidth.

To back up to Microsoft Azure Blob Storage with the BACKUP statement, use the TO URL clause as show in Listing 2-5.

LISTING 2-5 Backup to URL

Click here to view code image

-- Create storage account identity and access key
CREATE CREDENTIAL MyCredentialName
WITH IDENTITY = 'MyStorageAccountName',
SECRET = '<MyStorageAccountAccessKey>';
GO
-- Backup database to URL using storage account identity and access key
BACKUP DATABASE MyDB
TO URL = 'https://<MyStorageAccountName>.blob.core.windows.net/<MyStorageAccountContainerN
ame>/MyDB.bak'
WITH CREDENTIAL = 'MyCredentialName'

Introduced in SQL Server 2014, the SQL Server Managed Backup to Microsoft Azure feature automatically manages your backups to Microsoft Azure. This feature can be enabled at the instance or database level, through the [smart_admin].[sp_set_instance_backup] or [smart_admin].[sp_set_db_backup] system stored procedures respectively. The database engine then automatically performs full and log backups automatically. Backups are retained for up to 30 days.

Full backups are performed whenever any of the following are true:

The last full backup is over a week old.

The log has grown more than 1GB since the last full backup.

Log chain is broken.

Log backups are performed whenever any of the following are true:

More than two hours have expired since the last log backup.

The log has grown in 5MB.

The log backup is behind the full backup.

Listing 2-6 shows an example of how to configure SQL Server Managed Backup to Microsoft Azure at the instance level.

LISTING 2-6 SQL Server Managed Backup to Microsoft Azure at the instance level

Click here to view code image

USE [msdb];
GO
EXEC [smart_admin].[sp_set_instance_backup]
    @enable_backup=1
    ,@storage_url = 'https://mystorageaccount.blob.core.windows.net/'
    ,@retention_days=30
    ,@credential_name='MyCredential'
    ,@encryption_algorithm ='AES_256'
    ,@encryptor_type= 'ServerCertificate'
    ,@encryptor_name='MyBackupCertificate';
GO

Listing 2-7 shows an example of how to configure SQL Server Managed Backup to Microsoft Azure at the database level.

LISTING 2-7 SQL Server Managed Backup to Microsoft Azure at the instance level

Click here to view code image

USE [msdb];
GO
EXEC [smart_admin].[sp_set_db_backup]
    @database_name='MyDB'
    ,@enable_backup=1
    ,@storage_url = 'https://MyStorageAccount.blob.core.windows.net/'
    ,@retention_days=30
    ,@credential_name='MyCredential'
    ,@encryption_algorithm ='NO_ENCRYPTION';
GO

The Microsoft SQL Server Backup to Microsoft Azure Tool is an externally available tool that enables backup to Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud. It works with all versions and editions of SQL Server, even if they do not support compression and encryption.

Potentially implement data compression on tables within the database to reduce the size of the database. In most cases PAGE compression is superior to ROW compression.

Assess taking advantage of columnstore indexes, which may substantially reduce the size of the tables. With columnstore indexes the table size can be potentially substantially reduced, as an example, from 120GB to 4GB. Be aware that columnstore indexes are designed primarily for data warehouse workloads where queries scan large volumes of data. For an On-Line Transaction Processing (OLTP) database you might still be able to use columnstore indexes on large tables that are rarely, if ever, queried. Examples of tables that fit this profile include auditing and logging tables.

Consider moving old data out of your very large database into an archive database. Old data is infrequently queried and should not live in your OLTP database forever.

Take advantage of backup compression to reduce the size of the backup set.

Back up your database to multiple backup devices. The database engine is able to consume up to 64 threads to concurrently back up the extents of your database to 64 backup devices, one thread per device. You also need to maximize the I/O throughput at the database’s storage subsystem level.

Most of these techniques focus on reducing the size of your database, which in turn reduces the duration of your backup and restore operations. At some point in time these techniques are not sufficient because your database evolves into what is sometimes referred to as a very large database or VLDB.

A VLDB can be defined as a database whose restore or backup time SLAs cannot be easily met through faster networking/storage hardware resources, or by using any of the techniques discussed above. Additional consideration needs to be given to designing a backup strategy for such VLDBs.

For example, a 4TB database can easily take longer than one day to restore, due to the backup size, storage subsystem speed, networking infrastructure, backup compression, and the number and size of transaction log backups that also need to be to restored. (This is why you should periodically test the time taken by your restore procedure to ensure that your RTO can be met.)

Although VLDBs are not really that common in the field, expect the exam to have some questions about how to best backup and restore a VLDB. You will most likely be either asked to design a backup strategy for a VLDB or provide the series of restore steps that need to be performed to recover a VLDB in the shortest period of time.

The most common and easiest technique to reduce your backup (and restore) times for VLDBs is to take advantage of filegroups and re-architect your VLDB from a single primary file into multiple data files. Each secondary data file, or set of data files, would be contained in its own filegroup. Each file should ideally be located on a separate disk. That way if any single disk fails, the damage is contained to that file/filegroup. To recover from the disaster, restore just the damaged file/filegroup, as opposed to the entire database. This technique can substantially reduce the time it takes to recover from a disaster, depending on what has failed. It should also reduce the backup duration because you do not backup the same volume of data.

One commonly used technique is to simply spread your VLDB across a number of files. You would then back up only one of the files in your VLDB nightly, and rotate between the files over subsequent nights.

Let’s consider the following scenario of 4TB VLDB that has six separate data files as shown in Listing 2-8. The VLDB has a filegroup called [DATA] that consists of four 1TB secondary data files. This [DATA] filegroup is configured as the default filegroup. This helps ensure that there’s no data in the [PRIMARY] file group. Notice how these six data files are located on separate drives to help ensure that we do not lose two 1 TB data files at once. (Let’s assume that there is no need to perform file backups on the weekend because this organization only operates during weekdays.)

LISTING 2-8 Creating a VLDB

Click here to view code image

CREATE DATABASE [VLDB]
  ON [PRIMARY]
    (NAME = N'VLDB_System'' FILENAME = N'D:SQLDataVLDB_System.mdf'' SIZE = 100MB)'
  FILEGROUP [DATA]
    (NAME = N'VLDB_Data1'' FILENAME = N'E:SQLDataVLDB_Data1.ndf'' SIZE = 1TB)'
    (NAME = N'VLDB_Data2'' FILENAME = N'F:SQLDataVLDB_Data2.ndf'' SIZE = 1TB)'
    (NAME = N'VLDB_Data3'' FILENAME = N'G:SQLDataVLDB_Data3.ndf'' SIZE = 1TB)'
    (NAME = N'VLDB_Data4'' FILENAME = N'H:SQLDataVLDB_Data4.ndf'' SIZE = 1TB)'
    (NAME = N'VLDB_Data5'' FILENAME = N'I:SQLDataVLDB_Data5.ndf'' SIZE = 1TB)
  LOG ON
    (NAME = N'VLDB_log'' FILENAME = N'L:SQLLogVLDB_Log.ldf' ' SIZE = 100GB) ;
GO
ALTER DATABASE [VLDB] MODIFY FILEGROUP [DATA] DEFAULT;

The VLDB in this scenario could potentially use the following backup strategy:

Back up the primary data file nightly.

Back up the [VLDB_Data1] file on Monday.

Back up the [VLDB_Data2] file on Tuesday.

Back up the [VLDB_Data3] file on Wednesday.

Back up the [VLDB_Data4] file on Thursday.

Back up the [VLDB_Data5] file on Friday.

Back up the transaction log every 15 minutes.

If a single data drive failed, it would be sufficient to replace the damaged disk, restore the failed data file, and replay the transaction log until the data file is synchronized with the rest of the database.

The problem with this approach is that all of the data would be spread across all of the files. If a single data file is lost in a disaster, nobody can access any of the data within the VLDB until the restore and recover have completed. So you have improved your backup duration, and your RPO potentially, but not necessarily your availability (or RTO).

SQL Server supports a feature called partial availability. With partial availability users can still access portions of the database even though certain filegroups are unavailable. For partial availability to work, the primary data file and transaction log files must always be available. This approach also works with partitioning where a single table is split into multiple partitions, and each partition is stored in a separate filegroup.

Consequently, a better technique is to locate your data more intelligently on the filegroups within the VLDB. This requires more domain knowledge about the database and how your organization uses the VLDB.

It is common for VLDBs to have very large tables that contain the following types of data:

Archive/historical data

Audit information

Logging information

Read-only data

Reference data

These tables might take up the majority of the capacity within the database. This data might not be as important as the rest of the data and can consequently be restored last. Also, it might not need to be backed up as frequently. In these cases, place these tables onto their own separate filegroups. Again, back up these filegroups at a separate frequency from the rest of the database. Back up read-only filegroups only when they are modified.

An alternative technique in deciding what tables should be split into their own filegroups takes into account the relative importance of the tables in the database.

Consider an online shopping VLDB with hundreds of tables that have the following tables:

[Orders]

[OrderHistory]

[Products]

[Customers]

In the case of a disaster incident it is critical to get these tables restored as soon as possible. You want to minimize the downtime for customer shopping! The database could be restored in the following order:

1. Primary data file

2. Products

3. Customers

4. Orders (at this stage customers could begin placing new orders)

5. Order history

6. The rest of the database

At some point in time a database can be too large to fail. At that stage, you need to consider implementing the appropriate high availability technology, such as Availability Groups and/or Log Shipping. These high availability technologies maintain multiple, separate copies of the database, which protects against instance and storage failure

In this section we will examine how you perform log backups and what to do in the case of an incident where your transaction log fills up. We will also look at some transaction log configuration options and how they will impact your disaster recovery strategy. But before we can look at how to perform log backups we need to look at the crucial concept of database recovery models, which control how much information is logged to the transaction log and potentially what amount of data you will lose in the case of a disaster occurring.

In the exam you should expect questions on what recovery models to use in given scenarios, how to deal with a full transaction log incidents and broken log chains. An exam question on tail-logs is virtually guaranteed, so make sure you understand what tail-logs, how to back them up and the recovery scenarios in which they are used.

SQL Server supports the following recovery models:

Full Under the FULL recovery model the database engine fully logs every operation in the transaction log for recoverability purposes. Transaction log backups have no dependency on the database’s data files. Under normal operations on a correctly managed SQL Server instance, no data loss occurs. The full recovery model is typically used in production environments.

Bulk-Logged The BULK_LOGGED recovery model reduces the amount of logging information written to the transaction log during minimally logged operations by only recording the extents that were modified. Consequently, transaction log backups rely on the database’s data files that were modified by the minimally logged operations being available during the backup operation. The bulk logged recovery model is not typically used in production environments.

Simple With the SIMPLE recovery model the database engine automatically reclaims the space used by operations in the transaction log. No transaction log backups are possible. The simple recovery model is typically used in non-production environments, such as development, user acceptance, and testing.

The default recovery model of a database is determined by the [model] database’s recovery model at the time of the database’s creation. The [model] database’s recovery model is full by default. The [master], [msdb], and [tempdb] system databases use the simple recovery model by default. You can change the [msdb] system database recovery model to full if you want the benefits of transaction log backups.

With the FULL recovery model, the transaction log continues to grow in size until it either fills up the disk, or reaches the transaction log’s maximum size limit. If this occurs, the database engine generates a 9002 error. Users are no longer able to modify any data within the database. That is why you need to back up the transaction log periodically as it has the effect of reclaiming space from the log. As an alternative, the transaction log can be truncated, but this is uncommon.

Use the full recovery model when any of the following apply:

You want to recover all of the data up to the point in time when the database crashed.

You need to recover to a specific point in time.

You need to recover to a marked transaction.

You need to be able to restore individual pages.

Your database contains multiple filegroups and you want to take advantage of piecemeal recovery.

You are going to implement any of the following high availability technologies:

Availability Groups

Database Mirroring

Log Shipping

The BULK_LOGGED recovery model minimally logs bulk operations such as BCP, BULK INSERT, INSERT … SELECT, and SELECT INTO. It also applies to indexing operations such as CREAT INDEX, ALTER INDEX, and DROP INDEX. Otherwise the database operates exactly like it would under the FULL recovery model.

Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery to when the bulk operation executed. The database engine only logs the extents that were modified during the minimally logged operations as opposed to the entire operation. This results in substantially less logging and consequently a much faster bulk operation.

Importantly, data loss is possible until you next back up your transaction log. This is because the database engine does not back up the extents that were modified until the next log backup. Figure 2-5 shows at a high-level how the log backup operation saves the modified extents to the log backup, by referencing the Bulk Change Map (BCM). The database engine uses the BCM as a bitmap to record which extents were modified by bulk operations.

If you lose the data files that were modified by the bulk operation before you perform your next backup, you have lost all of the data that was modified since the bulk operation. That is why it is recommended to perform a backup after your bulk operation under the bulk-logged recovery model. A full, differential, or log backup is sufficient.

Use the BULK_LOGGED recovery model temporarily when any of the following apply:

You want to perform a bulk operation with the best performance.

You do not have sufficient transaction log space, or you want to use the least amount of transaction log space.

With the SIMPLE recovery model, the database engine automatically manages the transaction log by safely deleting operations that are no longer needed for recovery purposes. This mechanism does not guarantee that the transaction log will never fill up. The transaction log can still fill up 100 percent if there is a long running transaction for example. The main benefit for using the SIMPLE recovery model is that that there are no transaction logs to manage, and it is less likely that the transaction log is going to fill up.

Use the SIMPLE recovery model when any of the following apply:

You are willing to lose all data modifications since the last full or differential database backup.

The database is being used for non-production purposes, or as a read-only copy of a production database.

You are relying on non-SQL Server based disaster recovery, such as through snapshotting technology that is typically available in virtualization or SAN technology.

Use the ALTER DATABASE statement to change a database’s recovery model:

Click here to view code image

ALTER DATABASE { database_name  | CURRENT }
SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

The Database Properties page in SQL Server Management Studio allows you to change the recovery model, as shown in Figure 2-6.

You can switch the recovery model of any database at any time. When you switch to the simple recovery model, however, you break the log backup chain. We will look at log chains shortly in the section, “Understand log chains.” Consequently, it is recommended that you perform a log backup before switching to the simple recovery model. Furthermore, when you switch from the simple recovery model it is recommended to perform a full or differential backup so as to initialize the log backup chain. A differential backup is typically smaller. Although you have switched recovery models, the database continues operating in the simple recovery model until you perform this initial backup operation.

If you want to determine what recovery model all your databases are using, you can query the [sys].[databases] system catalog as shown in Listing 2-9.

LISTING 2-9 Query the recovery model of all databases

Click here to view code image

USE master;
GO
SELECT name' recovery_model_desc
FROM sys.databases;

Under the FULL and BULK_LOGGED recovery models the database’s transaction log will continue to grow until it fills up the disk drive it is located on. One of the primary responsibilities of the database administrator is to manage the transaction logs for their databases. This is typically done by backing up the log, and we will turn our attention there next.

Transaction log backups are not possible under the simple recovery model because the database engine automatically truncates the log.

Transaction log backups are also not possible in the full or bulk-logged recovery models until a full database backup has been performed. The database engine automatically truncates the log in this scenario because there is no point in retaining transaction log records until the first full database backup.

The BACKUP LOG statement has the following options:

NO_TRUNCATE This option does not truncate the transaction log at the end of the log backup operation. It also allows the database engine to attempt a log backup in situations where the database is damaged.

NORECOVERY This option backs up the tail-log and leaves the database in the RESTORING state. Tail-logs are discussed later on in this chapter. The NORECOVERY option is useful when you need to fail over to a secondary database in an Availability Group. It is also useful when you need to back up the tail-log before your RESTORE operations.

STANDBY This option backs up the tail of the log and leaves the database in a read-only and STANDBY state. This STANDBY state effectively creates a second transaction log that allows the database to be read, but also allows log backups to be restored. When the next log backup is applied, the second transaction log is rolled back so as to bring the database back into the NORECOVERY state.

Although these BACKUP LOG options are rarely used in the field they will most likely be tested in the exam. So make sure you know the difference between them and the when to use them. The exam might contain some recovery scenario where one of these BACKUP LOG options needs to be sued.

The STANDBY option is equivalent to the BACKUP LOG WITH NORECOVERY operation followed by a RESTORE WITH STANDBY operation.

Use both the NO_TRUNCATE and NORECOVERY options to perform a best-effort log backup that does not truncate the log and changes the database into the RESTORING state in a single atomic operation.

A log chain is the continuous set of transaction log backups performed on a database after a full backup is performed. If a log chain is unbroken, the database can be recovered to the point in time when the disaster incident occurred. If the log chain is broken, you are not able to restore the database past the break in the log chain.

Consider the following scenario where the following backup strategy is used:

A full backup is performed on Sunday at 23:00.

A log backup is performed every hour from Monday till Saturday during business hours (08:00 – 18:00).

Assume the following sequence of events happens:

1. Sunday’s full backup completes successfully.

2. All the log backups complete successfully.

3. The database crashes on Saturday at 20:00.

4. Tuesday’s 15:00 transaction log backup is lost or corrupted.

In this case the log chain is broken and the database can only be restored till Tuesday at 14:00. Although all log backups after Tuesday at 14:00 are fine, they cannot be restored until the Tuesday 14:00 log backup is restored.

One way to mitigate against this kind of a scenario is to take a periodic differential backup. Assume that the above backup strategy also performed a differential backup from Monday to Saturday at 23:00.

In this case the database can be restored with no data loss by bypassing Tuesday’s 15:00 log backup. It is simply the case of performing the following actions:

1. Restore Sunday’s full backup.

2. Restore Monday’s differential backup.

3. Restore all log backups from Tuesday through Saturday.

An alternative, and potentially quicker restore would involve the following actions:

1. Restore Sunday’s full backup.

2. Restore Friday’s differential backup.

3. Restore all log backups from Saturday.

Another way to mitigate against this kind of scenario is to mirror the log backup to another backup destination using the MIRROR TO clause discussed earlier. If one of the log backups is corrupted or lost, the mirrored one can be used.

This highlights the importance of ensuring that you have an unbroken log chain. It also highlights the importance of potentially designing multiple restore paths for your mission-critical database solutions.

That is why it is important to architect your databases to have redundancy at the transaction log. The easiest way is to provide redundancy at the disk or volume level. Hardware RAID 1 or Windows Server mirroring is sufficient.

In the case of a disaster incident, the first thing that you should always think about is whether the transaction log still exists, and whether a tail-log backup can be performed. This ensures that you minimize your data loss because all committed transactions are recoverable through the tail-log backup.

A tail-log backup is not required where you do not need to restore the database to the point in time of the disaster incident.

Consider the following experiment in Listing 2-10 that performs the following actions:

1. Creates a new database.

2. Creates a new table.

3. Inserts the first record into the table.

4. Performs a full database backup.

5. Inserts the second record into the table.

6. Performs a log backup.

7. Inserts the third record into the table.

8. Simulates a disaster by deleting the database’s primary (MDF) data file.

LISTING 2-10 Orphaned log experiment

Click here to view code image

-- Set up experiment: Run selectively as and if required
/* You might have to enable xp_cmdshell by running the following:
EXEC sp_configure 'show advanced'' 1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell'' 1;
RECONFIGURE;
GO
-- Create directory for experiment
EXEC xp_cmdshell' 'md C:Exam764Ch2';
GO
*/
-- Create database
CREATE DATABASE TailLogExperimentDB
ON PRIMARY (NAME = N'TailLogExperimentDB_data'' FILENAME = N'C:Exam764Ch2
TailLogExperimentDB.mdf')
LOG ON (NAME = N'TailLogExperimentDB_log'' FILENAME = N'C:Exam764Ch2
TailLogExperimentDB.ldf')
GO
-- Create table
USE [TailLogExperimentDB]
GO
CREATE TABLE [MyTable] (Payload VARCHAR(1000));
GO
-- Insert first record
INSERT [MyTable] VALUES ('Before full backup');
GO
-- Perform full backup
BACKUP DATABASE [TailLogExperimentDB] TO DISK = 'C:Exam764Ch2TailLogExperimentDB_FULL.
bak' WITH INIT;
GO
-- Insert second record
INSERT [MyTable] VALUES ('Before log backup');
GO
-- Perform log backup
BACKUP LOG [TailLogExperimentDB] TO DISK = 'C:Exam764Ch2TailLogExperimentDB_LOG.bak'
WITH INIT;
GO
-- Insert third record
INSERT [MyTable] VALUES ('After log backup');
GO
-- Simulate disaster
SHUTDOWN;
/*
Perform the following actions:
    1. Use Windows Explorer to delete C:Exam764Ch2TailLogExperimentDB.mdf
    2. Use SQL Server Configuration Manager to start SQL Server
The [TailLogExperimentDB] database should now be damaged as you deleted the primary data
file.
*/

At this stage, you have a full and log backup that contains only the first two records that were inserted. The third record was inserted after the log backup. If you restore the database at this stage, you lose the third record. Consequently, you need to back up the orphaned transaction log.

Listing 2-11 shows the attempt to back up the orphaned transaction log.

LISTING 2-11 Attempted log backup

Click here to view code image

USE master;
SELECT name' state_desc FROM sys.databases WHERE name = 'TailLogExperimentDB';
GO
-- Try to back up the orphaned tail-log
BACKUP LOG [TailLogExperimentDB] TO DISK = 'C:Exam764Ch2TailLogExperimentDB_
OrphanedLog.bak' WITH INIT;

The database engine is not able to back up the log because it normally requires access to the database’s MDF file, which contains the location of the database’s LDF files in the system tables. The following error is generated:

Click here to view code image

Msg 945' Level 14' State 2' Line 56
Database 'TailLogExperimentDB' cannot be opened due to inaccessible files or
insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 3013' Level 16' State 1' Line 56
BACKUP LOG is terminating abnormally.

Listing 2-12 shows how to correctly back up the orphaned transaction log with the NO_TRUNCATE option.

LISTING 2-12 Orphaned log backup with NO_TRUNCATE option

Click here to view code image

-- Try to back up the orphaned tail-log again
BACKUP LOG [TailLogExperimentDB] TO DISK = 'C:Exam764Ch2TailLogExperimentDB_
OrphanedLog.bak' WITH NO_TRUNCATE;

The backup of the orphaned transaction log succeeds. You can now restore the database without any data loss. The next section will cover how to restore databases.

To clean up this experiment run the code in Listing 2-13.

LISTING 2-13 Orphaned log experiment cleanup

Click here to view code image

-- Cleanup experiment: Run selectively as and if required
/*
EXEC xp_cmdshell 'rd /q C:Exam764Ch2';
GO
EXEC sp_configure 'xp_cmdshell'' 0;
RECONFIGURE;
GO
EXEC sp_configure 'show advanced'' 0;
RECONFIGURE;
GO
USE [master];
DROP DATABASE [TailLogExperimentDB];
*/

A tail-log backup is recommended in the following situations:

If the database is online and you plan to perform a restore operation on the database. Start with a tail-log backup to avoid data loss. Don’t forget that you might want to save all transactions, even though you might be restoring to an earlier point in time.

If a database is offline and fails to start, and you plan to restore the database. Again, start with a tail-log backup to avoid data loss.

If a database is damaged and you want to recover all the transaction in the orphaned transaction log a tail-lo backup should be considered.

Click here to view code image

Msg 9002' Level 17' State 2' Line 91
The transaction log for database 'database_name' is full due to 'LOG_BACKUP'.

Because a database’s transaction log can become full due to a myriad of reasons, it is important to determine the root cause of the error. Here are the common reasons for the 9002 error:

There is no more free space in the transaction log and it has reached its maximum size limit.

There is no more free space in the transaction log and it cannot grow because there is no more free space left on the disk.

A long running transaction is preventing log truncation.

To determine what is preventing log truncation, execute the query shown in Listing 2-14 and examine the [log_reuse_wait_desc] column.

LISTING 2-14 Querying the database’s transaction log reuse wait

Click here to view code image

USE master;
GO
SELECT [database_id], [name] as 'database_name', [state_desc], [recovery_model_desc],
[log_reuse_wait_desc]
FROM [sys].[databases];

Table 2-1 shows you how to interpret the [log_reuse_wait_desc] column.

To manage a full transaction log, the most common corrective actions include:

Adding a second transaction log to the database Although the database engine can only use one LDF file at a time, a database can have multiple LDF files. By creating a second empty log file you allow the database engine to start writing log records again.

Backing up the transaction log As discussed, whenever you back up the log, the database engine automatically truncates the log, freeing up space.

Freeing up disk space on the disk volume This allows the transaction log to grow in size if automatic growth is enabled.

Increasing the size of the disk volume It is common for SQL Server to be deployed in virtualized environments. In such environments, it is easy to increase the size of the virtual disks without incurring any outage.

Increasing the size of the log The database might have a size limit configured for the transaction log.

Killing a long running transaction A long running query can be holding up the truncation operation, which in turn causes the transaction to fill up. Use the KILL statement to end the process using its Server Process ID (SPID).

Moving the log to another disk that has sufficient capacity This involves detaching the database, moving the log files, and re-attaching the database. This might not be an option in scenarios where the database cannot be taken offline.

Truncating the transaction log This is not recommended because it breaks your log-chain. If you need to truncate the log, it is highly recommended that you immediately perform a full backup so as to re-initialize your log chain.

To kill a long running transaction query the [sys].[dm_tran_database_transactions] DMV to determine what transactions are currently executing in the database and how long they have been executing. As an alternative, execute the DBCC OPENTRAN command within the database. The DBCC OPENTRAN command displays information about the oldest active transaction within the transaction log of a database:

Click here to view code image

Transaction information for database 'WideWorldImporters'.
Oldest active transaction:
    SPID (server process ID): 69
    UID (user ID) : -1
    Name          : user_transaction
    LSN           : (34:12:7)
    Start time    : Sep 30 1984 12:13:21:666AM
    SID           : 0x01060000000000051500000820672ae6c43f9eb9788953c9e9030000
DBCC execution completed. If DBCC printed error messages' contact your system
administrator.

Use the KILL statement to kill the offending transaction. Listing 2-15 show an example of how to kill the server process ID (SPID) identified by the DBCC OPENTRAN command.

LISTING 2-15 KILL statement

KILL 69;

Be careful killing processes because they might be running important transactions. You cannot kill your own process. You should not kill system processes, including the following:

AWAITING COMMAND

CHECKPOINT SLEEP

LAZY WRITER

LOCK MONITOR

SIGNAL HANDLER

In the exam you might get asked what corrective action to take when the transaction log become full in a specific scenario. Use the techniques in this section to work out the best corrective action in the exam.

With the release of SQL Server 2014, the database engine supports a feature called delayed durability. This feature potentially impacts the amount of data that is lost in the event of a disaster incident.

By default, when operations are recorded to the transaction log, they are initially written to the log cache (log buffers), which is located in memory. As part of the write ahead logging (WAL) protocol, the log cache is flushed to disk whenever one of the following occurs:

A commit transaction record is written.

The 60KB log block/buffer is filled.

A CHECKPOINT operation is performed.

The [sys].[sp_log_flush sysem] stored procedure is executed.

When delayed durability is enabled, the database engine no longer flushes the log buffers when the commit transaction occurs. It tries to wait until 60KB of records are written to the log buffer before flushing the buffer. This can improve performance where you have a transactional database solution that commits a large volume of small transactions.

Use the ALTER DATABASE statement to enable delayed durability:

Click here to view code image

ALTER DATABASE database_name
SET DELAYED_DURABILITY = DISABLED | ALLOWED | FORCED ;

Setting delayed durability to FORCED forces all transactions to use delayed durability. Setting delayed durability to ALLOWED requires the developer to explicitly invoke delayed durability using the COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON) statement.

The next change made to the transaction log architecture is how it can use the latest generation of flash storage. We will now examine how SQL Server supports persistent memory for transaction logs.

Due to current capacity limits of NVDIMM you cannot place the entire transaction log onto this SCM. Your transaction log size might easily exceed the maximum NVDIMM capacity. Also, it would not be cost effective for most customers.

Consequently, with the initial support for NVDIMM you can only place the transaction log’s cache (or log buffers that live in memory) onto this new memory type. The main benefit is that the log cache contains multiple commit records. Normally the log cache is flushed as soon as a commit record is written. On high performance transactional databases this can become a bottleneck, with high WRITELOG waits. Figure 2-7 shows how the log buffer is stored in NVDIMM memory and contains multiple commit records.

As the log records are fully durable as soon as they are copied into the log buffer there is no exposure to data loss, as in the case of delayed durability that was discussed in the previous section. Consequently, you do not need to back up the log more frequently to minimize potential data loss.

To take advantage of NVDIMM-N memory you need to perform the following tasks:

1. Install NVDIMM memory in your server.

2. Format the NVDIMM memory as a Direct Access (DAX) volume in Windows Server 2016.

3. Create a second 20MB transaction log for the database using the ALTER DATABASE statement:

Click here to view code image

ALTER DATABASE database_name
ADD LOG FILE (
NAME = logical_file_name'
FILENAME = os_file_name'
SIZE = 20 MB)

The database engine automatically detects that the second transaction log file is located on a DAX volume and creates a 20MB log file. This second log file on the DAX volume is always 20MB in size, irrespective of the size specified in the ALTER DATABASE statement. This might change in the future.

NVDIMM support for the log buffer is only available for SQL Server installed on physical machines. Virtualized SQL Server instances and failover clusters are not supported. Only secondary replicas are supported in Availability group architectures.

Backups can be scheduled to occur automatically in SQL Server through the SQL Server Agent. We will cover SQL Server Agent in more detail in Chapter 3, “Manage and monitor SQL Server instances.”

The following steps show how to automate a full database backup through the SQL Server Agent. In this example, the backup of the [master] and [msdb] system databases are scheduled to be executed nightly at 02:00.

1. Open SQL Server Management Studio.

2. Expand the SQL Server Agent folder.

3. Right-click the Jobs folder, and select New Job.

4. In the New Job dialog box, on the General page, provide a Name and Description for the backup, as shown in Figure 2-8. It is best practice to configure the Owner as sa. You can create your own categories through the sp_add_category stored procedure located in the [msdb] system database.

5. Click the Steps page.

6. Click the New button to create a new step for the job.

7. In the New Job Step dialog box, on the General page, provide a name for the step and the BACKUP command that is performed to back up the [master] system database, as shown in Figure 2-9.

8. Click the Parse button to ensure the T-SQL command is syntactically correct.

9. Click the OK button to close the Parse Command Text dialog box.

10. Click the Advanced page.

11. In the New Job Step dialog box, on the Advanced page, configure the job step to perform the next step on failure, as show in Figure 2-10. This ensures that the job attempts to back up the [msdb] database, even though the [master] database backup failed.

12. Click OK.

13. Click the New button as shown in Figure 2-11 to create another job step.

14. In the New Job Step dialog box, on the General page, provide a name for the step and the BACKUP command for the backup of the [msdb] system database, as shown in Figure 2-12.

15. Click the OK button.

16. In the New Job Step dialog box, on the Advanced page, ensure the job steps are performed in the correct order, as show in Figure 2-13. Note how you can configure the job to perform any step in the job first.

17. Click the Schedules page.

18. In the New Job Step dialog box, on the Schedules page, click the New button to schedule when you want the database backup to execute.

19. In the New Job Schedule dialog box, configure the name, frequency, and duration required, as shown in Figure 2-14.

20. Click the OK button.

21. Confirm that the schedule has been saved correctly, as shown in Figure 2-15.

22. Click the Notifications page.

23. In the New Job Step dialog box, on the Notifications page, configure who needs to be notified if a job fails, succeeds, or completes, as shown in Figure 2-16. We will look at how you create operators in the “Create and manage operators” section in Chapter 3.

24. Click the OK button to schedule the backup operation. The job is created in the Jobs folder of SQL Server Management Studio.

25. To document the scheduled job, right-click the job, select Script Job As, then CREATE TO, and finally New Query Editor Window.

Listing 2-16 shows the T-SQL script created for the scheduled backup job.

LISTING 2-16 Scheduling a backup through SQL Server Agent

Click here to view code image

USE [msdb  ]
GO
/****** Object:  Job [Full Backup - System Databases]    Script Date: 26/09/2017
11:56:45 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [DBA]    Script Date: 26/09/2017 11:56:45 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA' AND category_
class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB'' @type=N'LOCAL'' @name=N'DBA'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Full Backup - System Databases''
        @enabled=1'
        @notify_level_eventlog=0'
        @notify_level_email=2'
        @notify_level_netsend=0'
        @notify_level_page=0'
        @delete_level=0'
        @description=N'Perform a full database backup of the following system databases:
- master
- msdb

Schedule:
- Nightly at 2:00AM''
        @category_name=N'DBA''
        @owner_login_name=N'sa''
        @notify_email_operator_name=N'Victor Isakov'' @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup master database]    Script Date: 26/09/2017 11:56:45 PM
******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId' @step_name=N'Backup master
database''
        @step_id=1'
        @cmdexec_success_code=0'
        @on_success_action=3'
        @on_success_step_id=0'
        @on_fail_action=3'
        @on_fail_step_id=0'
        @retry_attempts=3'
        @retry_interval=5'
        @os_run_priority=0' @subsystem=N'TSQL''
        @command=N'BACKUP DATABASE master
TO DISK = ''R:SQLBackupmaster.bak''
WITH INIT;''
        @database_name=N'master''
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup msdb database]    Script Date: 26/09/2017 11:56:45 PM
******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId' @step_name=N'Backup msdb
database''
        @step_id=2'
        @cmdexec_success_code=0'
        @on_success_action=1'
        @on_success_step_id=0'
        @on_fail_action=2'
        @on_fail_step_id=0'
        @retry_attempts=0'
        @retry_interval=0'
        @os_run_priority=0' @subsystem=N'TSQL''
        @command=N'BACKUP DATABASE msdb
TO DISK = ''R:SQLBackupmsdb.bak''
WITH INIT;''
        @database_name=N'master''
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId' @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId' @name=N'Nighty @ 02:00''
        @enabled=1'
        @freq_type=4'
        @freq_interval=1'
        @freq_subday_type=1'
        @freq_subday_interval=0'
        @freq_relative_interval=0'
        @freq_recurrence_factor=0'
        @active_start_date=20170926'
        @active_end_date=99991231'
        @active_start_time=20000'
        @active_end_time=235959'
        @schedule_uid=N'47e87563-0e09-4856-821d-eec181839d09'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId' @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

In the exam you might get asked about the stored procedures highlighted in Listing 2-16 and their parameters. Make sure you are familiar with them for the exam.

This section showed you how to schedule Transact-SQL statements via SQL Server Agent jobs. If your SQL Server instance has a large number of databases this will be cumbersome to set up and difficult to manage. A better approach might be to take advantage of maintenance plans and we will look at their capabilities next.

Use the Maintenance Plan Wizard to create a basic maintenance plan. You can then customize the maintenance plan further by opening up the maintenance plan in SQL Server Management Studio. We will go through such an exercise in this section.

Maintenance plans support the following tasks:

Back Up Database This task allows you to specify the source databases, destination files or tapes, and overwrite options for a transaction log backup. It supports the following types of backups:

Differential

Full

Transaction Log

Check Database Integrity This task performs internal consistency checks of the data and index pages within the database.

Clean Up History This task deletes historical data associated with Backup and Restore, SQL Server Agent, and maintenance plan operations. This task allows you to specify the type and age of the data to be deleted.

Execute SQL Server Agent Job This task allows you to select SQL Server Agent jobs to run as part of the maintenance plan.

Execute T-SQL Statement Task This task allows you to run any T-SQL script.

Maintenance Cleanup Task This task removes files left over from executing a maintenance plan.

Notify Operator Task This task sends an email to any SQL Server Agent operator.

Rebuild Index This task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth.

Reorganize Index This task defragments and compacts clustered and non-clustered indexes on tables and views. This improves index-scanning performance.

Shrink Database This task reduces the disk space consumed by the database and the log files by removing empty data, index, and log pages.

Update Statistics This task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.

It is common to perform optimization tasks and integrity checks with your database backups. This helps ensure that your database backup contains no corruptions and that the database will run optimally straight away if it ever has to be restored. At the very least, configure the following maintenance tasks for all databases to minimize data loss and ensure optimal performance:

Perform database backups to meet your RPO and RTO SLAs.

Perform database consistency checks to detect and potentially fix database corruptions.

Rebuild and reorganize indexes to ensure optimal query performance and efficient storage usage.

Update statistics to ensure optimal query performance.

For simpler environments, it is common to create a single maintenance plan to perform all of these maintenance tasks. For more complex requirements, schedule multiple customer job steps.

The following steps demonstrate how to create a maintenance plan for all user databases that automatically performs a full database backup, consistency check, and index optimization tasks weekly on Sunday at 01:00:

1. Open SQL Server Management Studio.

2. Expand the Management folder.

3. Right-click the Maintenance Plans folder, and select the Maintenance Plan Wizard.

4. On the welcome screen, click the Next button, as show in Figure 2-17.

5. In the Select Plan Properties screen, provide a name and description as shown in Figure 2-18.

6. Click the Change button to schedule your maintenance plan.

7. Define your desired schedule, as show in Figure 2-19, and click the OK button.

8. Click the Next button.

9. In the Select Maintenance Tasks screen, check the following options, as show in Figure 2-20:

Check Database Integrity

Rebuild Index

Update Statistics

Clean Up History

Back Up Database (Full)

Maintenance Cleanup Task

10. In the Select Maintenance Task Order screen, reorder the tasks so that the Clean Up History task is last, as shown in Figure 2-21.

11. In the Define Database Check Integrity Task, select the Database drop-down list.

12. In the drop-down list, select All User Databases (Excluding master, model, msdb), as show in Figure 2-22.

13. Select the Ignore Databases When The State Is Not Online to ensure that there are no backup errors. Databases that are offline cannot be backed up.

14. Click the OK button.

15. In the Define Database Check Integrity Task, ensure the following options are checked, as shown in Figure 2-23:

Include Indexes

Physical Only

16. Click the Next button.

17. In the Define Rebuild Index Task, select All User Databases, and configure the following options, as shown in Figure 2-24:

Configure a fillfactor of 100% by selecting 0% free space to ensure that all data pages are filled 100 percent.

Use the [tempdb] system database for sorting operations.

Perform an online index operation for all possible tables.

Rebuild indexes offline for tables that do not support online index operations.

Use a low priority for the index rebuild operations.

If the index operation experiences blocking, abort the operation after five minutes.

18. Click the Next button.

19. In the Define Update Statistics Task, select All User Databases, and configure the following options, as shown in Figure 2-25:

Update column statistics only. There is no need to update index statistics because the previous task performs that.

20. Click the Next button.

21. In the General tab of the Define Back Up Database (Full) Task screen, select All User Databases, as shown in Figure 2-26.

22. Click the Destination tab.

23. In the Destination tab of the Define Back Up Database (Full) Task screen, configure the destination folder and the following options, as shown in Figure 2-27.

Select the Create A Sub-Directory For Each Database option. This option makes it easier to manage the database backups.

24. Click on the Options tab

25. In the Options tab of the Define Back Up Database (Full) Task screen, configure the following options, as shown in Figure 2-28.

Enable backup compression.

Perform checksum on the database. This helps check that the database contains no errors and can be restored.

Verify the backup integrity. This helps check that the backup contains no errors and can be restored.

26. In the Define Maintenance Cleanup Task, specify the backup location and configure the following cleanup options, as shown in Figure 2-29.

Include first-level options. This matches our configuration in the Define Backup Database Destination screen.

Delete backup files older than 5 weeks. This helps ensure that we do not run out of space in our backup store.

27. Click the Next button.

28. In the Define History Cleanup Task, change the Remove Historical Data Older Than configuration option to 18 months, as shown in Figure 2-30.

29. Click the Next button.

30. In the Select Report Options screen, change the report folder location, as shown in Figure 2-31. It is important to change it from the default directory, otherwise you will potentially generate thousands of small report files in SQL Server error log directory.

31. Click the Next button.

32. In the Complete The Wizard screen, review your configuration, as shown in Figure 2-32.

33. Click the Finish button to create the maintenance plan.

34. In the Maintenance Plan Wizard Progress screen, ensure that all of the actions have run successfully, as shown in Figure 2-33.

35. Click the Close button.

It is a common mistake not to configure alerting for when scheduled backup operations fail. If the SQL Server Agent crashes or is not restarted by a database administrator after a manual restart of SQL Server, no scheduled backup operations will execute. Without any alerting or notification this could lead to substantial data loss if a disaster occurs. We will examine how you can configure altering for failed backup operations next.

To configure alerting you need to perform the following high level steps:

1. Configure Database Mail.

2. Configure SQL Server Agent to use Database Mail.

3. Define operators.

4. Configure notifications when a job fails.

We will look at how you configure Database Mail in Chapter 3. In this section we will focus more on how to create an operator and configure alerting.

To configure an operator, perform the following steps:

1. Open SQL Server Management Studio.

2. Expand the SQL Server Agent folder.

3. Right-click the Operators folder, and select New Operator.

4. Configure the following properties for the operator, as shown in Figure 2-34.

Name

E-mail Name

SQL Server Agent jobs easily allow you to be alerted when a job fails, succeeds, or completes. Be careful configuring jobs only to alert you when they fail. If the SQL Server Agent service is shutdown, or a job does not run for any reason, you will not be notified at all. As said earlier, it is better to configure important jobs to notify you when they complete either way.

As an example we will configure alerting for the “Full Backup – System Databases” SQL Server Agent job that we created earlier in this section. To configure alerting, perform the following steps:

1. Open SQL Server Management Studio.

2. Expand the Jobs folder.

3. Right-click the backup job that you want to configure an email notification for, and click Properties.

4. Click the Notifications page in the Job Properties dialog box.

5. Configure the operator that you want to be notified when the job completes, as shown in Figure 2-35.

You can also configure notifications for maintenance plans. The Maintenance Plan Wizard does not provide that functionality in its setup beyond the ability to email the maintenance plan report. However, you can customize a maintenance plan to notify you if with a different email depending on whether a backup fails or succeeds.

In the following example we will customize the “Full Backup & Optimization – User Databases” maintenance plan we created earlier in this section. To configure a notification in an existing maintenance plan, perform the following steps:

1. Open SQL Server Management Studio.

2. Expand the Management folder.

3. Expand the Maintenance Plans folder.

4. Double-click the maintenance plan you want to configure. The maintenance plan is opened up in the Maintenance Plan Designer, as shown in Figure 2-36.

5. Drag a Notify Operator Task from the Toolbox onto the design surface below the Back Up Database (Full) task, as shown in Figure 2-37.

6. Click on the name in the notify operator task and rename it to “Notify Operator of Success” as shown in Figure 2-38.

7. Double-click the renamed Notify Operator of Success task, and configure the following properties, as shown in Figure 2-39.

Specify which operators are notified in the Operators To notify check box.

Provide an email subject in the Notification Message Subject text box to indicate the backups have completed successfully.

Provide an email body in the Notification Message Body text box to indicate the backups have completed successfully.

8. Click the OK button.

9. Click the Back Up Database (Full) task, and drag the green arrow onto the Notify Operator of Success task, as shown in Figure 2-40. The green arrow indicates that the Notify Operator of Success task performs only if the Back Up Database (Full) task is executed successfully.

10. Drag a second Notify Operator Task from the Toolbox onto the design surface to the left of the Notify Operator of Success task, and rename the task to “Notify Operator Of Failure.”

11. Double-click on the renamed Notify Operator of Failure task and configure the following properties:

Specify which operators are notified in the Operators To notify check box.

Provide an email subject in the Notification Message Subject text box to indicate that backups have failed.

Provide an email body in the Notification Message Body text box to indicate that the backups have failed.

12. Click the Back Up Database (Full) task and drag the green arrow onto the Notify Operator of Failure task.

13. Right-click the green arrow between the Back Up Database (Full) task and the Notify Operator of Failure task.

14. Select the Failure precedence, as shown in Figure 2-41.

15. Ensure that there is a red arrow between the Back Up Database (Full) task and the Notify Operator of Failure task, as shown in Figure 2-42. The red arrow indicates that the Notify Operator of Failure task only executes if the Back Up Database (Full) task fails.

16. Close the maintenance plan in SQL Server Management Studio to save the changes.

Another alternative is to configure alerts for failed backups by taking advantage of SQL Server Agent alerts. SQL Server Agent has the capability of notifying operators whenever a SQL Server event alerts occurs. SQL Server event alerts are raised based on one of the following:

An error number

An error’s severity level

When the error message contains a particular string

The benefit of using alerts is that they can catch all backup errors, irrespective of how the backup statement was executed. For an alert to fire you need to know what error number is generated when a backup fails. The database engine maintains all the error messages in the [sys].[messages] table in the [master] system database. So you can query it to find the specific error you are interested in.

To configure an alert when a backup fails, perform the following steps:

1. Open SQL Server Management Studio.

2. Connect to your SQL Server instance.

3. Click the New Query button in the Standard tool bar to open a new query window.

4. Run the query, as shown in Figure 2-43 in the [master] database. The [sys].[messages] system table contains the error messages used by the database engine. You can use error number 3041 for your backup failure alert.

5. Expand the Alerts folder.

6. Right-click the Alerts folder, and select New Alert.

7. In the General page of the New Alert dialog box, configure the following properties, as shown in Figure 2-44.

Provide a name for the alert in the Name text box.

Specify 3041 in the Error Number text box.

8. Click the Response page of the New Alert dialog box, and configure the following properties, as shown in Figure 2-45.

Select the Notify Operators check box.

Select the Email check box for all the operators you want the alert sent to.

9. Click the Options page of the New Alert dialog box and configure the following properties, as shown in Figure 2-46.

Select the Email check box for the Include Alert Error Text in options.

Configure the Delay Between Responses option for 5 minutes. This prevents too many emails being sent if an alerts fires multiple times within the 5 minute interval.

10. Click the OK button to create the alert.

Listing 2-17 shows how to create the same alert in T-SQL.

LISTING 2-17 Create [Backup Error] alert

Click here to view code image

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Backup Error''
        @message_id=3041'
        @severity=0'
        @enabled=1'
        @delay_between_responses=300'
        @include_event_description_in=1'
        @category_name=N'[Uncategorized]''
        @job_id=N'00000000-0000-0000-0000-000000000000'

In Skill 2.1 we looked at how to develop a backup strategy. The backup strategy that you use as a result of those skills and your experience will very much dictate what options you have available when a disaster occurs. This, in turn, dictates your restore strategy. So the skills in this section will closely match what was covered in Skill 2.1.

In this section we will look at the skills required to restore a database correctly and in a timely fashion. Be careful when restoring a database as you do not want to accidently delete data that is critical to your restore strategy, such as with the tail-log.

In the exam you might get a restore question that has a complex backup strategy, specific details as to what has gone wrong during a disaster and a business requirement to restore the crashed database. Always make sure you read the business requirements to ensure you understand whether you need to restore the database as quickly as possible, or with no data loss or with minimal data loss. You might then be as asked what order of restore statements will be required to meet your business objectives. Watch out for broken log chains!

There are multiple reasons why you need to initiate a restore procedure, including:

Recover from a disaster incident.

Recover from user error.

Refresh a database in a non-production environment from a production database backup.

Test your disaster recovery procedures.

Set up a high-availability solution such as Log Shipping or Availability Groups.

Perform an upgrade of a database between different versions of SQL Server.

In the case of a disaster incident, or user error, it is easy to panic and make mistakes. Consequently, to recover from a disaster incident, or user error, use the following procedure:

1. Do not panic.

2. Assess the situation. Start thinking about the people, processes, and recovery assets required.

3. Stop access to the database, if necessary.

4. Check to see if the tail-log exists.

5. Back up the tail-log, if it exists.

6. Assess the damage/corruption in the database.

7. Assess whether a restore strategy is required.

8. Assess whether point-in-time recovery is required.

9. Assess what backups exist.

10. Verify that the backups are current and valid.

11. Plan your restore strategy.

12. Initiate your restore process.

When planning your restore strategy, you need to ensure that all the required backups exist and are valid. You do not want to initiate a restore sequence of 10 log backups only to find that the ninth log backup is missing or corrupt.

Before you initiate a restore process, ensure that your backup devices have no errors and can be restored. The RESTORE statement has a number of options that allow you to investigate and check the backup media and backup set:

FILELISTONLY This statement returns a list of the database and log files contained in the backup set. This only lists the files without validating their contents.

HEADERONLY This statement returns the backup header information for all backup sets on a particular backup device. This lists the header information without further validation.

LABELONLY This statement returns information about the backup media identified by the given backup device. This lists the media information without further validation.

VERIFYONLY This statement verifies the backup, but it does not perform the restore operation. It checks whether the backup set is complete and the entire backup is readable. The aim of the RESTORE VERIFYONLY operation is to be as close to an actual restore operation as practical. The RESTORE VERFIYONLY performs a number of checks that include:

Checking some header fields of database pages, such as the Page ID.

The backup set is complete and all volumes are readable.

The checksum is correct, if present.

There is sufficient space on the destination disks.

Listing 2-18 shows an example of how to verify the backups.

LISTING 2-18 Verifying a backup set

Click here to view code image

RESTORE VERIFYONLY
FROM DISK = 'B:SQLBackupWordWideImporters_FULL.bak'

Don’t forget that you might have multiple restore sequences for a database. These different restore sequences might have substantially different restore durations. Different restore sequences might also protect against backup set damage or corruption.

Let’s assume you have an On-Line Transactional Processing (OLTP) database that is 400GB in size. The database is only used during weekdays between 09:00 and 17:00. The database has the following backup strategy:

A full backup is performed every Sunday at 20:00.

The full backup is 250GB in size (due to compression).

The full backup takes under two hours to complete.

A differential backup is performed every Monday, Tuesday, Wednesday, Thursday, and Friday at 23:00.

These differential backups take under one hour to complete.

These differential backups typically start off being 5GB in size, and grow to 9GB in size by Friday.

Log backups are performed every hour from 09:00 to 18:00.

These log backups are on average 1GB in size, and take 15 minutes to complete.

If the database’s data files crash on Friday at 10:30 you could use the following restore sequence:

1. Perform a tail-log backup.

2. Restore the full backup.

3. Restore Monday’s 10 log backups.

4. Restore Tuesday’s 10 log backups.

5. Restore Wednesday’s 10 log backups.

6. Restore Thursday’s 10 log backups.

7. Restore Friday’s 9:00 and 10:00 backup.

8. Restore the tail log.

All the log restores would involve replaying 42GB of log backups. It would be quicker to use the following restore sequence:

1. Perform a tail-log backup.

2. Restore the full backup.

3. Restore Thursday’s differential backup.

4. Restore Friday’s 9:00 and 10:00 backup.

5. Restore the tail log.

Because this restore sequence would involve restoring an 8GB differential backup followed by 2GB worth of log backups, it would be much quicker.

If the differential backups grew by 15GB each day, Thursday’s differential backup would be 60GB in size. In this case, it would probably be quicker to restore all 42 log backups.

Now let’s assume that Thursday’s differential failed due to a lack of disk space. In this case you would use the following restore sequence:

1. Perform a tail-log backup.

2. Restore the full backup.

3. Restore Wednesday’s differential backup.

4. Restore Thursday’s 10 log backups.

5. Restore Friday’s 9:00 and 10:00 backup.

6. Restore the tail log.

Take advantage of both differential and log backups to give your disaster recovery plan both flexibility and resilience.

Restore the entire database.

Restore a part of a database (perform a partial restore).

Restore a transaction log.

Restore specific files of a database.

Restore a specific filegroup of a database.

Restore specific pages of a database.

Revert a database snapshot.

We will cover all of these restore operations in this section and give examples of the syntax. The exam will be testing your knowledge of the syntax and the various options used to restore a database and its log.

The RESTORE statement is used to perform all restore operations. Use the following statement to restore a database:

Click here to view code image

RESTORE DATABASE { database_name | @database_name_var }
 [ FROM <backup_device> [ '...n ] ]
 [ WITH
   {
    [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
   | '  <general_WITH_options> [ '...n ]
   | ' <replication_WITH_option>
   | ' <change_data_capture_WITH_option>
   | ' <FILESTREAM_WITH_option>
   | ' <service_broker_WITH options>
   | ' <point_in_time_WITH_options—RESTORE_DATABASE>
   } [ '...n ]
 ]
[;]

Use the following statement to restore a log:

Click here to view code image

--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var }
 [ <file_or_filegroup_or_pages> [ '...n ] ]
 [ FROM <backup_device> [ '...n ] ]
 [ WITH
   {
     [ RECOVERY | NORECOVERY | STANDBY =
        {standby_file_name | @standby_file_name_var }
       ]
    | '  <general_WITH_options> [ '...n ]
    | ' <replication_WITH_option>
    | ' <point_in_time_WITH_options—RESTORE_LOG>
   } [ '...n ]
 ]
[;]

As with the BACKUP statement, let’s turn our attention to the various options supported by the RESTORE operation.

MOVE This option is used to relocate the database’s set of files during the restore operation. It is commonly used when restoring a database to a different SQL Server instance that has a different storage layout.

NORECOVERY This option specifies the restore operation to not roll back any uncommitted transactions. The database remains unavailable. Committed transactions are committed to the database. Uncommitted transactions are replayed/rolled forward. The database engine “assumes” that subsequent log restores commit these uncommitted transactions.

RECOVERY This option specifies the restore operation to roll back any uncommitted transactions before making the database available for use. All committed transactions are included within the database. RECOVERY is the default behavior.

REPLACE This option bypasses the database engine’s internal safety check that helps you by automatically preventing you from overwriting existing database files. The safety checks include:

Overwriting existing files.

Restoring over a database that is using the full or bulk-logged recovery model where a tail-log backup has not been taken, and the STOPAT option has not been specified.

Restoring over an existing database with a backup of another database.

STANDBY This option creates a second transaction log that allows the database to be read, but also allows subsequent log backups to be restored. This can be useful in recovery situations where you want to inspect the database between log restores. When the subsequent log is restored, the database engine rolls back all the operations from the second transaction log so that the database is back in the NORECOVERY mode when the STANDBY option is specified.

Listing 2-19 shows an example of a restore sequence that restores the following from a share:

Initial full backup, to a new location

Differential backup

Last three transaction log backups that occurred after the differential backup

Note the use of the recovery options throughout the code. Also note the sequence numbers use in the FILE option. This is what the exam might test you on.

LISTING 2-19 Restoring a database

Click here to view code image

USE [master];
GO
-- Restore full backup
RESTORE DATABASE [WorldWideImporters]
    FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_FULL.bak'
    WITH NORECOVERY '
    MOVE 'WorldWideImporters_Data' TO 'D:SQLDataWorldWideImporters_Data.mdf''
    MOVE 'WorldWideImporters_Log' to 'L:SQLLogWorldWideImporters_Log.ldf';
GO
-- Restore differential backup
RESTORE DATABASE [WorldWideImporters]
    FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_DIFF.bak'
    WITH NORECOVERY ;
GO
-- Restore last 3 log backups
RESTORE LOG [WorldWideImporters]
    FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_LOG.bak'
    WITH FILE = 11' NORECOVERY ;
GO
RESTORE LOG [WorldWideImporters]
    FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_LOG.bak'
    WITH FILE = 12' NORECOVERY ;
GO
RESTORE LOG [WorldWideImporters]
    FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_LOG.bak'
    WITH FILE = 13' NORECOVERY ;
GO
-- Make database available
RESTORE DATABASE [WorldWideImporters]
    WITH RECOVERY;
GO

It would be equivalent to restore the last log backup using the WITH RECOVERY option instead, as show in Listing 2-20.

LISTING 2-20 Restoring last log backup

Click here to view code image

RESTORE LOG [WorldWideImporters]
FROM DISK = '\SQLBACKUPSSQLBackupWordWideImportersWordWideImporters_LOG.bak'
WITH FILE = 13' RECOVERY;

Reverting a database overwrites the data files, overwrites the old transaction log file, and rebuilds the transaction log. Therefore, it is recommended to perform a log backup before reverting a database. It is further recommended to perform a full backup after the revert operation completes to initialize the log chain again.

Listing 2-21 shows an example of how to revert a database snapshot.

LISTING 2-21 Revert a Database Snapshot

Click here to view code image

USE master;
GO
RESTORE DATABASE [WorldWideImporters]
FROM DATABASE_SNAPSHOT = 'WorldWideImporters_20160917';

Restructure your database to use filegroups if you want to take advantage of this feature. Make sure you engage your business to determine the relative priority of the tables within the databases.

A piecemeal restore starts with an initial restore sequence called the partial-restore sequence. The partial-restore sequence must restore and recover the primary filegroup. During the partial-sequence sequence the database goes offline.

Use the following statement to start a partial-restore sequence:

Click here to view code image

RESTORE DATABASE { database_name | @database_name_var }
   <files_or_filegroups> [ '...n ]
 [ FROM <backup_device> [ '...n ] ]
   WITH
      PARTIAL ' NORECOVERY
      [  ' <general_WITH_options> [ '...n ]
       | ' <point_in_time_WITH_options—RESTORE_DATABASE>
      ] [ '...n ]
[;]

After the partial-restore sequence, perform one or more filegroup-restore sequences. Each filegroup-restore sequence restores and recovers on or more filegroups. At the end of each filegroup-restore sequence, the restored filegroups are available to users. Prioritize the filegroups according to their importance to the users and how long they take to restore, until the entire database has been recovered and is online.

Piecemeal restores work with all recovery models, although there are limitations with the SIMPLE recovery model.

For a database that is using the full or bulk-logged recovery model, use the following steps to perform a piecemeal restore:

1. Perform the partial-restore sequence on the primary filegroup and any other important file groups.

2. Perform a filegroup-restore sequence on the remaining secondary file groups in order of their importance.

For a database that is using the simple recovery model, use the following steps to perform a piecemeal restore:

1. Perform the partial-restore sequence on the primary filegroup and all read/write secondary filegroups.

2. Perform a filegroup-restore sequence on the remaining read-only secondary file groups in order of their importance.

Assume you have a database named [OnlineStore] that is used by an online shopping website that has the following structure, as shown in Listing 2-22:

A primary filegroup contains no user tables and is only 100MBs in size.

A [Orders] secondary filegroup that has all the critical tables (like [Customers], [Products], [Orders], and [OrderDetails]) that enables customers to place orders. The [Sales] filegroup is 20GB in size.

A [CompletedOrders] secondary filegroup that contains historical tables such as [CompletedOrders], [CompletedOrderDetails] that holds completed orders for the last two years. The [CompletedOrders] filegroup is 200GB in size.

A [Data] default secondary filegroup that contains the remainder of the operational tables used in the database. The [Data] filegroup is 10GB in size.

An [Archive] secondary filegroup that holds data that is older than two years. The [Archive] group is 1TB in size because it goes back 10 years.

LISTING 2-22 [OnlineStore] database definition

Click here to view code image

CREATE DATABASE [OnlineStore]
 ON  PRIMARY
( NAME = N'OnlineStore_Primary', FILENAME = N'D:SQLDataOnlineStore_Primary.mdf'
, SIZE = 100MB),
 FILEGROUP [Archive]
( NAME = N'OnlineStore_Archive', FILENAME = N'E:SQLDataOnlineStore_Archive.ndf'
, SIZE = 1TB),
 FILEGROUP [CompletedOrders]
( NAME = N'OnlineStore_CompletedOrders', FILENAME = N'F:SQLDataOnlineStore_
CompletedOrders.ndf' , SIZE = 200GB),
 FILEGROUP [Data]
( NAME = N'OnlineStore_Data', FILENAME = N'G:SQLDataOnlineStore_Data.ndf' , SIZE
= 10GB),
 FILEGROUP [Orders]
( NAME = N'OnlineStore_Orders', FILENAME = N'H:SQLDataOnlineStore_Orders.ndf' ,
SIZE = 20GB)
 LOG ON
( NAME = N'OnlineStore_Log', FILENAME = N'L:SQLLogOnlineStore_Log.ldf' , SIZE =
1GB)
GO
ALTER DATABASE [OnlineStore]
MODIFY FILEGROUP [Data] DEFAULT;

Assume you have taken the following backups:

Full backup

Differential backup

Log backup

Assume that the D, E, F, G, and H drives have all failed. Only the L drive that contains the transaction log has survived. You need to recover the database ASAP so that users can place orders.

Use the following steps to perform a piecemeal restore off the [OnlineStore] database:

1. Perform a tail-log backup, restore the primary file group, and bring the database online, as shown in Listing 2-23.

LISTING 2-23 Partial-restore sequence

Click here to view code image

-- Back up orphaned transaction log to minimize data loss
USE [master];
GO
BACKUP LOG [OnlineStore] TO DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.bak' WITH
NO_TRUNCATE;
-- Start partial-restore sequence
USE [master]
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'PRIMARY' FROM DISK = 'B:SQLBackupOnlineStore_FULL.bak' WITH
NORECOVERY, PARTIAL;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'PRIMARY' FROM DISK = 'B:SQLBackupOnlineStore_DIFF.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'PRIMARY' FROM DISK = 'B:SQLBackupOnlineStore_LOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'PRIMARY' FROM DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore] WITH RECOVERY;

2. Restore and recover the [Orders] file group and bring it online, as shown in Listing 2-24.

LISTING 2-24 [Orders] filegroup-restore sequence

Click here to view code image

USE [master];
GO
-- Restore Orders filegroup and bring it online
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Orders' FROM DISK = 'B:SQLBackupOnlineStore_FULL.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Orders' FROM DISK = 'B:SQLBackupOnlineStore_DIFF.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Orders' FROM DISK = 'B:SQLBackupOnlineStore_LOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Orders' FROM DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore] WITH RECOVERY

3. Check to make sure that the [Orders] filegroup is online and that users can query the [Orders] table, as shown in Listing 2-25.

LISTING 2-25 Check partial availability of database files

Click here to view code image

USE [OnlineStore];
GO
-- Check to see if [Orders] filegroup is online
SELECT file_id, name, type_desc, state_desc
FROM sys.database_files
GO
-- Ensure users can query the critical tables
SELECT * FROM  [Orders]

Figure 2-47 shows the result set of the query that checks to see what filegroups are available within the database.

4. Restore and recover the [Data] and [CompletedOrders] filegroups, as shown in Listing 2-26

LISTING 2-26 [Data] and [CompletedOrders] filegroup-restore sequence

Click here to view code image

USE [master];
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Data', FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackup
OnlineStore_FULL.bak' WITH NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Data', FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackup
OnlineStore_DIFF.bak' WITH NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Data', FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackup
OnlineStore_LOG.bak' WITH NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Data', FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackup
OnlineStore_ORPHANEDLOG.bak' WITH NORECOVERY;
GO
RESTORE DATABASE [OnlineStore] WITH RECOVERY
GO

5. Restore and recover the final 1TB [Archive] filegroup, as shown in Listing 2-27.

LISTING 2-27 [Archive] filegroup-restore sequence

Click here to view code image

USE [master];
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Archive' FROM DISK = 'B:SQLBackupOnlineStore_FULL.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Archive' FROM DISK = 'B:SQLBackupOnlineStore_DIFF.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Archive' FROM DISK = 'B:SQLBackupOnlineStore_LOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'Archive' FROM DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore] WITH RECOVERY
GO

Alternatively, assume that only the E drive that contains the [CompletedOrders] has failed. The rest of the [OnlineSales] database is fine and accessible.

1. In this case recover and restore the [CompletedOrders] filegroup while the rest of the database is available, as shown in Listing 2-28. This is referred to as partial availability.

LISTING 2-28 [CompletedOrders] filegroup-restore sequence for a partially available database

Click here to view code image

USE [master];
GO
-- Take the [CompletedOrders] file offline
ALTER DATABASE
MODIFY FILE (name = OnlineStore_CompletedOrders' OFFLINE);
GO
-- Back up orphaned transaction log to minimize data loss
BACKUP LOG [OnlineStore] TO DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.bak' WITH
NO_TRUNCATE;
GO
--
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackupOnlineStore_FULL.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackupOnlineStore_DIFF.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackupOnlineStore_LOG.bak' WITH
NORECOVERY;
GO
RESTORE DATABASE [OnlineStore]
FILEGROUP = 'CompletedOrders' FROM DISK = 'B:SQLBackupOnlineStore_ORPHANEDLOG.
bak' WITH NORECOVERY;
GO
RESTORE DATABASE [OnlineStore] WITH RECOVERY
GO

As you can see from this section, performing a piecemeal restore is complicated and can involve substantial downtime. What happens if only a small number of pages in your database were corrupted? Ideally, you would not have to restore large data files, but could instead restore just the damaged pages. Fortunately, SQL Server supports the ability to restore just the damaged pages in a database and we will look at that next.

To obtain a list of the corrupted pages, you can query a number of sources, including:

The [msdb] system database’s [suspect_pages] table

The database engine’s ERRORLOG

The output of a DBCC command

If your database only has a small number of isolated pages that are damaged, you can take advantage of the page restore process. It is quicker to restore a small number of pages than to restore an entire database.

Ideally you should determine what kind of a page has been damaged. If the damaged pages are in a nonclustered index for example, it could be substantially easier to drop the index and recreate it, than to initiate a page recovery process.

Page restore only works for read/write files in databases that are using the full or bulk-logged recovery models. Page restore cannot be used for corruptions in the transaction log. Furthermore, page restore does not work for the following types of database pages:

Database Boot Page (Page 1:9)

File Boot Page (Page 0 of all data files)

Full-text catalog

Global Allocation Map (GAM)

Page Free Space (PFS)

Secondary Global Allocation Map (SGAM)

Use the following RESTORE statement to restore pages within the database:

Click here to view code image

--To Restore Specific Pages:
RESTORE DATABASE { database_name | @database_name_var }
   PAGE = 'file:page [ '...n ]'
 [ ' <file_or_filegroups> ] [ '...n ]
 [ FROM <backup_device> [ '...n ] ]
   WITH
       NORECOVERY
      [ ' <general_WITH_options> [ '...n ] ]
[;]

Page restore works with all editions of SQL Server. Only SQL Server Enterprise Edition supports online page restore. However, online page restore might not be possible in certain cases where critical pages have been damaged. With all other editions, excluding the Developer Edition, the database has to be offline.

Use the following process to perform a page restore:

1. Restore a full database with the PAGE restore option.

2. List all the Page IDs to be restored.

3. Restore any differential backups, if appropriate.

4. Restore the log chain after the full backup.

5. Perform a new log backup that includes the final LSN of the restored pages.

6. Restore this new log backup.

Listing 2-29 shows an example of a page restore process.

LISTING 2-29 Page restore

Click here to view code image

USE [msdb];
GO
-- Determine corrupted pages
SELECT database_id' file_id' page_id' event_type' error_count' last_update_date
FROM dbo.suspect_pages
WHERE database_id = DB_ID('WorldWideImporters');
GO
--  Restore 4 corrupt pages
USE [master];
GO
RESTORE DATABASE [WorldWideImporters] PAGE='1:300984' 1:300811' 1: 280113' 1:170916'
   FROM WideWorldImporters_Backup_Device
   WITH NORECOVERY;
GO
RESTORE LOG [WorldWideImporters]
   FROM WideWorldImporters_Log_Backup_Device
   WITH FILE = 1' NORECOVERY;
GO
RESTORE LOG [WorldWideImporters]
   FROM WideWorldImporters_Log_Backup_Device
   WITH FILE = 2' NORECOVERY;
   WITH NORECOVERY;
GO
BACKUP LOG [WorldWideImporters]
TO DISK='B:SQLBackupPageRecovery.bak';
GO
RESTORE LOG <database>
FROM DISK='B:SQLBackupPageRecovery.bak'
WITH RECOVERY;
GO

In the exam, if you get a question on performing page recovery, it will most likely test you on the specific order of steps that must be taken to restore pages and get them transactionally consistent with the rest of the database. So make sure you understand the order of the high-level steps discussed in this section.

With point-in-time recovery you need to know the precise time to which you want to restore the database. Importantly, any need to understand that any transactions that were committed after this point in time is lost.

That is why point-in-time recovery is not commonly used in the industry. If point-in-time recovery is required, it is more common to restore your database into a separate non-production database to the required point in time and then for the appropriate table(s) to be transferred to the production database.

The RESTORE statement supports point-in-time recovery. SQL Server supports recovery to these different types of point in times:

Date/Time A specific date and time.

Log Sequence Number (LSN) An internal numbering sequence used for each operation within the transaction log. This is used internally by the database engine and typically not used by database administrators.

Marked Transaction An explicitly named explicit transaction.

Typically, a point-in-time recovery is performed from a log backup, but it can be used with database backups as well. Remember, a database backup contains a portion of the transaction log. Use the STOPAT clause for point-in-time-recovery.

The STOPAT clause supports the following variations when restoring from a database backup:

Click here to view code image

<point_in_time_WITH_options—RESTORE_DATABASE>::=
 | {
   STOPAT = { 'datetime'| @datetime_var }
 | STOPATMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']
 | STOPBEFOREMARK = 'lsn:lsn_number'
                 [ AFTER 'datetime']

The STOPAT clause supports the following variations when restoring from a log backup:

Click here to view code image

<point_in_time_WITH_options—RESTORE_LOG>::=
 | {
   STOPAT = { 'datetime'| @datetime_var }
 | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
 | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime']
   }

The STOPATMARK clause rolls the transaction log forward to the mark and includes the marked transaction. The STOPBEFOREMARK clause rolls the transaction log forward to the mark, but excludes the marked transaction.

Listing 2-30 restores a database to a particular point in time using the BACKUP LOG statement.

LISTING 2-30 Point-in-time recovery with a date/time

Click here to view code image

RESTORE LOG WideWorldImporters
   FROM WideWorldImporters_Log_Backup_Device
   WITH FILE=6' RECOVERY'
STOPAT = 'Sep 17' 2016 2:30 AM' ;

Sometimes you do not know precisely when a particular event happened. In this case, you can query the transaction log using undocumented commands and potentially work out when the particular event occurred. The database engine writes all database modifications serially to the transaction log, and timestamps all records using the Log Sequence Number (LSN).

The undocumented [fn_dblog] function can be used to query the database’s transaction log. If you can work out what operation caused the incident, you determine the corresponding LSN. This LSN can be used to restore the transaction log to its point in time.

Consider the following experiment that performs the following high-level steps:

1. Inserts three records into a table.

2. Performs a database backup.

3. Inserts a record into a table.

4. Examines the transaction log’s operations and their LSNs (Figure 2-48 shows the partial output).

Record the last LSN

5. Simulates a mistake by updating all the records in the table.

6. Drops the database.

7. Restores the full backup.

8. Restores the log backup stopping at the LSN recorded at Step 4.

9. Confirms the database has been restored to before the mistake was simulated.

Listing 2-31 demonstrates how to restore to a particular LSN.

LISTING 2-31 Restoring a database to a LSN

Click here to view code image

-- Set up experiment
/* You might have to enable xp_cmdshell by running the following:
EXEC sp_configure 'show advanced'' 1;
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell'' 1;
RECONFIGURE;
GO
*/
USE [master];
EXEC xp_cmdshell 'md C:Exam764Ch2';
GO

-- Create database
CREATE DATABASE [RestoreToLSNExperimentDB]
ON  PRIMARY (
        NAME = N'RestoreToLSNExperiment_data''
        FILENAME = N'C:Exam764Ch2RestoreToLSNExperiment.mdf')
LOG ON (
        NAME = N'RestoreToLSNExperiment_log''
        FILENAME = N'C:Exam764Ch2RestoreToLSNExperiment.ldf')
GO
USE [RestoreToLSNExperimentDB]
GO
-- Create table
CREATE TABLE [MyTable] (Payload VARCHAR(1000))
GO
-- Step 1: Insert 3 records
INSERT [MyTable] VALUES ('Record 1')' ('Record 2')' ('Record 3')
GO
-- Step 2: Perform full backup
BACKUP DATABASE [RestoreToLSNExperimentDB]
TO DISK = 'C:Exam764Ch2RestoreToLSNExperimentDB_FULL.bak'
WITH INIT;
GO
-- Step 3: Insert 1 record
INSERT [MyTable] VALUES ('Record 4');
GO
SELECT * FROM [MyTable]
-- Step 4: Query the transaction log
SELECT * FROM fn_dblog(NULL'NULL);
-- Record the last LSN: 0x00000025:000000a9:0007
GO
-- Step 5: Accidentally update all 4 records (simulating a mistake)
UPDATE [MyTable] SET Payload = 'MISTAKE'
GO
SELECT * FROM [MyTable]
SELECT * FROM fn_dblog(NULL'NULL);
-- Perform log backup
BACKUP LOG [RestoreToLSNExperimentDB]
TO DISK = 'C:Exam764Ch2RestoreToLSN_LOG.bak'
WITH INIT;
GO
-- Step 6: Drop database
USE master;
DROP DATABASE [RestoreToLSNExperimentDB];
-- Step 7: Restore full backup
RESTORE DATABASE [RestoreToLSNExperimentDB]
FROM DISK = 'C:Exam764Ch2RestoreToLSNExperimentDB_FULL.bak'
WITH NORECOVERY;
-- Step 8: Restore lob backup at LSN recorded above
RESTORE LOG [RestoreToLSNExperimentDB]
FROM DISK = 'C:Exam764Ch2RestoreToLSNExperimentDB_LOG.bak'
WITH RECOVERY' STOPATMARK = 'lsn:0x00000025:000000a9:0007' ;
-- Step 9: Confirm restore doesn't include "mistake"
USE [RestoreToLSNExperimentDB];
SELECT * FROM [MyTable];
-- Cleanup experiment
/*
USE master;
DROP DATABASE [RestoreToLSNExperimentDB];
GO
EXEC xp_cmdshell 'rd /s /q C:Exam764Ch2;
GO
EXEC sp_configure 'xp_cmdshell'' 0;
RECONFIGURE;
GO
EXEC sp_configure 'show advanced'' 0;
RECONFIGURE;
GO
*/

Using the date/time, or LSN as a recovery point can be imprecise because it requires you to know exactly what happened when. If you want to potentially roll back a significant modification, such as an end-of-day process, you might want to take advantage of marked transactions.

A marked transaction is an explicit marker that you can record in the transaction log to effectively create a recovery point.

Marked transactions can also be used to create a consistent recovery point across multiple databases. This is a common requirement for multi-database solutions such as Microsoft BizTalk, Microsoft SharePoint, or Microsoft Team Foundation Server (TFS).

Listing 2-32 shows you how to create a marked transaction.

LISTING 2-32 Create a transaction log mark

Click here to view code image

USE [WideWorldImporters];
GO
BEGIN TRANSACTION PriceIncrease
    WITH MARK 'PriceIncrease';
UPDATE [Warehouse].[StockItems]
    SET [RecommendedRetailPrice] = [RecommendedRetailPrice] * 1.25;
COMMIT TRANSACTION PriceIncrease;
GO

You can query the [msdb] database’s [logmarkhistory] to see all marked transactions, as show in Listing 2-33.

LISTING 2-33 Query all marked transactions

Click here to view code image

SELECT [database_name]' [mark_name]' [description]' [user_name]' [lsn]' [mark_time]
FROM [msdb].[dbo].[logmarkhistory];

Listing 2-34 shows the RESTORE LOG statement that restores the database to the marked transaction created in Listing 2-33.

LISTING 2-34 Restore database to marked transaction

Click here to view code image

USE [WorldWideImporters];
GO
RESTORE LOG [WideWorldImporters]
FROM DISK = 'B:SQLBackupWideWorldImporters_LOG.bak'
WITH RECOVERY'
STOPATMARK = 'PriceIncrease';
GO

For the exam make sure you understand the use cases for when you would create and use the different marks in the transaction log. Make sure you also understand the difference between the STOPATMARK and STOPBEFOREMARK options and when to use them. So read the exam question carefully to know which one potentially represents the correct answer.

Separating your database into a number of files or filegroups allows you to reduce your RTO because there is less data to restore in the case of a single file or filegroup being damaged.

If you are restoring a read/write filegroup, perform the following steps:

1. Perform a tail-log backup, if necessary.

2. Restore the filegroup.

3. Restore any different backups, if necessary.

4. Restore the log-chain to bring the filegroup into sync with the rest of the files in the database.

5. Stop at a point in time if necessary.

6. Recover the filegroup and bring it online.

If you are restoring a read-only filegroup, it may be unnecessary to apply the log chain. If the file was marked as read-only after the backup, additional restore operations need to be applied.

All editions of SQL Server support offline file restore. With offline file restore the entire database is unavailable for the duration of the file restore operation.

SQL Server Enterprise Edition supports online file restore. With online file restore only the restoring file group is unavailable until it is restored and recovered.

Use the following syntax to restore a filegroup:

Click here to view code image

--To Restore Specific Files or Filegroups
RESTORE DATABASE { database_name | @database_name_var }
   <file_or_filegroup> [ '...n ]
 [ FROM <backup_device> [ '...n ] ]
   WITH
   {
      [ RECOVERY | NORECOVERY ]
      [ ' <general_WITH_options> [ '...n ] ]
   } [ '...n ]
[;]

Listing 2-35 shows an example of a file restore operation.

LISTING 2-35 Filegroup restore example

Click here to view code image

USE master;
GO
-- Restore filegroup
RESTORE DATABASE [WordWideImporters]
   FILEGROUP = 'WordWideImporters_OrderHistory''
   FROM WordWideImporters_Backup_OrderHistory_Device
   WITH NORECOVERY'
   REPLACE;
GO
-- Restore first log backup
RESTORE LOG [WordWideImporters]
   FROM WordWideImporters_Log_Device
   WITH FILE = 1' NORECOVERY;
GO
-- Restore second log backup and recover database
RESTORE LOG [WordWideImporters]
   FROM WordWideImporters_Log_Device
   WITH FILE = 2' RECOVERY;
GO

You need to test your restore strategy for the following reasons:

To test that your backups are restorable.

To make sure that all stakeholders understand the restore process.

To time the length that your restore process takes.

The backup and restore operations’ duration are dependent on the following:

The size of your database

The size of your backup set

The speed of your database’s storage subsystem

The speed of your backup destination’s storage subsystem

The speed of your network

The speed and number of processor cores being used

How compressible the data being backed up is

A restore operation is not necessarily symmetrical with a backup process. In other words, if a full backup takes 200 minutes, it might take 300 minutes to restore that same backup. Do not rely on the backup duration to predict your restore duration.

In this skill we will examine how you can identify database corruption before repairing a corrupt database. Microsoft always recommends restoring a database from a last known good backup over database repair techniques. That is why you should ensure that your backups are completing successfully and can be restored. Performing period database consistency checks helps ensure backups can be restored and this skill will show you how to perform these checks.

The following DBCC commands perform consistency checks on a database:

DBCC CHECKALLOC Checks the consistency of disk space allocation structures with a database.

DBCC CHECKCATALOG Checks the consistency of the system tables with a database.

DBCC CHECKDB Checks the logical and physical integrity of all the objects of a database.

DBCC CHECKFILEGROUP Checks the allocation and structural integrity of all tables and indexed views in a filegroup.

DBCC CHECKTABLE Checks the allocation and structural integrity a table.

When you perform any of these DBCC commands, the database engine automatically creates a transactionally consistent internal database snapshot. DBCC operates on this database snapshot. The snapshot minimizes false errors and allows DBCC to run as an online operation that prevents blocking and concurrency problems.

DBCC is not able to create an internal database snapshot and uses table locks in certain situations, including when the database is located on:

A disk formatted using the FAT file system

A disk filesystem that does not support named streams, such as ReFS

A disk filesystem that does not support alternate streams, such as ReFS

Use the DBCC CHECKDB command to implement your database consistency checks. Use the other DBCC CHECK commands when you want to fine-tune your database consistency check, such as in the case when you need to perform them against a VLDB.

The DBCC CHECKDB command has the following syntax.

Click here to view code image

DBCC CHECKDB
    [ ( database_name | database_id | 0
        [ ' NOINDEX
        | ' { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
    ) ]
    [ WITH
        {
            [ ALL_ERRORMSGS ]
            [ ' EXTENDED_LOGICAL_CHECKS ]
            [ ' NO_INFOMSGS ]
            [ ' TABLOCK ]
            [ ' ESTIMATEONLY ]
            [ ' { PHYSICAL_ONLY | DATA_PURITY } ]
            [ ' MAXDOP  = number_of_processors ]
        }
    ]
]

The DBCC CHECKDB operation performs the following operations:

DBCC CHECKALLOC

DBCC CHECKTABLE on every table within a database

DBCC CHECKCATALOG

Performs a consistency check on all indexed views within a database

Performs a consistency check on FILESTREAM data within a database

Performs a consistency check on the Service Broker data within a database

DBCC CHECKDB supports a number of different options:

DATA_PURITY This option specifies that column values should be checked to ensure that they are valid for the domain and not out of range.

ESTIMATEONLY This option causes DBCC not to perform any checks, but to estimate how much [tempdb] space is consumed instead.

NO_INFOMSGS This option specifies that no information messages are reported. DBCC only reports errors.

NOINDEX This option specifies that comprehensive checks should not be performed on nonclustered indexes.

PHYSICAL_ONLY This option limits DBCC to checking the allocation consistency, physical structure of the pages, and record headers. It detects checksum and torn pages errors, which are typically indicative of a hardware problem with your memory or storage subsystem.

TABLOCK This option specifies that the internal database snapshot should not be taken and that table lock be used instead. This can improve performance in certain cases. Use the TABLOCK when you know there is no user activity. The TABLOCK option causes the DBCC CHECKCATALOG and Service Broker checks not to run.

MAXDOP This option controls the degree of parallelism that the DBCC operation uses. The DBCC operation typically dynamically adjusts the degree of parallelism during its execution.

A complete DBCC CHECKDB operation can take a substantial period of time on a large database in later versions of SQL Server. Microsoft has introduced more comprehensive logical checks; internal database structures are more complex and there is more functionality in the database engine.

Use the following techniques to reduce the overall time that DBCC takes:

Use the NOINDEX option to direct the database engine to skip checking nonclustered indexes for user tables. This can substantially reduce the time taken in data warehouse use cases, where there might be a substantial number of nonclustered indexes. If a nonclustered index becomes corrupt you can drop and re-create it.

Use the PHYSICAL_ONLY option to direct the database engine to skip all logical checks. This can substantially reduce the take that DBCC CHECKDB takes. It is the goal of Microsoft to make DBCC CHECKDB WITH PHYSICAL ONLY to run as quickly as a BACKUP to a nul device operation. In this instance the duration of the DBCC operation is totally dependent on your storage subsystem’s performance.

Take advantage of DBCC CHECKFILEGROUP to check a subset of the database’s filegroups. Rotate these operations between all the filegroups in your database to ensure that there are no database consistency problems. Consider not running consistency checks against read-only filegroups that are consistent.

Take advantage of DBCC CHECKTABLE and rotate the consistency checks across all the tables in the database across successive nights, similarly to DBCC CHECKFILEGROUP.

In the exam you might get asked a question on how to reduce the time taken by DBCC CHECKDB to complete for a VLDB in a given scenario. Use the guidance in the above bullet points to help you in the exam.

Be aware that DBCC is not linear in its execution. If, at any point in time the DBCC operation detects a potential corruption, it triggers extra checks that perform a deeper analysis and consistency check of the database.

Use the T-SQL query in Listing 2-36 to show the progress of a running DBCC operation.

LISTING 2-36 DBCC operation progress

Click here to view code image

SELECT session_id' db_name(database_id) as database_name'
             start_time' command' percent_complete' estimated_completion_time
FROM sys.dm_exec_requests
WHERE command LIKE 'dbcc%';

Listing 2-37 shows an example of a database consistency check that includes the extended logical checks, uses a table lock, and does not generate information messages:

LISTING 2-37 DBCC consistency check

Click here to view code image

DBCC CHECKDB ([AdventureWorksDW])
WITH EXTENDED_LOGICAL_CHECKS' TABLOCK' NO_INFOMSGS;
GO

Use SQL Server Agent or maintenance plans to schedule period database consistency checks. Keep track of how long the consistency checks take and whether they are increasing in duration as you might have to optimize their execution time using some of the techniques discussed in this section.

Although you might have schedule database integrity checks you might still experience a database corruption. It is important to identify and diagnose the database corruption before attempting recovery. We will look at how you do these next.

The database engine automatically logs database consistency errors in certain places:

The database engine’s ERRORLOG captures any DBCC CHECK operations. Additional information is written in the event of a database corruption.

The Windows Application Log should also capture the same DBCC error.

The [msdb] system database also tracks suspect pages in the [suspect_pages] table. The database engine records any suspect pages encountered during operations such as:

A query reading a page.

A DBCC CHECKDB operation.

A backup operation.

The database engine creates a dump file in the LOG directory whenever a DBCC CHECKDB detects a consistency problem. The dump file uses the SQLDUMPnnnn.txt naming convention. The dump file contains the results of the DBCC CHECKDB operation and additional diagnostic output. This dump file is automatically sent to Microsoft via the Feature Usage data collection and Error Reporting feature of SQL Server.

You should periodically check these repositories so that you can identify any database corruption as soon as possible. This can be automated through SQL Server Agent jobs or external programs.

You should also schedule SQL Server Agent alerts on the following error messages:

823 This error message is generated whenever the Windows environment issues a cyclic redundancy check. It represents a disk error. It typically indicates that there is a problem with the storage subsystem, hardware, or driver that is in the I/O path.

824 This error message is generated when a logical consistency check fails after reading or writing a database page. It also typically indicates that there is a problem with the storage subsystem, hardware, or driver that is in the I/O path.

825 This error message is called the Read Retry error and is generated whenever the database engine has had to retry a read or write operation. The database engine retries the I/O operation up to four times before failing it.

832 This error message is generated when a page in memory has been corrupted. In other words, the checksum has changed since it was read from disk into memory. It is a rare error. It possibly indicates bad memory.

Table 2-3 shows the different error messages for these four different 8xx errors:

Once you have identified a database corruption incident you need to take corrective action. We will examine the options you have with recovering from database corruption next.

Figure 2-49 shows an example of a database corruption. Notice how the DBCC CHECKDB recommends the minimum repair commands that are required to fix the errors encountered.

The first step in dealing with a database corruption incident is to obtain as much information as possible about what has been corrupted, before planning a course of action.

Use the OBJECT_NAME system function to identify the database object that has been corrupted. An Index ID value of 0 represents a heap. An Index ID value of 1 represents a clustered index. An index value greater than 1 represents a nonclustered index.

If the database corruption is contained to a nonclustered index, you may choose to simply drop and rebuild the nonclustered index.

Otherwise, Microsoft recommends that you should restore the database from a backup instead of performing a repair with one of the repair options. The repair options do not consider any constraints that may exist between the tables when performing their repair operations.

In some circumstances, and especially for databases that have been upgrade from an earlier version of SQL Server, the values stored in a column might not be valid for that column. Execute the DBCC CHECKDB with the DATA_PURITY option on all the databases that have been upgrade from an earlier version of SQL Server. This only ever needs to be performed once per database. Any detected errors have to be updated manually because the database engine cannot know what the valid value should be.

Most DBCC repair operations are fully logged in the transaction. Consequently, it is recommended that you perform repairs in a transaction by using the BEGIN TRAN statement. If a repair is completed successfully, you can commit this repair transaction.

Consider backing up your corrupt database at the database or file level before attempting any repair operations. You can use the backup to attempt a repair on another SQL Server instance.

Set the corrupted database to single user mode before performing any repair operations. You don’t want users accessing a database that has corrupt data. Nor do you want them interfering with your repair attempts.

The REPAIR_REBUILD repair operation is generally considered safe to use. The REPAIR_REBUILD repair process guarantees that there is no possibility of data loss. The REPAIR_REBUILD repair typically involves small repairs, such as repairing missing rows in a nonclustered index, or more time consuming operation such as rebuilding an entire nonclustered index on a table.

Listing 2-38 shows an example of how to set a database to single user mode.

LISTING 2-38 Repairing a database using the REPAIR_REBUILD operation

Click here to view code image

USE [master];
-- Change database to single user mode
ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- Perform safe repair
DBCC CHECKDB ([WideWorldImporters]) WITH REPAIR_REBUILD;
GO

The DBCC command can fail during its operation, in which case it generates an error message. The state value of the error message helps you to troubleshoot what went wrong. Use Table 2-4 to troubleshoot any DBCC execution errors.

The goal of the REPAIR_ALLOW_DATA_LOSS option is to get the database into a transactionally consistent state. A REPAIR_ALLOW_DATA_LOSS repair most likely results in a loss of data. Compare the amount of data lost by using this emergency repair method versus how much data would be lost if you restored the database through your backups, which might be out of date, corrupt, or lost.

The emergency repair process cannot be rolled back. It is a one-way operation.

The REPAIR_ALLOW_DATA_LOSS repair operation differs in that it can use database pages that have been marked as inaccessible because of I/O or checksum errors. It performs the following operations:

Forces recovery to run on the transaction log to salvage as much information as possible.

Rebuilds the transaction log if it is corrupt. At this stage, you have lost your full ACID guarantee.

Runs DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option on the database.

If the DBCC CHECDB operation succeeds, set the database state to online.

The recovered database is now in a consistent state, but there is no guarantee of any data consistency.

If the emergency repair process fails, the database cannot be repaired by DBCC.

Listing 2-39 shows an example of an emergency repair process performed on a database.

LISTING 2-39 Repairing a database using the REPAIR_ALLOW_DATA_LOSS operation

Click here to view code image

USE [master];
GO
ALTER DATABASE [WideWorldImporters] SET EMERGENCY;
GO
ALTER DATABASE [WideWorldImporters] SET SINGLE_USER;
GO
DBCC CHECKDB ('WideWorldImporters'' REPAIR_ALLOW_DATA_LOSS)
WITH NO_INFOMSGS;
GO

You work as a database administrator for World Wide Importers. You need to design a backup strategy for their mission critical 4TB database that is used as an online store. The SQL Server instance is using a SAN that can read/write 1,000MB/sec.

The database structure is as follows:

The primary data file is 1GB in size and does not contain any data.

The [AuditHistory] table is 1.5TB in size and located on its own [FG_Audit] read-only filegroup.

The [OrderHistory] table is 1.5TB in size and located on its own [FG_OrderHistory] read-only filegroup.

The default [FG_Data] filegroup contains the rest of the tables used by the online store.

The database has the following characteristics:

The [AuditHistory] and [OrderHistory] tables are updated at the end of the month when a process runs that moves the oldest month’s worth of audit, and orders data into these tables.

The online store website predominantly inserts new records.

Page verify = NONE.

You are responsible for designing their backup strategy. Management has given you the following requirements:

The RPO is 15 minutes for the tables used by the online website.

The RTO is one hour.

The backup storage usage should be minimized.

The backup set needs to be retained for 18 months.

An important end-of-week business process runs on Saturday at 17:00 that takes five hours to complete. This process initially commits a marked transaction. It then updates a substantial amount of data into the database. A database snapshot is performed before the process starts.

1. Management wants you to create a backup strategy that meets their requirements. What backup strategy should you use?

A. Use the following backup strategy:

Perform a full backup every Sunday.

Perform differential backups nightly.

Perform log backups every hour.

B. Use the following backup strategy:

Perform a file backup of the primary filegroup daily.

Perform a file backup of the [FG_Data] filegroup daily.

Perform file backup of the [AuditHistory] and [FG_OrderHistory] at the end of the month after they are updated.

Perform log backups every 15 minutes.

C. Use the following backup strategy:

Perform a filegroup backup of the primary filegroup daily.

Perform a filegroup backup of the [FG_Data] filegroup daily.

Perform filegroup backup of the [AuditHistory] and [FG_OrderHistory] filegroups at the end of the month after they are updated.

Perform differential backups every 15 minutes.

D. Use the following backup strategy:

Perform a file backup of the primary filegroup daily.

Perform a file backup of the [FG_Data] filegroup daily.

Perform file backup of the [AuditHistory] and [FG_OrderHistory] filegroups daily.

Perform log backups every 15 minutes.

2. Management discovers that Saturday’s process crashed and wants you to recovery the database to 17:00, before the process ran. Upon further investigation, you discover that the process crashed because the disk that stored the [FG_Data] filegroup failed at 17:15. Management wants you to restore the database as soon as possible. What recovery strategy should you use?

A. Revert the database using the database snapshot

B. Use the DBCC REPAIR_REBUILD command to repair the damaged portions of the database.

C. Initiate a restore process from your backup set. Use the STOPAT restore option to stop at 17:00 on Saturday

D. Discover the damaged pages. Perform a page recovery process

3. A scheduled DBCC CHECKDB on the database generates the following error:

Click here to view code image

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 6341281, index ID 5, partition ID 54039787910657209, alloc
unit ID 92740571204375891 (type In-row data). Page (1:3126) is missing a reference
from previous page (1:3972). Possible chain linkage problem.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 6341281, index ID 5, partition ID 54039787910657209, alloc
unit ID 92740571204375891 (type In-row data), page (1:3256). Test (IS_OFF (BUF_
IOERR, pBUF->bstat)) failed. Values are 25611328 and -69.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 6341281, index ID 5, partition ID 54039787910657209, alloc
unit ID 92740571204375891 (type In-row data). Page (1:3972) was not seen in the
scan although its parent (1:16777) and previous (1:5612) refer to it. Check any
previous errors.

Management wants you to repair the database corruption as quickly as possible. What database corruption recovery technique should you perform?

A. Perform an emergency repair after setting the database to single user mode.

B. Perform a tail-log backup. Restore the last full, differential backups. Restore the log chain including the tail-log.

C. Restore the last full, differential backups. Restore the log chain and stop at the time of when the DBCC CHECKDB ran.

D. Rebuild the damaged nonclustered index.

1. Correct answer: B

A. Incorrect: You cannot meet your RPO with hourly log backups.

B. Correct: The RTO requirements are met with log backups every 15 minutes. Backup storage usage is minimized with month backups of the [AuditHistory] and [FG_OrderHistory] filegroups.

C. Incorrect: You cannot meet your RTO by performing differential backups every 15 minutes.

D. Incorrect: You cannot meet the requirement to minimize the backup storage usage by backing up the [AuditHistory] and [FG_OrderHistory] filegroups daily.

2. Correct answer: C

A. Incorrect: You cannot revert a database where the underlying files have been lost.

B. Incorrect: DBCC cannot repair database files that have been lost.

C. Correct: A restore process with the STOPAT option recovers the database to Saturday at 17:00.

D. Incorrect: Page recovery cannot be performed because the database file has been lost. Furthermore, the database does not have checksums on the pages so it cannot detect corruption.

3. Correct answer: D

A. Incorrect: An emergency repair is not required. It is considered a last resort, plus it will take the database offline.

B. Incorrect: Restoring a database does not recover the database in the shortest period of time.

C. Incorrect: Restoring a database does not recover the database in the shortest period of time.

D. Correct: An index ID of 5 indicates a nonclustered index was damaged.

The simple recovery model automatically truncates the database’s transaction log when the transaction log records are no longer required for recovery purposes.

The bulk-logged recovery model optimizes bulk modification in the database by only recording the extents that had been modified by the bulk operation.

A full backup operation backup backs up the entire database.

A differential backup operation backs up the extents that have been modified since the last full backup.

A log backup operation backs up the transaction log, and then truncates it.

A full backup is required before you can perform log backups.

When switching from the full recovery model to the bulk-logged recovery model, and then back to the full recovery model, perform a log (or differential) backup to maintain your log chain.

The NORECOVERY restore option allows further backups to be restored and keeps the database unavailable.

The RECOVERY performs recovery on the database, rolling back all uncommitted transactions and makes the database available.

Use the DBCC command to periodically check a database’s consistency checks.

Microsoft recommends restoring backups as a primary means of recovering from a database corruption.

The DBCC repair operation with the REPAIR_REBUILD option does not lose any data within the database.

The DBCC repair operation with the REPAIR_ALLOW_DATA_LOSS option results in data loss.

Use the emergency repair mode as an absolutely last resort to recover from database corruption.