SQL Server full database backup without a job

Question

I am trying to work out where I can modify a schedule for a backup job that is running. The SQL Server was setup by a team before it was handed to us.

We have a set of maintenance jobs in our SQL Server Agent which I can modify, but looking through the server logs there is another full backup that runs, backing everything up into a blob storage.

The issue is, I cannot find where this is being scheduled. There is no job that I can see in the Agent that is executing this backup and there is no maintenance plan in the management folder either.

Is there something I am missing? Or any way for me to find what is executing this backup?

Answer 1

A backup command can be run from just about anywhere and scheduled using any kind of scheduling tool. So, while SQL Agent is the most common method, it's not the only one. To determine what's happening when this backup runs, I'd suggest creating an Extended Events session to capture the queries. You'll be able to see the login that is being used. You may, depending on the connection settings, also be able to see the application making the call. At the very least, you can see the machine within your network that the call is coming from.

Because Extended Events can return a lot of data, I'd suggest turning it on only for the time that the calls are being made based on the log information you have. To search through the data, you can use the Live Data Explorer window within SSMS. I have a blog post on getting started with that tool that might help.

Answer 2

Although deprecated feature, the default trace will have additional information as to the source of backup commands. Below is an example query below to extract Audit Backup/Restore Events from all available trace files.

SELECT tt.*
FROM sys.traces AS t
CROSS APPLY fn_trace_gettable(
    REVERSE(N'crt.gol' + SUBSTRING(REVERSE(t.path), CHARINDEX(N'\', REVERSE(t.path)), 128)), default) AS tt
JOIN sys.trace_events AS te ON
    te.trace_event_id = tt.EventClass
JOIN sys.trace_subclass_values AS tesv ON
    tesv.trace_event_id = tt.EventClass
    AND tesv.subclass_value = tt.EventSubClass
WHERE
    t.is_default = 1
    AND tt.ObjectType = 16964
    AND te.name = 'Audit Backup/Restore Event';