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

Questions: What does BACKUP WITH NAME Command do?

Answer: Nothing more than naming your database backup.

Honestly, I was recently asked this question by a user and I was a bit lost when I heard this one. To find the answer, I had to search the internet and read documents before I was able to answer it. I am never ashamed of accepting that there are quite a few things I do not know.

After reading MSDN documents, I figured out that we can name our backup and use that name to check its value in the msdn table backupset.

Let me show you a simple example:

— taking a backup with name

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

Now we will run a query on the msdb.dbo.backupset table with a where condition. Please note that this table contains details of successful backup operations.

SELECT name, * 
FROM msdb.dbo.backupset 
WHERE [name] = N'AW-SQLAuthority'
GO

When we run above queries it gives us record for all the backups with the name specified in the WHERE conditions.

I am not sure how many really uses this particular feature of SQL Server. Anyway, there are many other useful ways to use msdb.db.backupset database.

Well, that’s it. I think this is when it is about backup with name at this point of time. Do you have any other suggestions or idea? If yes, please share it here in the comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)