What does BACKUP WITH NAME Command do? – Interview Question of the Week #130

Interview question: What does WITH NAME do in a SQL Server BACKUP command?

Answer: It gives the backup set a descriptive name. You can see that name in msdb.dbo.backupset.name and in backup-header information. It does not rename the destination .bak file, change what is backed up, or replace a restore test.

I will admit that when a reader first asked me this, I was not sure. I went back to the documentation before answering. SQL Server has enough backup options that I would rather check than guess.

Here is the idea using my original AdventureWorks2014 example. Choose a database that exists on your instance and a backup folder writable by the SQL Server service account. This command creates a real backup file, so run it only in a suitable lab:

BACKUP DATABASE AdventureWorks2014
TO DISK = N'D:\SQLBackups\AdventureWorks2014.bak'
WITH NAME = N'AW-SQLAuthority';
GO

The file path is the backup destination. AW-SQLAuthority is the backup set’s logical name. The two can be different, and more than one backup set can be stored on a media set, so the name alone is not a unique identifier.

My original lookup in msdb.dbo.backupset remains useful. I now select the columns that answer the question and include the backup set ID and time:

SELECT TOP (10)
       bs.backup_set_id,
       bs.database_name,
       bs.name AS backup_set_name,
       bs.backup_start_date,
       bs.backup_finish_date,
       mf.physical_device_name
FROM msdb.dbo.backupset AS bs
LEFT JOIN msdb.dbo.backupmediafamily AS mf
  ON mf.media_set_id = bs.media_set_id
WHERE bs.database_name = N'AdventureWorks2014'
  AND bs.name = N'AW-SQLAuthority'
ORDER BY bs.backup_finish_date DESC, bs.backup_set_id DESC;

A striped or mirrored backup can have multiple media-family rows for one backup set. Also, physical_device_name in history is a recorded device path, not proof that a file still exists at that location. Confirm the actual backup media and test restores as part of backup operations.

The useful distinction for the interview is short: NAME describes a backup set; TO DISK selects where bytes go. The original answer was right about finding the name in backupset, but “nothing more than naming your database backup” could be read as renaming the file, which it does not do.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.