Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

SQL Server 2019 : Administration SQL Server Part 1

Name: SQL Server 2019 : Administration SQL Server Part 1
Rating: 4.3 (38 reviews)

In this 4 hour course, discover two very important modules : BACKUPS and RESTORES and corruption on SQL Server 2019.

Last updated 11/2022

English

English [Auto],

What you'll learn

What are the common causes of corruption?
How to check the consistency of the data?
How to repair in case of corruption?
What to do when corrupting logs?
Control corruption on your backup with BACKUP WITH CHECKSUM
Explanation and demonstration on the CHECKDB
Discover TORN PAGE DETECTION and PAGE CHECKSUM
Discover DBCC IND and DBCC PAGE
Learn how to repair a non-clustered index
Recreating a log file by attaching a DB
Repairing a database using the HACK ATTACH mode
And discover all the techniques of RESTORE in case of corruption
How to make very simply a BACKUP and a RESTORE
Discuss the BACKUP LOGS and DIFFERENTIAL BACKUP, and implement them in your BACKUP strategy.
Understand the interest of the WITH INIT in a BACKUP
we will see the different full recovery model on a database (FULL,SIMPLE and BULKED LOGGUED)
The performance when doing a BACKUP on several files.
Avoiding corruption on BACKUPs with the CHECKSUM
Understand the importance of BACKUP WITH COPY ONLY to avoid breaking the BACKUP chain during the day.
To discover that you can make a RESTORE of a page, or from a LSN, to avoid losing hours of RESTORE, in case of server crash.
Recover unsaved logs, in case of server crash, with the power of the TAIL LOG BACKUP.
We will discuss the BACKUP WITH COMPRESSION,to save disk space, which can be significant
and the simultaneous copy of BACKUP with the WITH MIRROR option.
We will also see how to restore system databases (Master, MSDB, model, TEMPDB).
The RESTORE WITH STANDBY, which allows to examine the data between each RESTORE.
Understand what is a CHECKPOINT on your SQL Server engine
And to finish, we will see how to make a RESTORE from a SNAPSHOT, see the necessary rights, to make BACKUP, how to consult BACKUP metadata.

Course content

12 sections • 49 lectures • 4h 31m total length

Small explanation on the subtitles0:47
Introduction2:10
Understand how to back up a database and restore the production database to a development server after a crash, using backups to recover.

Démo : BACKUP and RESTORE LOGS4:27
In this vidéo, you will learn how to backup ans restore a database in SQL Server.
The Syntax is : BACKUP LOGS and RESTORE LOGS
More détails in this vidéo, You will see It's really simple to do it
And have fun :)
Démo : Differential BACKUP and RESTORE6:42
Learn how differential backups save time by restoring from a full backup plus subsequent differentials, keeping production online and speeding recovery in SQL Server 2019 administration.
Démo : BACKUP WITH INIT5:38
Demonstrates backup with init, creating labeled backups, and restoring from multiple backups by selecting the correct backup and choosing replace existing database while monitoring progress.
Démo : BACKUP WITH COPY ONLY8:31
Demonstrates how copy-only backups preserve the backup chain while performing full and differential backups, showing restore implications and the impact of not using copy-only.
Démo: RESTORE moving the directory files3:52

Démo: The different database recovery model in SQL Server8:46
In this vidéo, we will see the different database recovery model :
- FULL, SIMPLE and BULKED LOGGED !
What are their difference and how to change it ?
And have fun :)
Démo: What is the TAIL LOG Backup ?6:07
Démo : BACKUP WITH CHECKSUM4:27
Demonstrates backup with checksum in SQL Server 2019 administration part 1, showing how checksums detect page corruption and verify backups for safer recovery.
Démo : How to RESTORE just a single page !4:21
Démo: RESTORE WITH STOPAT5:53
Demonstrates how to perform point in time restore using stop at to recover a database from backups at a specific time, preserving production and guiding data recovery steps.
Démo : RESTORE WITH STANDBY6:10
Explore restore with standby in SQL Server 2019, using standby mode for point-in-time recovery and tracking restoration progress to recover data to a chosen time.
Démo : RESTORE from a SNAPSHOT2:15
Restore a database from a snapshot and show how to use snapshot syntax while verifying a successful restoration.
Démo : How to RESTORE from a LSN ? (log sequential number)4:24
Learn how to restore from a log sequence number (lsn) in SQL Server 2019 administration, including backups, simulating crashes, and recovering a dropped database.
Démo : Does a COMPRESSION do a CHECKSUM ?5:35
This demo investigates whether compression uses a checksum in SQL Server by comparing backups with and without checksum, and analyzing page corruption detection and backup verification.
Démo: Understand what is a CHECKPOINT on your SQL Server engine11:37
Démo : Consult BACKUP and RESTORE metadata6:17
Démo : Learn how to simply clone your database !3:29
Clone a database with a single command, duplicating the source to a target. The demo shows you can clone databases you created, not the master.

Never forget to BACKUP yours system database !!!3:31
Démo : How to RESTORE your MASTER system database ?5:56
Learn how to restore the master system database from a backup, troubleshoot access after restoration, restart the SQL Server engine, and verify a successful connection and validation.
Démo : How to RESTORE ALL systems databases ?6:40
Learn how to restore all system databases in SQL Server 2019 administration part 1, including preparing the setup, using a magic command, and verifying access.

