3

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?

2
  • 3
    Have you check the Windows Task Scheduler?
    – chesterman
    Aug 14, 2020 at 9:35
  • Would a task in this show in the SQL Server Logs?
    – tomBob
    Aug 14, 2020 at 10:56

2 Answers 2

6

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.

2
  • Thanks for this, has cleared up a lot for me. I will try out the Extended Events around the time of the backups and see where I get.
    – tomBob
    Aug 14, 2020 at 11:22
  • Sure thing. When you set up your session, you may want to add actions for NT User, or login (forget the exact names, but you'll see 'em). Aug 14, 2020 at 11:41
6

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.