SqlServer 备份集和备份片以及mirror的backup_set_id
SqlServer 备份集和备份片以及mirror的backup_set_id
backupset类似oracle的备份集
backupmediafamily类似oracle的备份片，一个备份文件就是一个备份片，通过family_sequence_number字段来区分每个备份片
mirror生成多份备份时，必须加WITH FORMAT否则报错Use WITH FORMAT to create a new mirrored backup set.
不带Mirror的备份，一个数据库只有一个备份文件
BACKUP DATABASE [testdb2] TO DISK = N'L:\BAK\testdb2_full_f1.bak'
SELECT * FROM msdb.dbo.backupset where database_name='testdb2' and type='D' and backup_finish_date>dateadd(mi,-10,getdate())
backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size key_algorithm encryptor_thumbprint encryptor_type
23 0A1A4927-E0AB-4CBE-9706-A47D28667529 23 1 1 1 1 1 1 1 NULL 4608 NULL NULL DAI\lukes.liao 13 0 5598 -32 0 39000000198900037 39000000200600001 39000000198900037 39000000167800037 2021-02-22 21:55:17.000 2021-02-28 21:47:24.000 2021-02-28 21:47:25.000 D 52 0 130 852 3290112 testdb2 WONCNFORIDB WONCNFORIDB 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 8B03AA60-42A8-4EB9-9477-F2881D7141D8 8B03AA60-42A8-4EB9-9477-F2881D7141D8 NULL 3C8FADBF-6735-45C6-9BCB-7BE48C20C87C 1D0A09F2-E189-4CAC-8F14-C2A28CA73992 NULL NULL 3290112 NULL NULL NULL
--结果只有一行
select * from msdb.dbo.backupmediafamily where media_set_id=23
media_set_id family_sequence_number media_family_id media_count logical_device_name physical_device_name device_type physical_block_size mirror
23 1 32222373-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_f1.bak 2 512 0
--结果只有一行，一份备份，这个备份只有一个备份文件，family_sequence_number是1，mirror字段值是0
不带Mirror的备份，一个数据库备份拆分成2个备份文件
BACKUP DATABASE [testdb2] TO DISK = N'L:\BAK\testdb2_full_two_f1.bak',DISK = N'L:\BAK\testdb2_full_two_f2.bak';
SELECT * FROM msdb.dbo.backupset where database_name='testdb2' and type='D' and backup_finish_date>dateadd(mi,-10,getdate())
backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size key_algorithm encryptor_thumbprint encryptor_type
24 D02B55B7-2208-414E-96AF-873F751F1A93 24 1 1 2 1 1 1 1 NULL 4608 NULL NULL DAI\lukes.liao 13 0 5598 -32 0 39000000201600037 39000000203300001 39000000201600037 39000000198900037 2021-02-22 21:55:17.000 2021-02-28 21:57:56.000 2021-02-28 21:57:56.000 D 52 0 130 852 3367936 testdb2 WONCNFORIDB WONCNFORIDB 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 8B03AA60-42A8-4EB9-9477-F2881D7141D8 8B03AA60-42A8-4EB9-9477-F2881D7141D8 NULL 3C8FADBF-6735-45C6-9BCB-7BE48C20C87C 1D0A09F2-E189-4CAC-8F14-C2A28CA73992 NULL NULL 3367936 NULL NULL NULL
--结果只有一行
select * from msdb.dbo.backupmediafamily where media_set_id=24
media_set_id family_sequence_number media_family_id media_count logical_device_name physical_device_name device_type physical_block_size mirror
24 1 3CFCB783-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_two_f1.bak 2 512 0
24 2 A3396D7D-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_two_f2.bak 2 512 0
--结果有2行，一份备份，这个备份生成两个备份2文件，每个备份文件只有一行，family_sequence_number是1、2，mirror字段值都是0
带Mirror的备份，一个数据库只有一个备份文件
BACKUP DATABASE [testdb2] TO DISK = N'L:\BAK\testdb2_full_mirror_f1.bak' MIRROR TO DISK = N'L:\BAK\testdb2_full_mirror_f2.bak' WITH FORMAT;
SELECT * FROM msdb.dbo.backupset where database_name='testdb2' and type='D' and backup_finish_date>dateadd(mi,-10,getdate())
backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size key_algorithm encryptor_thumbprint encryptor_type
25 95A43F8D-0477-491C-B680-9A361E25E126 25 1 1 1 1 1 1 1 NULL 4608 NULL NULL DAI\lukes.liao 13 0 5598 -32 0 39000000204400037 39000000206100001 39000000204400037 39000000201600037 2021-02-22 21:55:17.000 2021-02-28 22:03:46.000 2021-02-28 22:03:47.000 D 52 0 130 852 3290112 testdb2 WONCNFORIDB WONCNFORIDB 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 8B03AA60-42A8-4EB9-9477-F2881D7141D8 8B03AA60-42A8-4EB9-9477-F2881D7141D8 NULL 3C8FADBF-6735-45C6-9BCB-7BE48C20C87C 1D0A09F2-E189-4CAC-8F14-C2A28CA73992 NULL NULL 3290112 NULL NULL NULL
--结果只有一行
select * from msdb.dbo.backupmediafamily where media_set_id=25
media_set_id family_sequence_number media_family_id media_count logical_device_name physical_device_name device_type physical_block_size mirror
25 1 66D9C8DC-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_f1.bak 2 512 0
25 1 66D9C8DC-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_f2.bak 2 512 1
--结果有2行，2份备份，mirror字段值0的第一份备份，mirror字段值1的是第2份备份，两份备份的family_sequence_number都是1
带Mirror的备份，一个数据库备份拆分成2个备份文件
BACKUP DATABASE [testdb2] TO DISK = N'L:\BAK\testdb2_full_mirror_one_f1.bak',DISK = N'L:\BAK\testdb2_full_mirror_one_f2.bak'
MIRROR TO DISK = N'L:\BAK\testdb2_full_mirror_two_f1.bak',DISK = N'L:\BAK\testdb2_full_mirror_two_f2.bak' WITH FORMAT;
SELECT * FROM msdb.dbo.backupset where database_name='testdb2' and type='D' and backup_finish_date>dateadd(mi,-3,getdate())
backup_set_id backup_set_uuid media_set_id first_family_number first_media_number last_family_number last_media_number catalog_family_number catalog_media_number position expiration_date software_vendor_id name description user_name software_major_version software_minor_version software_build_version time_zone mtf_minor_version first_lsn last_lsn checkpoint_lsn database_backup_lsn database_creation_date backup_start_date backup_finish_date type sort_order code_page compatibility_level database_version backup_size database_name server_name machine_name flags unicode_locale unicode_compare_style collation_name is_password_protected recovery_model has_bulk_logged_data is_snapshot is_readonly is_single_user has_backup_checksums is_damaged begins_log_chain has_incomplete_metadata is_force_offline is_copy_only first_recovery_fork_guid last_recovery_fork_guid fork_point_lsn database_guid family_guid differential_base_lsn differential_base_guid compressed_backup_size key_algorithm encryptor_thumbprint encryptor_type
26 C4857810-ADE7-4D31-B46A-ACBCB47D1928 26 1 1 2 1 1 1 1 NULL 4608 NULL NULL DAI\lukes.liao 13 0 5598 -32 0 39000000207100037 39000000208800001 39000000207100037 39000000204400037 2021-02-22 21:55:17.000 2021-02-28 22:13:12.000 2021-02-28 22:13:12.000 D 52 0 130 852 3367936 testdb2 WONCNFORIDB WONCNFORIDB 512 1033 196609 SQL_Latin1_General_CP1_CI_AS 0 FULL 0 0 0 0 0 0 0 0 0 0 8B03AA60-42A8-4EB9-9477-F2881D7141D8 8B03AA60-42A8-4EB9-9477-F2881D7141D8 NULL 3C8FADBF-6735-45C6-9BCB-7BE48C20C87C 1D0A09F2-E189-4CAC-8F14-C2A28CA73992 NULL NULL 3367936 NULL NULL NULL
--结果只有一行
select * from msdb.dbo.backupmediafamily where media_set_id=26 order by mirror
26 1 F6483E5A-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_one_f1.bak 2 512 0
26 2 0704B239-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_one_f2.bak 2 512 0
26 2 0704B239-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_two_f2.bak 2 512 1
26 1 F6483E5A-0000-0000-0000-000000000000 1 NULL L:\BAK\testdb2_full_mirror_two_f1.bak 2 512 1
--结果有4行，2份备份，mirror字段值0的第一份备份，这里面两个备份文件的family_sequence_number分别是1、2，mirror字段值1的是第2份备份，这里面两个备份文件的family_sequence_number分别是1、2
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2760242/，如需转载，请注明出处，否则将追究法律责任。