SQL Server – Backup Type

Background

Quite familiar with the three types of backup that we have in SQL Server.

Database Backup Types

The database backup types are full, differential, and log.

Metadata

msdb.dbo.backupset

To track backups we can look in the [msdb].[dbo].[backupset] table.

Something Broke?

Today I was trying to do a restore and noticed that that the type column in the [msdb].[dbo].[backupset] table read ‘F‘.

backupset.type ( Legacy )

Here is the traditional mapping of backup operation to type.

Backup Operation Backup Type ( backupset.type)
Database D
Differential I
Transaction L

File Groups Backup

To better support Very Large Databases ( VLDB ), Microsoft added a couple of targeted functionalities.

LAB

Let us play around with targeted file groups backup.

Database Properties

Database Properties – Filegroups

GUI

databaseProperties_GUI_20180625_1047PM.png

Code

sp_helpfilegroup
Sample

use [DBLab]
go

exec sp_helpfilegroup
go

Output

sp_helpfilegroup__20180625__1054PM

sys.filegroups
Sample

use [DBLab]
go

select
        [filegroup]
        = tblSFG.[name]

    , [filegroupID]
        = tblSFG.[data_space_id]

    , [isReadOnly]
        = tblSFG.[is_read_only]

    , [isDefault]
        = tblSFG.is_default

from   sys.filegroups tblSFG

order by
	tblSFG.[name] asc

Output

 sysDOTfilegroups_20180625_1108PM

Explanation
  1. isReadOnly
    • fgAudioFiles
  2. isDefault
    • PRIMARY

Backup

Database Backup – Normal

Sample


use master
go

backup database [DBLab]
	to DISK='NUL:'
	with init
	    , format
		, description='normal' 

Output

database_backup_normal_20180625__1138PM

Explanation

  1. Data files
    • DBLab, fileAudioFile_001, fileVideo_001
  2. Log
    • DBLab_log

Review Backup Type

SQL

declare @dbname sysname

set @dbname = 'DBLab';

select top 1

          backup_set_id

        , [type]

        , [database]
            = tblBS.[database_name]

        , [description]
            = tblBS.[description]

from   [msdb].[dbo].[backupset] tblBS

where tblBS.[database_name] = @dbname

order by
        backup_set_id desc

Output

msdb__dbo__backupset__20180625__1138PM

Database Backup – Read/Write File Groups

Overview

Add read-write_filegroups to implicitly skip read-only file groups.

Sample


use master
go

backup database [DBLab]
    read_write_filegroups
	to DISK='NUL:'
	with init
	, format

Output

readwritefilegroups__20180625__1148PM

Explanation

  1. Included
    • Data files
      • DBLab, fileVideo_001
    • Log
      • DBLab_log
  2. Not Included (as readonly )
    • fileAudioFile__001

Review Backup Type

Output

readwritefilegroups__backupset_20180625__1152PM

Explanation
  1. Type=P
    • Partial

Database Backup – Specific filegroups

Overview

Use the filegroup marker to explicitly tag filegroups you want included.

Sample


use master
go

backup database [DBLab]
	filegroup='PRIMARY'
	to DISK='NUL:'
	with init
	    , format
	    , description='filegroup explicit ( primary)'

Output

filegroupsTag__20180626_1203AM

Explanation

  1. Included
    • Data files
      • DBLab
    • Log
      • DBLab_log
  2. Not Included (as not part of tagged filegroup)
    • fileAudioFile__001
    • fileVideo_001

Review Backup Type

Output

filegroupsTag__20180626_1205AM

Explanation
  1. Type=F
    • File Groups

backupset.type ( Revised )

Here is a revised mapping of backup operations that includes new file groups subtleties.

Backup Operation File Group Backup Type ( backupset.type)
Database D
Differential I
Transaction L
Partial Read Write file groups only / Skip Read Only file groups P
File Groups or Files  Explicitly listed file groups F

References

  1. Microsoft

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s