SQL Server Administration

This SQL Server administration tutorial provides you with the knowledge and skills you need to administer SQL Server database servers effectively.

This tutorial is for database administrators and developers, who want to administer SQL Server successfully. To follow the tutorial, you should have some fundamental database concepts.

Section 1. Basics

System databases – introduce you to system databases and their purposes.

Section 2. Backup & Restore

Recovery model – learn about SQL Server recovery models including simple, full, and bulk-logged.
Backup types – introduce to you three backup types including full backup, differential backup, and transaction log backup.
Full backup – show you how to create a full backup of a database.
Differential Backup – learn about differential backups and how to create and restore a differential backup.
Transaction Log Backup – explain the transaction log backup and how to back up and restore a database from transaction log backups.

Section 3. Managing Logins, Users, and Permissions

Create Login – create a login account to log in to the SQL Server.
Create User – create a new user in the current database.
Grant permissions – grant permissions on a securable to a principal.
Revoke permissions – revoke previously granted permissions on a securable from a principal.
Alter Login – show you how to modify the properties of a login.
Alter User – rename a user, map the user with a new login account, or change the default schema of a user to another.
Drop Login – delete a login account from the SQL Server.
Drop User – remove a user from the current database.

Section 4. Managing Roles

Roles – learn about database roles.
CREATE ROLE – show you how to add a new role to the current database.
ALTER ROLE – learn how to rename a role, add a member to a role, and remove a member from an existing role.
DROP ROLE – walk you through the steps of removing a role from the current database.

Section 5. Database Mail

Database Mail – configure Database Mail and send email messages to users using SQL Server Database Engine.

Section 6. Blocking & Deadlock

Blocking – understand the blocking in SQL Server.
Deadlock – learn about the deadlock and how to simulate a deadlock in SQL Server.

Section 7. Table Partitioning

Create a partitioned table – learn about table partitioning and how to create a partitioned table.
Partitioning an existing table – show you how to partition an existing table.

Section 8. Database snapshots & contained databases

Database Snapshot – learn about the database snapshots and how to create a database snapshot for the reporting or testing purposes
Contained Databases – introduce you to contained databases and how to create a contained database.

Section 9. Import / Export Data

BCP – learn how to use the SQL Server BCP utility to bulk copy data between an SQL Server instance and a file.
BULK INSERT – show you how to use the BULK INSERT statement to load data from a file into a table.

Section 10. Database Encryption

SQL Server TDE – show you how to encrypt a database using the transparent data encryption technique.