Track down what triggered a SQL backup attempt

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.
ExchangeDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dr. KlahnPrincipal Software EngineerCommented:
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.
0
Darran BrownDBACommented:
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.
0
Darran BrownDBACommented:
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%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ste5anSenior DeveloperCommented:
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.
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check in the SQL Server Error log or in the Windows Event Log as the Backups are logged.
0
ExchangeDAuthor 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.