If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME?
Is it true that MEDIANAME is only for tape backups? If not, when and why should I specify MEDIANAME in the backup statement?
Additional question: msdb.dbo.backupmediaset.media_set_id actually means backup file id if backup media is disk, it that true?
If I make full/differential/log backups to disk under full recovery model it is important to specify MEDIANAME?
NO, it is not important
Is it true that MEDIANAME is only for tape backups? If not, when and why should I specify MEDIANAME in the backup statement?
No it is not just for tape backups, you can take local disk backup and specify media name. More over its a name given to particular backup set.For example
BACKUP DATABASE AdventureWorks2012
TO DISK='X:\SQLServerBackups\AdventureWorks1.bak',
DISK='Y:\SQLServerBackups\AdventureWorks2.bak',
DISK='Z:\SQLServerBackups\AdventureWorks3.bak'
WITH FORMAT,
MEDIANAME = 'AdventureWorksStripedSet0',
MEDIADESCRIPTION = 'Striped media set for AdventureWorks2012 database;
GO
I just took backup of Adventureworks database on my local machine and provided media name below screenshot will show you the result.
Additional question: msdb.dbo.backupmediaset.media_set_id actually means backup file id if backup media is disk, it that true?
No they both are different. Below query can easily show it to you. I used media name we just created above in the backup query
select bm.media_set_id,
bs.backup_set_id,
bm.media_family_count,
bm. name,
bm. description
from msdb.dbo.backupmediaset bm
inner join msdb.dbo.backupset bs
ON bm.media_set_id=bs.media_set_id
where bm.name='AdventureWorksStripedSet0'
And the output is
So you can see backup_set_id and media_set_id are different.
From MediaName Property:
The MediaName and MediaDescription properties are written to a tape medium when the medium is initialized.
From Backup Object:
For example, the MediaName and MediaDescription properties provide, primarily, data used to ensure media availability for tape devices and are applicable when the backup operation defined will initialize the media.
So you don't need this for your backup operation
