Track down what triggered a SQL backup attempt

ExchangeD
ExchangeD used Ask the Experts™
on
In the SQL Server logs there was an error event when it attempted to backup a transaction log to a file to a non existent path. The thing is, we don't know what triggered the backup attempt. Poured over the maintenance logs and SQL Agent Jobs and cannot find anything. Is there a way to track down what triggered this backup request? If not for this one, then something to set up in case it happens again? Everyone who has access to the server insists they didn't do it. It is possible that it is a stored procedure or something that is programmed to backup the log to a file when triggered (maybe for troubleshooting) but I don't know how to track that sort of thing down. The path it attempted to use is very unique so if there is a way I could do some sort of comprehensive search that would work.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dr. KlahnPrincipal Software Engineer

Commented:
The path it attempted to use is very unique

Possibility:  Somebody has enough access to the system to trigger the backup and wants to steal info.

Check to see who was logged in when the attempt occurred.

Do a full virus sweep on the system using at least two different antiviruses, then run Malwarebytes, then run Spybot - S&D.

Do a file content search and look for the file pathname inside all files, including system files and archives.  Search for the full pathname and for likely substrings.

If your web server is located on the same system, move it to different hardware (even if it is virtualized).  This is good general procedure and will eliminate somebody getting into the system through a server exploit.
Right click the database and run the backup and restore report. It should tell you who ran the backup. If it's not clear it may be a 3rd party backup tool or the application that uses the database. The app may have an admin tool that can take backups, you would need to speak the people that use that. Or 3rd party SQL backup products can be scheduled outside of SQL inc. virtual server backup tools like Veeam. Most of these use the SQL VSS Writer service  so you could disable that to stop other backups if they are causing issues with your sql backup chain but also providing you're confident you don't need those 3rd part backups. It's hard to track them down from within SQL. The backup set name and path are usually the best clues in SQL, they hopefully note the backup product. Speaking with anyone who could be involved in those products is your main hope.
and here's a quick script to search the items like stored procedures in a database
 
 SELECT OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE definition LIKE '%backup%'
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer
Commented:
Everyone who has access to the server insists they didn't do it.
You need, for sure, to review our permission concepts.

Being an administrator of the OS must exclude being a SQL Server sysadmin automatically.
That's why SQL Server has additional db_owner  and especially the db_backupoperator role. Review who is in which group.

SQL Server permissions should be granted explicitly. Ideally per audited AD security groups.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Check in the SQL Server Error log or in the Windows Event Log as the Backups are logged.
ExchangeDIT Professional

Author

Commented:
Thanks all. Using the query from Darran we were able to reverse engineer the fact that someone was programmatically triggering a log backup trying to gather info when a certain condition was met. I am no SQL guru but this seems like a bad way to go about troubleshooting an occurrence in a DB since wont this mess up the normal restore sequence if we needed to roll back? Seems like you would want to trap to maybe another DB or something.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial