Retrieve information about a recent copy only backup?

Question

I have taken a copy only full backup using the query

BACKUP DATABASE abc
TO DISK = N'E:\Backup\abc.bak' 
WITH COPY_ONLY,COMPRESSION

And when I try to check this information via the query,

SELECT  top 100 *
FROM msdb.dbo.backupset a with(nolock)
INNER JOIN msdb.dbo.backupmediafamily b with(nolock)
ON a.media_set_id = b.media_set_id 
WHERE is_copy_only = 1
ORDER BY  a.database_name desc, 
a.backup_finish_date desc

...the name of the backup I just took can't be found in the name column, instead NULL is being returned. However the path to the backup is listed in the physical_device_name column. I am unable to figure out why name of the backupset is being returned as NULL.

Answer 1

The quick answer is that you didn't name your COPY_ONLY backup.

BACKUP  DATABASE abc
    TO  DISK = N'E:\Backup\abc.bak'
WITH    COMPRESSION, COPY_ONLY, STATS = 10, 
        NAME = 'abc Name', 
        DESCRIPTION = 'abc Description';

These two WITH options are the equivalent to what you would find in the GUI under the Backup Options page.

Answer 2

Try querying without the "top 100":

SELECT *
FROM msdb.dbo.backupset
WHERE name = "abc"

OR

SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ),*
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0

Reference: Where is database .bak file saved from SQL Server Management Studio?

As Avarkx said, add NAME = 'abc Name' to your WITH so that it shows a name when you do a backup. Otherwise it shows NULL. Then run the select without the TOP 100.

Answer 3

To get name of the backup set, you will have to use "NAME" parameter after "WITH" statement which will give the backup set a name. And then you will have value in name column of msdb.dbo.backupset column.