How to check who performed a SQL Server database backup

In this post I would like to share a simple way of extracting latest database backup including the user that has initiated it. Generally SQL Server engine does not store such info unless you have some custom piece of code (such as triggers) or you are using auditing in SQL Server 2008 and above. As you know each backup triggers an event that is stored in the application log of the operating system. This event includes the message that is written in the SQL Server error log and additionally has an user name, that triggered the event. Based on that and using PowerShell here is a simple list that returns newest 2000 backup events (full, differential or log) for all databases, sorting them descending by time. Columns returned are Time (the time the backup completed), Username (the user performed the backup) and Message(the message, which includes the type of backup and the database).

[sourcecode language=”powershell”]
Get-EventLog "Application" -newest 2000 | Where-Object {$_.EventID -eq 18264 -or $_.EventID -eq 18265 -or $_.EventID -eq 18270} |sort -descending TimeWritten |
Format-Table TimeWritten,UserName,Message -auto
[/sourcecode]

As you can see I am querying directly the OS Application log and filtering three specific events:

  • EventID 18264 – Database full backup event
  • EventID 18265 – Database log backup event
  • EventID18270 – Database differential backup event

Also keep in mind that Backup and Restore events are not written to SQL Server Error log and NT Application event log if trace flag 3226 is enabled. So before trying to find out who performed a certain backup, check if those events are logged at first placeSmile

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.