What is the first thing to think about corruption3:32
Identify corruption primarily at the physical disk level, avoid memory tests, and weigh database restoration against lengthy terabyte-scale repairs; plan options before acting.
What are the causes of corruption?6:03
Identify the causes of SQL Server 2019 database corruption: storage issues, faulty installations, memory and VM snapshot errors, and human error; learn to detect and prevent them with Microsoft-supported checks.
Corruption that disappears?2:15
Démo :IO/errors7:15
Identify the three corruption-related error types and how Windows reads disk data during IO, illustrated by a live demo on suspect pages and correction steps.
Démo : TORN PAGE DETECTION et PAGE CHECKSUM5:33
Démo : DBCC IND et DBCC PAGE6:00
Explore how DBCC IND and DBCC PAGE reveal page allocation and index structures in SQL Server 2019, showing cluster and nonclustered indexes, page depth, and fragmentation through hands-on examples.

Verification of data consistency2:28
Démo : Creation of a job for consistency check4:48
Explanation on the CHECKDB3:57
Explain how checkdb validates the physical and logical integrity of all objects in a database, checks indexes and catalog consistency, and assesses page data, with notes on CPU impact.
Démo : Options on the CHECKDB10:13
Explore DBCC CHECKDB options, compare standard checks with the physical-only option, and learn how to detect corruption and estimate space via snapshots and SSD performance.
CHECKDB survey2:55
Analyze a checkdb survey of production databases worldwide to reveal how often admins run checks, whether they act on results, and gaps in data integrity, consistency checks, and corruption awareness.
Démo : BACKUP WITH CHECKSUM4:27

Démo : Let's launch our first REPAIR !12:15
Démo : Repair of a non-clustered index5:07
Examine how to address index corruption and fragmentation in SQL Server 2019 by deactivating and rebuilding a cluster index, repairing a non-clustered index, and checking the database.
Démo :Corruption with XVI32 editor4:53
Watch a demo of corruption with the XVI32 editor, showing how a data page can be damaged, a database dropped and recreated, and security implications of access.
Restore Vs Repair4:28
Démo : Restore from a corrupted backup6:21
Demonstrate restoring a corrupted sql server 2019 backup, activate look back, restore the database and active log, and assess inconsistencies during recovery.

Requirements

Just one computer, with Management Studio installed

Description

This course brings together two of my courses on the Udemy platform which are :

SQL server : Detecting and repairing corruption on a database
SQL server : The complete course about BACKUP and RESTORE.

The first chapter will talk about BACKUP and RESTORE :

How to make very simply a BACKUP and a RESTORE.
Discuss the BACKUP LOGS and DIFFERENTIAL BACKUP, and why it is absolutely necessary to implement them in your BACKUP strategy.
Understand the interest of the WITH INIT in a BACKUP, and learn how to make a restoration in time with the WITH STOPAT option.
And finally, we will see the different full recovery model on a database(FULL,SIMPLE and BULKED LOGGUED), and why this is (very) important for our BACKUPs.

The second part, which is longer, is the part of a more advanced level, we will discuss :

The performance when doing a BACKUP on several files.
Avoiding corruption on BACKUPs with the CHECKSUM, and understanding the importance of BACKUP WITH COPY ONLY, to avoid breaking the BACKUP chain during the day.
To discover that you can make a RESTORE of a page, or from a LSN, to avoid losing hours of RESTORE, in case of server crash.
Recover unsaved logs, in case of server crash, with the power of the TAIL LOG BACKUP.
We will discuss the BACKUP WITH COMPRESSION, to save disk space, which can be significant, and the simultaneous copy of BACKUP with the WITH MIRROR option.
We will also see how to restore system databases (Master, MSDB, model, TEMPDB).
The RESTORE WITH STANDBY, which allows to examine the data between each RESTORE.
And to finish, we will see how to make a RESTORE from a SNAPSHOT, see the necessary rights, to make BACKUP, how to consult BACKUP metadata.

The second chapter will talk about corruption on SQL Server :

Database corruption happens all the time, all over the world !! When it happens in your environment, do you know what to do?

This 2-hour course will show you what you need to know to detect and repair most cases of database corruption.
The course begins by explaining why early detection of corruption is so important, and then examines the different causes of database corruption.
You will learn how to configure SQL Server to automatically detect when I/O errors occur.
You'll learn how to configure SQL Server to automatically detect when I/O errors occur, what consistency checks are, the DBCC commands to use to perform consistency checks, and how to configure SQL Server to perform consistency checks regularly.

This course is aimed at all database administrators and developers who will learn how to react to a case of corruption.

Why start SQL administration?

SQL Server is one of the skills very actively sought after on the job market.
Administering an SQL server today is ensuring that you have a full-time job.
Moreover, SQL Server, because of its simplicity, allows you to quickly become competent in very specific areas.
Following this course will help you to stand out technically from your colleagues, or during a job interview, I guarantee it.

Who this course is for:

Future Database Administrators (DBA)
Developers who want to learn about SQL
BI consultants working on technologies other than SQL Server (Oracle etc...)
Students who want to get started on SQL
Tech leads (CRM, Web etc...) who want to discover SQL

SQL Server 2019 : Administration SQL Server Part 1

What you'll learn

Explore related topics

Course content

The BACKUP and RESTORE course2 lectures • 3min

BACKUP and RESTORE level beginner5 lectures • 29min

Let's go a little further in BACKUP and RESTORE12 lectures • 1hr 9min

How to make SQL Server BACKUPS go faster3 lectures • 22min

How to RESTORE your system database ?3 lectures • 16min

Conclusion on BACKUP and RESTORE1 lecture • 4min

Course : Detecting and Correcting Database Corruption1 lecture • 4min

Cause of corruption6 lectures • 31min

Verification of data consistency6 lectures • 29min

How to repair in case of corruption?5 lectures • 33min

Requirements

Description

Who this course is for: