SQL Server System Table analysis (1)-Backup table

1. backupfile

Each record in the table corresponds to a backup of data files or log files in the database. The fields in the table describe the attributes of the files during Backup. In general, it is a detailed table of backup files.

Main fields:

Backup_set_id: the unique ID of the Backup set file. A backup set may contain multiple data files or log files.

Filegroup_name: name of the file group backed up by the database. The file group name can be customized in the database configuration.

File_number: ID in the backup set

Backed_up_page_count: Number of pages backed up file_type: Type of the backed up file, D = data file, L = Log File, F = Full-text directory (full-text index), which can be nullfile_size: the length of the backup file, in bytes.

Logical_name: Logical name of the backup file

Physical_drive: Physical drive or partition name of the file to be backed up, for example, D: \, indicates D Disk

Physical_name: Physical location of the file to be backed up state: status of the file during backup, 0 = online, 1 = restoring, 2 = recovering, 3 = recovery pending, 4 = suspect...

File_guid: Unique File Identifier backup_size: Backup size

Is_readonly: Read-only, 1 = file is read-only

Is_present: 1 = files included in the backup set

2. backupfilegroup

During backup, each file group in the database occupies one row.

Main fields:

Backup_set_id: Backup set idname containing the file group: file group name

3. backupmediafamily

Each media cluster occupies one row in the table. If a media cluster resides in an image media set, each image server in the media set has a separate row. This table is stored in the MSDB database. Media cluster, that is, the physical file after backup.

Main fields:

Physical_device_name: physical path of the backup file

4. backupmediaset

Each backup media set occupies one row in the table

5. backupset

Each backup set occupies one row in the table. The "Backup set" contains backups from a single successful backup operation. The restore, restore filelistonly, restore headeronly, and restore verifyonly statements operate on a single backup set in one or more specified backup sets.
An important table, the following table lists the backup set expiration time, backup set file name, backup execution username, master database version number, backup start time, backup End Time, backup size, database name, backup type, and database. recovery mode

Main fields:

Backup_set_id: Unique ID of the Backup set. ID, primary key.

Media_set_id: ID of the unique media set in the media set where the backup set is located.

Expiration_date: date and time when the backup set expires. An important attribute. If expiration_date is exceeded and the disk control is insufficient, the new backup file overwrites the expired backup file.

Name: Backup set name

User_name: name of the user performing the backup operation

Software_major_version: Microsoft SQL Server master version

Backup_start_date: start date and time of the backup operation.

Backup_finish_date: end date and time of the backup operation.

Type: Backup Type. D = database, I = Differential database, L = log, F = file or file group, G = Differential file, P = part, q = Differential part

Compatibility_level: Set the database compatibility level.

Backup_size: size of the Backup set (in bytes)

Database_name: name of the database involved in the backup operation

SERVER_NAME: name of the server that runs the SQL Server backup operation

Is_password_protected: whether to enable password protection

Recovery_model: recovery mode. Full, bulk-logged, simple

Is_damaged: 1 = database corruption was detected when the backup was created. The backup operation is required to ignore the error and continue the backup.

Compressed_backup_size: Total number of bytes of backup stored on the disk.

In practice, query the detailed information of the DB Backup set:

 Select     Top    1000     [  Backup_set_id  ]  ,.  [  Media_set_id  ]  ,  [  Expiration_date  ]  ,  [  Name ]  ,  [  User_name  ]  ,  [  Software_major_version  ]  ,  [  Backup_start_date  ]  ,  [  Backup_finish_date  ] ,  [  Type  ]  ,  [  Compatibility_level  ]  ,  [  Backup_size  ]  ,  [  Database_name  ]  ,  [ SERVER_NAME  ]  ,  [  Is_password_protected  ]  ,  [  Recovery_model  ]  ,  [  Is_damaged  ]  ,  [  Begins_log_chain ]  ,  [  Compressed_backup_size  ]  , B. physical_device_name

  From     [  MSDB  ]  .  [  DBO  ]  .  [ Backupset  ]  A,  [  MSDB  ]  .  [  DBO  ]  .  [  Backupmediafamily  ]  B

  Where  A. media_set_id  = B. media_set_id  Order     By  Backup_set_id  Desc 

RelatedArticle:
SQL Server System Table Analysis (2)-job)
SQL Server monitors job execution

Find yourself-2011/04/18-. net, C #, ASP. NET-starter blog