Duplicated records in sys.master_files caused by credential

Question

Dear all,

I have recently restored several databases on Sql Server 2019 with storage on azure storage acc. from backup files also stored on Azure storage account.

Two of databases have similar name:
ReportServer,
ReportServerTempDB

Credentials we created for sql server to access restore containers have similar container path name on same azure storage account:
https://<myrestoreacc>.blob.core.windows.net/ReportServer
https://<myrestoreacc>.blob.core.windows.net/ReportServerTempDB

and they are presented in sys.credentials table:

Restore scripts are fine and pointing to proper container path for each DB:

--ReportServer

USE [master]

RESTORE DATABASE [ReportServer]
FROM
URL = 'https://<mybckupacc>.blob.core.windows.net/reportserver/reportserver.bak'
WITH
MOVE 'ReportServer' TO 'https://<myrestoreacc>.blob.core.windows.net/reportserver/ReportServer.mdf',
MOVE 'ReportServer_log' TO 'https://<myrestoreacc>.blob.core.windows.net/reportserver/ReportServer_log.ldf'
;

--------------------------------------------------------------------------------------------------

--ReportServerTempDB

USE [master]

RESTORE DATABASE [ReportServerTempDB]
FROM
URL = 'https://<mybckupacc>.blob.core.windows.net/reportservertempdb/reportservertempdb.bak'
WITH
MOVE 'ReportServerTempDB' TO 'https://<myrestoreacc>.blob.core.windows.net/reportservertempdb/ReportServerTempDB.mdf',
MOVE 'ReportServerTempDB_log' TO 'https://<myrestoreacc>.blob.core.windows.net/reportservertempdb/ReportServerTempDB_log.ldf'
;

Problem exists in sys.master_files table for ReportServerTempDB where server wrote 2 rows per each file, both rows are identical except in credential_id field:

Now, this wouldn't bother me much since DBs are restored successfully and they are accessible but we also have DataCollector jobs on this server
which started failing because of this issue.
Is there any way to remove these duplicated rows and how to avoid this kind of issue in future?

Appreciate any answer.

Best regards.

Answer 1

You would have to drop or rename one of the credentials. I ran:

EXEC  sp_helptext 'sys.master_files'

and I found this join:

FROM sys.sysbrickfiles$ f
LEFT JOIN sys.syspalvalues st ON st.class = 'DBFS' AND st.value = f.filestate
LEFT JOIN sys.syspalvalues ft ON ft.class = 'DBFT' AND ft.value = f.filetype
LEFT JOIN sys.credentials cr ON f.pname LIKE cr.name + N'%' COLLATE database_default

f.pname is the value in the phyiscal_name column. Because of the LIKE operation, the name matches both the reportserver and reportservertempdb credentials, and whence the join produces two rows.

I will have to admit that I find this join a little iffy.