Sql-server – In the domain of SQL Server backup, what is a backup set, backup device, backup media, backup media set, backup media family

backupsql server

I'm not a native English speaker and I've read the descriptions in BOL but still can't understand what exactly are they. Can anyone explain in an easy understand way?

Thanks.

Best Answer

The screenshots from SSMS will help you understand the terms:

backup set - is a backup from a single, successful backup operation. E.g. a full database backup created at 8 AM yesterday enter image description here

You can see that there are 2 backup sets in a single backup media set

backup device - a disk file or tape drive

backup media - tapes or disks where backup are stored backup media set - in the screenshot - it's the BAK file where backups are added

enter image description here

backup media family - You're creating the 8 AM full database backup and your database is huge, the backup takes more space than 4 tapes can store. Say you have 4 tape devices that are used to backup your database. When one tape is full, you insert a new one. If your database backup takes 20 tapes, that's 5 tapes per each tape device. The 5 tapes created on the tape device 1 belong to the same backup media family, as they have the same "father" - tape device 1

enter image description here

Related Solutions

Oracle Database – Easy Explanation of ‘Incarnation’ Concept

Following is a short graphic which I will be using to explain what incarnation is used for in the answer:

                                    restore db    +-----+           +-----+     +-----+     
                                    recover db    | 2>3 | --------> |  3  | --> |  3  | --> 
                                    resetlogs     +-----+           +-----+     +-----+     
                                                  ^       Incarn.      3           3        
                                                 /        SCN #       400         500       
                                                /                                           
                                               /                                            
             restore db    +-----+          +-----+     +-----+                             
             recover db    | 1>2 | -------> |  2  | --> |  2  | -->  ...                    
             resetlogs     +-----+          +-----+     +-----+  ^                          
                           ^       Incarn.     2           2     |    2                     
                          /        SCN #      300         400    |   500                    
                         /                                       |                          
                        /                                        |                          
        +-----+     +-----+     +-----+                          |                          
    --> |  1  | --> |  1  | --> |  1  | -->   ...                |                          
        +-----+     +-----+     +-----+  ^                       |                          
Incarn.    1           1           1     |     1                 |                          
SCN #     100         200         300    |    400                |                          
                                         |                       |                          
Backup   11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00      
                                         |                       |                          
Restore/                                (1)                     (2)                         
Recovery

Basics

When an Oracle database is created it receives an initial incarnation number. The incarnation number is stored in the database itself, the control file(s) of the database instance and in the central RMAN catalog database (if you have one).

New Incarnations

Let's take a look at the above graphic. It is a slight modification of the graphic which Oracle uses to explain what incarnations are.
^{Reference: RMAN Media Recovery (Oracle Docs)}

Archive Log Backups

Now let's assume the Archive Log backups are occuring on an hourly basis. Oh, and the datbase is in the ARCHIVE_LOGS mode, which allows us to backup and restore archive logs which enables us to recover the database to a certain point in time. We'll also assume that the database has received a FULL backup at 04:00 (a.m.) the same day. Further, the database has nevery been restored since it was initiailly created and the current INCARNATION# of the database is at 1.

SCN

We'll further assume that 100 transcations occur each hour so that at the time of the backup the SCN# will be a multiple of 100.

Time

The time is only here to increase the readability of the graphic. It plays a role in understanding why Oracle creates a new incarnation for a database that has been restored to a certain point-in-time.

Restoring the Database to Point-in-Time (1)

Somewhere slightly after 13:00 (1pm) somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets of a bunch of RMAN commands to restore the database to that point-in-time our clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.

RMAN retrieves the FULL backup of the database and all Archive Log backups from disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll-forward all finished transactions to the Point-in-Time and roll-back all unfinished transactions to the Point-in-Time, to ensure the database is in a consistent state.

Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:

ALTER DATABASE OPEN RESETLOGS;

Why?

Ok. If you look at the graphic, the SCN# for 13:00 (1 pm) in the original INCARNATION# 1 path is 300. We never reached the backup at 14:00 (2pm) which would have recorded the SCN# 400 in the backup. During the restore/recovery process, no backup is going to happen and (let's assume) if the restore process takes an hour, then no new data is going to be inserted. When the database is ready to go back online and the next backup occurs at 14:00 (2pm) then the backup will contain the SCN# 300. This is the same SCN# of the database as it was before the restore/recovery was initiated, which was recorded for the backup at 13:00 (1pm).

How Does Oracle Know Which Backup to Use?

This is why the INCARNATION# is set to a new value. It allows Oracle to discern between the Archive Log backup at 13:00 (1pm) with the following information:

INCARNATION#    1
SCN#           300
Time......... 13:00

...and the backup at 14:00 (2pm) with the following information:

INCARNATION#    2
SCN#           300
Time......... 14:00

Restoring the Database to Point-in-Time (2)

Let's assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.

RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:

INCARNATION#    3
SCN#           400
Time......... 16:00

...which would otherwise conflict with the backup of the database at INCARNATION# 2 and SCN#400 from 15:00 (3pm). But, because of the new INCARNATION# everything is fine.

Summary

That is the (albeit short) explanation of what the incarnations are used for. It allows RMAN (and the heroic DBA) to determine which path to take to restore and recover the database that has previously been backed up and restored more than once.

Next Up

This short explanation of incarnations is by no means complete. There are other factors that result in ORPHANED incarnations and OBSOLETE backups. Some of these topics will be discussed in a follow up answer either in this thread or in a separate Q & A.

For Example...

When looking at incarnations on a larger time-scale, then restoring and recovering may require you to set off a RESET DATABASE TO INCARNATION <number> before you can start recovering the database, because you might be restoring and recovering the database to a point-in-time which is located on the path of the INCARNATION# 1 and which may require you to restore an AUTOBACKUP copy of the control file, before you can start even thinking about restoring and recovering the database.

Stay tuned...

Sql-server – store half of a record on one page and half of the record on another page

You are splitting hairs over semantics. A physical record cannot span pages. A physical record has to fit in a page.

But a index (or heap) row consists potentially from more than one physical records. An index row will consist from exactly one record in the IN_ROW_DATA allocation unit and zero, one or more records in ROW_OVEFLOW_DATA and/or LOB_DATA. The IN_ROW_DATA record contains pointers to overflow or LOB records. These off-row records themselves may contain more pointers to form a chain or a tree (this is how a VARCHAR(MAX) column can have values that are obviously much larger than 8060 bytes). Such a large value will consist from many individual records, each of 8060 bytes or less. None of these record will ever span multiple pages.

And I did not even touch columnstore index rows, which are completely different.