SqlServer 备份集和备份片以及mirror的backup_set_id

官方文档
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql?view=sql-server-2017

https://docs.microsoft.com/zh-cn/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-tables/backupmediafamily-transact-sql?view=sql-server-2017

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