Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I looked at the Microsoft documentation (http://technet.microsoft.com/en-us/library/ms186865.aspx) and see that there is a WITH NAME option for backups. But it's not the name of the file (I specify that with TO DISK). And if I don't specify it, the name is left blank.

So what is the purpose of specifying WITH NAME? What benefits do I get?

share|improve this question
2  
The most common purpose I've seen is right before and after a major change to the database. You can name your backup "Before xx Change" and "After xx Change". It makes it easier to find when you need it. –  Kenneth Fisher Jan 8 at 15:28
add comment

2 Answers

up vote 3 down vote accepted

It's the name of the backup set. Take a look at this example:

backup database SomeDatabase
to disk = '<backup dir>\SomeDatabase.bak'
with name = 'SomeBackupsetName';
go

backup database SomeDatabase
to disk = '<backup dir>\SomeDatabase.bak';
go

Note that I'm backing up the database to the same backup media ("SomeDatabase.bak"). Here is where you'll see the backup set name:

restore headeronly
from disk = '<backup dir>\SomeDatabase.bak';

The first row will be the first backup set with the BackupName set to "SomeBackupsetName". The second backup set will be NULL, as we didn't name it.

It's simply a way to distinguish between backup sets.

share|improve this answer
add comment

This merely allows you to look up a backup by name, using:

SELECT * FROM msdb.dbo.backupset WHERE [name] = N'the_name_you_used';

This can be useful as a kind of tagging mechanism, e.g. if you specify backups for different things (such as restoring to dev vs. initiating log shipping vs. shipping off-site). Much easier than relying solely on the file name (which can be changed in Windows after the backup is complete), or the file number when you've appended multiple backups to the same file.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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