4

When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database?

BACKUP DATABASE ExampleDatabase To DISK = N'...'
WITH FORMAT, 
MEDIANAME = N'ExampleDatabase-Media-yyy-mm-dd-hh-mm', 
NAME = 'ExampleDatabase-yyy-mm-dd-hh-mm',
COPY_ONLY
...
  • will this result in only the Media Set with name ='ExampleDatabase-Media-yyy-mm-dd-hh-mm' being wiped (if it exists)?

The context is that I am doing some ad-hoc backup and restores from one server to another. And I want to make sure that the FORMAT in my command will not format any other Media Sets that might have been created.

I want to make sure that my backup statement that uses FORMAT will not interfere with any other Backup maintenance plans that are in place by destroying Media Sets other than the one I define in my statement.

  • 5
    If you write out unique file names (easy to do if you include time stamps in the names somehow, which also improves manual file management), and use WITH INIT, will this really matter? In 20 years of managing SQL Server - and granted I never used tape backups - I don't ever recall having a need to specify any of the media set or format options. Or further, store more than one backup in a single file - can't say I ever understood the desire to do that, either. – Aaron Bertrand Mar 17 '16 at 5:16
  • @ AaronBertrand, You have said answer as a comment statement. You make as a answer. – Md Haidar Ali Khan Mar 17 '16 at 5:38
  • 2
    @MdHaidarAliKhan thanks, but really I just asked a question. The OP may have a very good reason for using media sets. – Aaron Bertrand Mar 17 '16 at 6:05
  • 1
    @AaronBertrand 1) I am not using Tape backups, just to disk 2) I have no good reason for using Media Sets. The reason I introduced MEDIANAME and FORMAT to my statement was that I was seeing my .bak files grow over time (because of default append behaviour). So I introduced an explicit timestamped MEDIANAME. I agree I could probably use WITH INIT and avoid WITH FORMAT, but I am still wanting to understand the semantics of a Media Set / Media Volume and the behaviour of FORMAT especially in the context of backing up to Disk rather than Tape (where the documented semantics make more sense). – zadam Mar 17 '16 at 7:28
3

@zadam,I would like to say some bit about media set. I also expect some more details from our expert like Mr. Aaron Bertrand & some other StackExchange DBA Experts.

First of all , what is media set.

A successful backup operation adds a single backup set to the media set.

enter image description here

The backup set is described in terms of the media set to which the backup belongs.If the backup media consists of only one media family, that family contains the entire backup set.

A media set is created on the backup media during a backup operation by formatting the backup media.

Backing Up to an Existing Media Set

When you are backing up to an existing media set, you have the following two options:

  1. Append to the existing backup set.

Note Here:- which is the default behavior of the BACKUP, can be explicitly specified by using the NOINIT option.

  1. Overwrite all existing backup sets with the current backup, leaving the current media header in place.

Note Here :- Overwriting existing backup sets is specified by using the INIT option of the BACKUP statement.

For Example:-

BACKUP DATABASE DatabaseName
TO DISK = 'd:\bu\mm_labeled.bak'
WITH MEDIANAME = 'BackupsToTheDDrive',
MEDIADESCRIPTION = 'Portable Drive Used for Backups',
INIT;

As per Grant Fritchey Here The MediaSetID is a GUID that uniquely defines this set.It will also be recorded in msdb so you can link a backup to a particular server.

For example :

SELECT  *
FROM    dbo.backupmediaset AS b
WHERE   b.media_uuid = '720C1417-3889-4730-9D33-74EBF11E854A' ;

For example if your full backup TSQL will be like that

Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'E:\Database_Backup\DatabaseName_Backup_16022016\DatabaseName.bak'
with FORMAT,
Medianame='E_SQLServerBackup',
Name='DatabaseName-Full Database Backup'
GO

enter image description here

Every volume of backup media (disk file or tape) contains a media header that is created when by the first backup operation that uses the tape (or disk). That header remains intact until the media is reformatted.

If you will execute this TSQL statement to know about media_set_id & 'family_sequence_number'.

select * from msdb.dbo.backupmediafamily;

Here i would like to say that if you shall take backup in Disk. So always your 'family_sequence_number' will be 1. But if you shall take backup in Tape then 'family_sequence_number' will be like the sequence number of the initial tape is 1, the sequence number of the second tape is 2, and so forth.

enter image description here

The media header contains all of the information required to identify the media (disk file or tape) and its place within the media family to which it belongs. This information includes:

The name of the media.
The media name is optionally, but we recommend consistently using media names that clearly identify your media. A media name is assigned by whoever formats the media.
The unique identification number of the media set.
The number of media families in the media set.
The sequence number of the media family containing this media.
The unique identification number for the media family.
The sequence number of this media in the media family. For a disk file, this value is always 1.
The Microsoft Tape Format media label or the media description (in free-form text).
The name of the backup software that wrote the label.
The unique vendor identification number of the software vendor that formatted the media.
The date and time the label was written.

Note :- In case of Disk, The sequence number of this media in the media family.This value is always 1.

For your ref of Tape Media Set along with Backup set Media Sets, Media Families, and Backup Sets (SQL Server) and Here

  • is valid for Making remote database backup into local folder ? – Kiquenet Jan 27 '17 at 11:51
  • Full backup options: WITH NOFORMAT, NOINIT, NAME = N'temp_database-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 – Kiquenet Jan 27 '17 at 11:57

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.