This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 55.00000000000001%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Hello everyone :D
My question is:
TYYYYY :D
Hi, @RicardoAnselmoOliveira-7430 Thanks for posting your question in Microsoft Q&A.
In SQL server all database backup information is stored in a system database called msdb. If the account running the query has rights on msdb you should able to see the backup history and running regular query should give you
msdb.dbo.backupset is the table
Regards
Geetha
Hi @RicardoAnselmoOliveira-7430 Thanks for sharing your query
msdb exist in Azure SQL managed instance(PAAS) & SQL server versions(On premises/SQL on VM(IAAS))
msdb database doesn't exist in the Azure SQL database(PAAS)
Regards
Geetha
Hi @GeethaThatipatri-MSFT , ty for your response!
the query im already running is this one:
SELECT DISTINCT getdate() AS timest
,@@servername AS g_instancename
,db.name AS g_databasename
,CASE
WHEN bck.database_name IS NULL
THEN 0
ELSE 1
END AS v_is_bck_configured
FROM sys.databases db
LEFT JOIN msdb.dbo.backupset bck ON db.name = bck.database_name
AND (CONVERT(DATETIME, bck.backup_start_date, 102) >= GETDATE() - 7)
AND (
CHARINDEX(bck.name, 'Veeam') = 0
OR (
PATINDEX('EC2%', @@SERVERNAME) = 1
AND (
bck.user_name LIKE 'NT AUTHORITY%'
OR bck.user_name LIKE 'WORKGROUP%'
)
)
)
AND bck.type = 'D'
WHERE db.name <> 'tempdb'
AND db.state_desc = 'online'
but the result im getting is : "Reference to database and/or server name in 'msdb.dbo.backupset' is not supported in this version of SQL Server.
"
Is this an issue of permissions or something else?
hi @GeethaThatipatri-MSFT , ty again for your clear answer!
So... That means i can't see the information about backups directly on SSMS? Only in Portal/Log Analytics?
Yes, correct, if you are running the above query on the Azure sql database then In Azure sql db, there is no msdb database.
if you find the above reply useful. If yes, do click on 'Mark as answer' link in the above reply. This will help other community members facing similar queries to refer to this solution.
Regards
Geetha