Overview

Introduction

The Administering Databases Practice Lab provides you with the instruction and server hardware to develop your hands-on skills in the defined topics. This module includes the following exercises:

Learning Outcomes

In this module, you will complete the following exercises:

  • Exercise 1. Securing Databases
  • Exercise 2. Backup and Restore a Database

After completing this lab, you will be able to:

  • Create logins and users for a database
  • Assign roles to users and granting permissions
  • Perform backup and restore using T-SQL
  • Perform backup and restore using SSMS

Exam Objectives

The following exam objectives are covered in this lab:

  • 5.3 Summarize methods used to interface with databases.
  • 6.7 Explain business continuity concepts.

Lab Duration

It will take approximately 30 minutes to complete this lab.

Exercise 1 - Securing Databases

You can secure your SQL Server databases by creating a SQL Server login and mapping the login to a database user, thereby permitting them to connect to a specific database on the instance of SQL Server. You can then grant or deny permissions to the user inside the database accordingly. For example, you can create a login named ‘SQLLogin’ and map the ‘SQLLogin’ to a user in the AdventureWorks2016CTP3 database. This authorizes the login to use the AdventureWorks2016CTP3 on the instance of SQL Server. You can then grant or restrict permissions such as read, write, and select on the AdventureWorks2016CTP3 database objects to the AdventureWorks2016CTP3 user.

Learning Outcomes

After completing this exercise, you will be able to:

  • Create logins and users for a database
  • Assigning roles to users and granting permissions

Exercise 2 - Backup and Restore a Database

You need to back up the critical data stored in your SQL Server databases regularly to minimize the risk of devastating loss. Some of the commonly used backup types in the SQL Server include:

Full Backup: The whole database is backed up, including a part of the transaction log. This backup represents the database at the time of completion of the backup.

Differential Backup: Only the data that has been modified since the last full backup alone is captured in this backup type.

Log Backup: This backup type backs up the transaction log of the specified database. However, you can only take the backup of the transaction log after creating at least one full database backup.

Learning Outcomes

After completing this exercise, you will be able to:

  • Perform backup and restore using T-SQL
  • Perform backup and restore using SSMS

Comprehensive Learning

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