SQL Server Backup Medianame – Best Practices

backupsql server

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?

Best Answer

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.

Related Question