Helpful SQL Queries for Searching

[amineadil25]

Hello

You can find some SQL queries that can help with searching in large environments with multiple jobs:

QUERY 1: SEARCHING BY DATABASE NAME

This query provides you with the VM name where the database is located, and also the job name used to back up this database.

select Distinct server_name as 'VM NAME',db_name as 'DATABSE NAME', name as 'JOB NAME' from [Backup.Model.GuestDatabasesView]
inner join dbo.ObjectsInJobs on([Backup.Model.GuestDatabasesView].obj_id=dbo.ObjectsInJobs.object_id)
inner join BJobs on(dbo.ObjectsInJobs.job_id=BJobs.id)
where [Backup.Model.GuestDatabasesView].db_name like '%SEARCH_BY_DATABASE%'

QUERY 2 : SEARCHING BY VM NAME

This query provides you with all the backed-up databases on a specific VM machine, using the VM name as a search keyword.

select Distinct server_name as 'VM NAME',db_name as 'DATABSE NAME', name as 'JOB NAME' from [Backup.Model.GuestDatabasesView]
inner join dbo.ObjectsInJobs on([Backup.Model.GuestDatabasesView].obj_id=dbo.ObjectsInJobs.object_id)
inner join BJobs on(dbo.ObjectsInJobs.job_id=BJobs.id)
where [Backup.Model.GuestDatabasesView].server_name like '%SEARCH_BY_VM_NAME%'

QUERY 3: SEARCHING FOR BACKUP FILE LOCATION BY VM NAME

This query provides you with the exact location of the backup files if you are using SOBR and want to know the exact path of any VM backup file

select display_name as 'NOM DE LA VM',parent_rep_name as 'REPERTOIRE PARENT', file_path as 'NOM DU FICHIER', DATEADD(HOUR, 1, [WmiServer.ObjectRestorePointsView].creation_time_utc) as 'DATE DE SAUVEGARDE',name as 'REPERTOIRE SPECIFIQUE' from [WmiServer.ObjectRestorePointsView]

join [WmiServer.BackupRestorePointChunksView] on([WmiServer.ObjectRestorePointsView].storage_id=[WmiServer.BackupRestorePointChunksView].storage_id)

join ReportRepositoriesView on(ReportRepositoriesView.extrepos_id=[WmiServer.BackupRestorePointChunksView].extent_id)

join [Storages.View] on([Storages.View].id=[WmiServer.ObjectRestorePointsView].storage_id)

where display_name like '%SEARCH_BY_VM_NAME%' order by completion_time_utc asc

[PetrM]

Hello Amine and Welcome to Veeam R&D Forums!

I appreciate your willingness to share this information with us, but for all future readers of this topic I have to emphasize that any manual interaction with the Veeam configuration database is not officially supported, and our Support team will not troubleshoot any technical issues that may occur as a result. I will not remove the queries from the post above because they are SELECT-only, but I still strongly recommend that you have a valid configuration backup.

Also, I should note that the fact you need to run such queries makes me think about potential improvements to our reporting system in the future.

Thanks!

[amineadil25]

as you noticed these are purely SELECT queries and they do not modify or affect any data in the configuration database i completely understand and agree that direct interaction with the configuration database is not officially supported and having a valid configuration backup is always important

The main purpose behind these queries was to improve visibility in large environments where multiple backup jobs exist in such infrastructures it can become difficult to quickly identify which databases are protected by which jobs or determine the appropriate job to use during restore operations

I also agree that this could be a very useful enhancement for the reporting and visibility capabilities in future versions of Veeam

Thanks again for your feedback