Main backup types

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

Assume that over a period of time, a database accumulates data as follows:


Full database backup

As the name implies, a full database backup contains the entire database.  It contains the data files of a database, and the active portion of the transaction log as at the end of the backup.


The basic syntax for a full database backup is as follows:

BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

Differential database backup

A differential database backup contains the changed data pages since the last full database backup, and the active portion of the transaction log as at the end of the backup.


The syntax to perform a differential backup is similar to that for a full database backup, with the exception of the DIFFERENTIAL option e.g.

BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL

Transaction log backup

A transaction log backup backs up the transaction log records that have not yet been backed up, up to the last log record that exists at the time the backup ends.  You can only perform a transaction log backup if your database is running the full or bulk-logged recovery models.


A common misconception is that a full database backup will truncate the transaction log, so that transactions that were backed up will no longer be backed up again in the next transaction log backup.  A full database backup does not truncate the transaction log.


The syntax to perform a transaction log backup is similar to that for a full database backup, with the exception of the second keyword i.e. instead of DATABASE, it's LOG e.g.

BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak'

So which types of backups should I perform?

As usual, the answer is it depends.  It depends on your disaster recovery requirements, and is usually a compromise between the amount of data loss that's acceptable, the impact of backups on normal database operations, and the time required to recover from a disaster.

If your environment can only tolerate a loss of 5 minutes worth of data, you would obviously need to perform some kind of backup every 5 minutes.  For a sizeable database, a full database backup every 5 minutes would impact the regular operations of a database too much to be of any use.  Similarly for a differential backup, if the database has undergone a lot of changes since the last full backup.  A transaction log backup would be the best fit, as it only backs up transactions that have been created since the last transaction log backup.

However, to restore a database using only transaction log backups, you will need to restore the entire chain of backups from the initial recovery point of the database up to the latest transaction log.  This in itself may be too long, if the initial recovery point is some time back.


You can use differential backups to bring forward the initial recovery point from which transaction log backups should start.  However, for an active database, a differential backup takes longer than a transaction log backup, impacts database operations more, and consumes more space.


These are some of the points that you should consider when formulating your backup plan:

·how much data loss is acceptable

·will the backups impact regular database operations

·is there a maintenance window whereby high impact backup operations can be performed

·what is the acceptable time to recovery

·is there a need for the ability to restore to a point in time, in which case transaction log backups will be required

·is there adequate storage space to store all the required backups

·can high availability solutions be used to complement the disaster recovery plan e.g. clustering, log shipping, database mirroring in SQL Server 2005 and above etc

Document history
7/16/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.