Join Adam Wilbert for an in-depth discussion in this video Understanding the available backup options, part of Database Foundations: Administration.
- With the recovery model, we established how we want to backup our transaction logs. Now we get to define how complete we want our individual backups to be. There's a balance to strike between the speed at which a backup is performed and the size that it takes up on disc and the time that it takes to restore a database from a backup. And the larger your database is, the more critical the decision will be. Let's take a look at our options. I'm going to find the H+Active Database and right-click on it, and them I'm going to go to tasks, and then back up. At the top we can see the recovery model that we chose in the prior movie, and then we can turn our attention here to the backup type.
Right now it's set to full, but if I use the drop-down list you'll see I have the option of full or differential. A full backup contains all the data in the database and a portion of the transaction log that's necessary to recover all the data up to a point that the backup was run. If you restore a database from a full backup, you only need to restore that one file to get up and running again. And so they tend to be the quickest to recover after a disaster. However, full backups are the slowest backup options available. A differential backup, which is sometimes called a Delta, contains only the data that has changed since the last full backup.
Since you're not saving all of the data with a differntial backup they tend to complete much faster than a full backup would. With a differential backup, you'll have at most two files to restore in the event of a disaster. First, you'll need to restore the full backup, and then apply the differential backup to return to the most current state of the data. There's also a third option available called an incremental backup or transaction log, which only contains the data that's not changed since the last full or incremental backup. After completing your first full backup, you'll see a third option here called an incremental backup or what's called a transaction log.
And this will only contain the data that's changed since the last full backup or incremental backup. So if I make a full backup on Monday, and make incremental backups on Tuesday and Wednesday, the second incremental backup will only contain Wednesday's changes. In order to restore a database from an incremental backup, you need to restore each one in sequence, starting with the last full backup. These are generally the smallest and fastest backups to perform but they take the longest to recover during a restore. So those are the available backup options that you can choose from. The option you choose will depend on the size of your database, and how critical it is to recover quickly in the event that you need to restore a database from the backup.
Note: This course will also prepare certification candidates for the "Administer a database" domain of the Microsoft Technology Associate (MTA) Exam 98-364, Database Administration Fundamentals.
- Securing the database server
- Understanding Windows authentication vs. SQL Server authentication
- Assigning fixed server roles and fixed database roles
- Granting object-level permissions
- Understanding ownership claims
- Creating backups
- Restoring a database