SQL query to know which user taked database backup.

Question

SQL query to know which user taked database backup.Please respond ASAP.

Answer 1

You can identify users who took database backups by querying the system tables and views that store backup history information. The most useful system view for this is msdb.dbo.backupset, and you can join it with msdb.dbo.backupmediafamily for more context.

Here’s a query to retrieve recent database backups along with the user who performed the backup:

SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type AS backup_type,
    CASE bs.type
        WHEN 'D' THEN 'Database'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
        ELSE 'Other'
    END AS backup_type_desc,
    bs.user_name,
    bs.server_name,
    bs.machine_name,
    bs.program_name,
    bmf.physical_device_name
FROM 
    msdb.dbo.backupset bs
JOIN 
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
ORDER BY 
    bs.backup_finish_date DESC;

• user_name: The login used to perform the backup.
• program_name: The program that initiated the backup (e.g., SQL Server Agent, SSMS).
• physical_device_name: Path to the backup file.
• backup_type: Type of backup (D = Full, I = Differential, L = Log).

This query assumes you have access to the msdb system database. If backups are done using third-party tools, the program_name may help identify that.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin