0

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.

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.

1

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.

0

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.

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.