Working with Restore Options

Practice Labs Module
Time
1 hour 2 minutes
Difficulty
Intermediate

The "Working with Restore Options" module provides you with the instructions and devices to develop your hands-on skills in the following topics: Managing point-in-time recovery, Working with page restore, Automating SQL Server restores.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Overview

Introduction

The Working with Restore Options module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Managing point-in-time recovery
  • Working with page restore
  • Automating SQL Server restores

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

Exam Objectives

Three exam objectives are covered in this lab.

  • Page restore using SSMS/Transact SQL
  • Point-in-time recovery using SSMS/Transact SQL
  • Automate SQL Server restores to refresh test environments

Exercise 1 - Managing Point-in-Time Recovery

Database recovery models are very important in SQL Server. The recovery model is considered as a database property. This property helps to identify backup and restore activities of transaction logs. You can perform database backup and restore activities only after considering the appropriate database recovery model. There are three types of database recovery models. These are as follows:

  • Full: In this recovery model, you need to take log backups. There is no data loss in this model. - - You can restore the database to a specific point in time. You can implement full database recovery model for databases in a production environment.
  • Bulk-logged: In this recovery model, you need to take log backups. However, this model does not support point-in-time recovery.
  • Simple: In this recovery model, you need not take log backups. It does not support point-in-time recovery. The changes made to the database after the last backup is not protected. Therefore, in the event of a failure, chances of data loss are to the maximum. You can have simple recovery model for development or test databases.

In this exercise, you will perform the following tasks:

  • Take a backup of AdventureworksDW2016CTP3 database using Transact-SQL.
  • Restore full database backup of AdventureworksDW2016CTP3 using SQL Server Management Studio.
  • Restore transaction log backup of AdventureworksDW2016CTP3 database using SQL Server Management Studio.
  • Restore complete database backup of AdventureworksDW2016CTP3 using Transact-SQL

Exercise 2 - Working with Page Restore

You can restore damaged pages without restoring the entire database. This will save organizational time and effort. Restoring few damaged pages can be faster than complete database restore.

In this exercise, you will restore database pages using SQL Server Management studio and Transact-SQL.

Exercise 3 - Automating SQL Server Restores

It is recommended to refresh the test environment with the latest production environment data. You can create an automatic schedule to conduct regular database restores. You can create a job in SQL Server Agent and attach the job to database restore schedule.

In this exercise, you will create a SQL Server Agent job to restore the database automatically.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.