SQL Server Backups (High Level Overview)

Author by Nick Adams

Backing up data in your environment is a no-brainer but HOW to back up your data requires some high level understanding of what backup types are available and how those backups are managed.

Understanding what Backup Types and Backup Models to use is going to be circumstantial and, of course, crucial to ensure your data environment is not at high risk for damage.  Your backup strategy is going to depend on your environment, how many clusters, instances, servers, and data volume you need to manage.

 

Backup Types

Full Backups
Backs up the entire database, this means everything.  Data, Objects…everything up until the process is complete.  This can be very hard on the system due to sheer volume so use this type with discretion.  It takes a long time.

 

Transact-SQL example

BACKUP DATABASE [Adventureworks2014]
TO DISK = ‘Disk location\location’
WITH NAME = ‘FullBackup_1’

 

Differential Backups
Backs up all data that has been changed since the last FULL Backup.  For Differential Backups to work a FULL Backup must have been created first.  Concurrent Differential Backups will contain all changes that the other Differential backup has as well.  Backs up the last data that the last Full Backup that was RUN and COMPLETED.

 

Transact-SQL example

BACKUP DATABASE [Adventureworks2014]
TO DISK = ‘Disk location\location’
WITH DIFFERENTIAL

 

Transaction Log Backups
Transaction Log Backups will backup data that has been modified since any last backup including Full, Differential, or Transaction Backups.  Transaction log backups are not cumulative.  This backup will also truncate all the old data and leaves anything currently active.  Thus, this allows for “point in time” recovery.

 

Transact-SQL example

BACKUP LOG [Adventureworks2014]
TO DISK = ‘Disk location\location’

 

Recover Models

SQL Server has three different backup models that can be used based on what you think is best for your environment.  Recovery models are set in the Recovery Wizard or by Transact-SQL.

Simple
With the Simple recovery model, there is no point in time recovery.  Transaction logs get truncated every time backups are run with this model.  Checkpoints happen every minute.  SQL Server manages the backups.

Full
The Full recovery model doesn’t touch the Transaction Log.  Admins should also be performing Transaction Log backups.  If not, the Transaction logs will fill up and cause storage or performance problems.  Admins will have more control over the backups than the Simple model.  Think of the Simple model as SQL Server doing the backup work for you.

Bulk-Logged
Bulk Logging models are a special case.  They should really be used to ensure stability of bulk loads or imports.  Bulk-Logged model will minimize logging for your bulk operations and increase performance. This being the case, use Bulk-Logged Backups right before bulk operations only and switch back to Full or Simple.