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).

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s