From the course: Designing Database Solutions for SQL Server 2016

Unlock the full course today

Join today to access over 23,300 courses taught by industry experts.

Backup and recovery strategies

Backup and recovery strategies - SQL Server Tutorial

From the course: Designing Database Solutions for SQL Server 2016

Start my 1-month free trial Buy for my team

Backup and recovery strategies

“

- [Instructor] One of the most important roles of a database administrator is to constantly protect the integrity of the database and maintain the ability to recover quickly in case of a failure. In light of this it's critically important to have a backup and recovery strategy in place in order to be ready for an emergency. Let's start by taking a look at the different backup options that are available in SQL Server 2016. There are three recovery models that dictate how the transaction logs operate for the database. In the simple model the transaction log automatically truncates itself when the data file has reached certain checkpoints. The benefit is that the log is kept small, but it does so at a cost. Database restores can only occur to the exact point that the last full backup was taken. With a simple recovery model, there's no opportunity to do a point-in-time restore. In the bulk-logged recovery model, bulk operations are minimally logged but the transaction log does maintain a…

Contents