2

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?

1
  • 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 '14 at 15:28
3

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.

3

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.

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.