Overview

Introduction

The Administering Databases module 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 using T-SQL and SSMS
  • Grant database permissions and roles using T-SQL and SSMS
  • Create a full, differential, and transaction log database backups using T-SQL and SSMS
  • Restore a database using T-SQL and SSMS

Exam Objectives

The following exam objective is covered in this lab:

  • 5.1 Understand database security concepts,
  • 5.2 Understand database backups and restore

Lab Duration

It will take approximately 20 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, select on the AdventureWorks2016CTP3 database objects accordingly to the AdventureWorks2016CTP3 user based on his role. Note that the database user is the identity of the login while it connects to a database.

SQL Server provides you with several database-level roles that govern a group of security principals. You can assign membership of the required roles to the database user appropriately to manage the permission in your database. For example, to enable a user in the AdventureWorks2016CTP3 database to run any DDL commands, you can assign the respective user the membership to the role db_ddladmin. You can then grant appropriate permissions on the database to the user. For example, you can grant the user the CREATE TABLE permission on the AdventureWorks2016CTP3 database.

Learning Outcomes

After completing this lab, you will be able to:

  • Create logins and users for a database using T-SQL and SSMS
  • Grant database permissions and roles using T-SQL and SSMS
  • Create a full, differential, and transaction log database backups using T-SQL and SSMS
  • Restore a database using T-SQL and SSMS

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 in this backup including a part of the transaction log. This back up 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 take the backup of the transaction log only after creating at least one full database backup.

You can restore a database from backups to revert the database to the point in time captured during the backup. SQL server provides various restore scenarios, which is the process of restoring data from one or more backups and recovering the database. Also, the database restore operations depends on the type of recovery model for a given restore scenario. For example, the complete database restore under simple recovery model restores the whole database and the specified database is offline for the period of the restore.

In this example, you will create a full, differential, and transaction log backups of the specified database. You will then restore the specified database backup. You will perform these tasks using T-SQL and SSMS alternatively.

Learning Outcomes

After completing this lab, you will be able to:

  • Create a full, differential, and transaction log database backups using T-SQL and SSMS
  • Restore a database using T-SQL and SSMS

Comprehensive Learning

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