Backup, Recovery, and Database Restore
Learn how to back up and recover databases.
We'll cover the following...
Learning how to back up, restore, and administer databases is essential for those pursuing a career as a database administrator, database developer, or data analyst. These are essential components of maintaining a database and are important responsibilities.
There are several primary backup methods available for SQL Server databases. Each one is dependent on the recovery model that we use. In recovery models, the last point in each model discusses the types of backups that we can use, for example, full and differential backup, and transaction log.
The actions performed by each backup method are discussed in the following subsections:
Full backup
- A full backup backs up the entire database, including the transaction log if applicable.
- It’s ideal for adding the same database to a new server for testing.
- It might take a long time to back up if the database is extensive in size, in addition to it backing up the entire database and transaction log.
- A full backup doesn’t need to be performed nearly as often as a differential since we’re backing up the entire database.
A full backup is done by using the following command:
BACKUP DATABASE DatabaseName TO DISK = '\DatabaseName.BAK'
Differential backup
- A differential backup depends on a full backup, so we must perform the full backup first, but this will back up any data changes between the last full backup and when the differential backup takes place.
- It’s ideal in any situation; we must do it more frequently as we often add data to the database.
- It’s much faster than a full backup and takes up less storage space.
- However, this doesn’t capture the database entirely, which is why we must perform it periodically.
The differential backup can be performed by using the following command:
BACKUP DATABASE DatabaseName TO DISK = '\DatabaseName.BAK' WITH DIFFERENTIAL
Transaction log
-
A transaction log backs up all of the activity that has happened in the database.
-
Like a differential backup, it’s dependent on a full backup.
-
It’s valuable and necessary in some cases when restoring a database in total.
-
Since transaction logs hold the activity within the database’s history, it’s wise to back this up frequently so that the logs do not grow large.
-
Some ...