4

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?

  • 1
    I have never used MEDIANAME in ~20 years of SQL Server administration. – Aaron Bertrand Feb 2 '16 at 19:52
  • It seems to me mediaset/medianame concept is obsolete when using disk as backup media. it that true? – Shawn Feb 2 '16 at 19:55
2

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.

enter image description here

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

enter image description here

So you can see backup_set_id and media_set_id are different.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.