Understanding SQL Server database backup types

As a database administrator you must be very clear on the types of backup available in SQL Server, each of them solves a different purpose in real time. It is your primary responsibility to create a solid database backup plan for your system if you don’t have one yet. Make sure the backups are performed and that backup tapes are stored in a secure location. A backup of database can be a life saver at some situation. You really want to know how it feels when you don’t have a backup and your database just crashed? Read the story of a DBA here

Types of Backup:

  1. Full Backup.
  2. Differential backup.
  3. Transaction log backup.
  4. Filegroup backup.
  5. File backup.
  6. Partial backup.
  7. Copy-only backup.
  8. Mirror backup.
  9. Split backup.

Technically there are only three types of backup (Full, Differential and Transaction log) but all above stated backups are just add-ons to those 3 types of backup.

sql-server-database-backup-general

sql-server-database-backup-options

1. FULL BACKUP:
The name itself is self-descriptive. It backs up the whole database including primary file, secondary files and log file. A full database backup can restore the data upto the time when backup was finished.

2. Differential backup:
A differential backup includes only the data to the backup that has changed since the last full backup.

3. Transaction log backup:
A transaction log backup keeps backup of the active part of the transaction log. It works only If the database is set to the “Full” or “Bulk-logged” recovery model, for SIMPLE recovery model you cannot have transaction log backup. The transaction log gets truncated after a successful transaction log backup otherwise it continues to grow.

4. Filegroup backup:
Filegroup backups allows you to backup one or more selected filegroups. It includes all files from that filegroup to the backup.

5. File backup:
This backup allows us to take only the selected file or files from one or more file group. Advantage of this type backup is, when you have very large data files and you need to just backup one or two selected datafiles rather backing up the whole database or filegroup.

6. Partial Backup:
Partial backups are very much similar to the file and filegroup backups but the main difference is, it excludes the READONLY filegroups in the backup and backups all other files and filegroups which are READ-WRITE.

7. Copy-only backup:
Copy-only backups is a special and extra ordinary feature introduced in SQL Server 2005 version. It does not disturb the current backup chain. Normally, a database backup is recorded in the database and in backup file itself in form of LSN and is identified as part of the backup chain. Suppose, you have a backup plan which is taking full backup of your database on every Sunday and differential backups every night. So the subsequent differential backups use the last full backup as their base or you can say a check point. In case you need to restore the testing environment with production to the latest, then what would you prefer? You would obviously prefer to take a full backup of production database and restore that in test. So, there the backup chain will break, the previous differential backup files will of no use since you have taken a full backup. To resolve that issue, Microsoft introduced copy-only backup option.

8. Mirror backup:
This feature allows us to take a backup of the database at two different physical place of the same copy.  Both backup files are identical and of same size. This saves time when you are planning to move or copy your backup file to another location after backup completion. Mirror backup option is compatible with FULL, DIFFERENTIAL and TRANSACTIONAL LOG backup types.

9. Split backup:
This feature allows us to take a database backup in pieces and at different physical places. The backup files are not identical like “Mirror backup”. These backup files are dependent on each other and cannot be restored excluding a single file from the whole. This is the fastest way to take backup of a database. This backup option is also compatible with FULL, DIFFERENTIAL and TRANSACTIONAL LOG backup types.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

6 thoughts on “Understanding SQL Server database backup types

  • May 28, 2015 at 2:00 pm
    Permalink

    It’s Very use full for me Thanks for shared this,,,, 🙂

    Reply
  • April 13, 2016 at 3:39 pm
    Permalink

    Thanks man, it was very useful.

    Reply
  • February 24, 2017 at 4:30 am
    Permalink

    With this code below, it will create a new backup file name every time the backup runs, correct?

    BACKUP DATABASE [dbBasics]

    FILEGROUP = N’PRIMARY’

    , FILEGROUP = N’FG2011′

    , FILEGROUP = N’FG2012′

    TO DISK = N’C:\Backup\dbBasicsFilegroup.bak’

    WITH NOFORMAT

    , NOINIT

    , NAME = N’dbBasics-Full Filegroup Backup’

    , SKIP, NOREWIND, NOUNLOAD, STATS = 10

    Reply

Leave a Reply

%d bloggers like this: