Common Terms & Concepts of SQL Backups

4 Jun

Not everyone is an IT professional handling the complex tasks that go along with managing your RDBMS (Relational Database Management System) on a day-to-day basis. However, that doesn’t mean we can’t verse ourselves in the basics, including common terms and concepts used in the RDBMS world. Perhaps one of the more important areas to study is that of “backups” or “recovery” – which we have talked about a good bit in the last few posts. Today we’ll dive in to some common terms and concepts that pertain to backing up your SQL database.

Below are some common SQL terms and concepts:

Backup (Noun) – This refers to the actual copy of backed up data that is used to restore the server and data if there is a failure.

Back up (Verb) – The act of backing up the data on the server to another device or disk.

Partial Backup – Contains data from only some of the filegroups in a database, including the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

Recovery Model – A database property that controls transaction log maintenance on a database. There are three different recovery models including: simple, full, and bulk-logged (see full comparison). The recovery model states a servers backup and restore requirements.

Restore – Copying all data from a SQL Server backup to a specific database.

Here are a few terms that are associated with types of backups:

Data Backup – The backup of data in a complete database, partial database, or files.

Database Backup – A full backup of the database.

Differential Backup – A backup of data changes since the last backup occurred.

Full Backup – A data backup that contains all data in a specific database or a set of filegroups or files, and also enough log to allow for recovering that data.

Log Backup – A backup of transaction logs

File Backup – A backup of one or more database files.

Here are some terms about different devices used in a backup:

Backup Device – A disk or other device which SQL Server backups are written, and from which they can be restored.

Backup Media – One of more disks or tape which the server backups have been written.

Media Set – An ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices.

These are some of the common terms you may here when talking about database backups, whether with a SQL Database or otherwise. As we said, not all of us are IT pros, and don’t have to deal database on a daily basis, but it’s always good to know a few things about your data and how it’s backed up – if for no reason other than sleeping better at night!

*Some terms were cited from Microsoft MSDN