what is the source of these backup files

Question

what is the source of these backup files

Rishabh Patil 121

Hi All,

I am dealing with a strange issue where in one of the instance, in one folder some job/something else is generating db backup files. I have checked all the sql agent jobs but none points to that folder location.

These backup files are occupying space in terabytes and we were not able to find the culprit yet.

I have checked other servers which might have pointed to the path N'H:\Backup\' but they are not doing anything in there.

Is there a better way to find the source of those backup files?

Any help would be appreciated.
tks

Answer 1

Olaf Helper 28,701

Though we dont find that job when logged in as sa user

As SysAdmin "sa" you can see everything in SQL Server, no need to logon with a different account.

That the service account as user issues the backup do not necessarily mean, it's done by a SQL Server-Agent job, it can also be done with Windows Scheduler, where the service account is assigned to the schedule.
See Schedule and automate backups of SQL Server databases in SQL Server Express

Erland Sommarskog 77,496 Reputation points MVP

2021-09-09T12:11:42.03+00:00

In addition to what Olaf says. the backup command may also be submitted from an application, which could be a service, If you have a Trace, you should have the colunm ClientProcessId, which is the PID of the Windows process that submitted the command. If the command was submitted from a service that PID may still be there. (I say may, as the service may have spawned a subprocess for the task.)

Answer 2

Olaf Helper 28,701

generating db backup files

A .BAK file can be anything, just a file extension, so first validate if it's really a DB backup file using RESTORE Statements - HEADERONLY (Transact-SQL) or use SSMS.

Rishabh Patil 121 Reputation points

2021-09-08T12:03:59.843+00:00

Okay, I will test it as first thing now. thanks
Rishabh Patil 121 Reputation points

2021-09-08T13:51:27.547+00:00

II restored one and checked that these are SQL db backup files but we don't sure of which server and database .
Erland Sommarskog 77,496 Reputation points MVP

2021-09-08T21:12:53.997+00:00

RESTORE HEADERONLY gives both the name of the database and the machine the backup was generated on. The instance name is not there, though.

Answer 3

Tom Phillips 17,631

You would not see the directory in the SQL Agent job if it was in a maintenance plan.

Answer 4

Rishabh Patil 121

I have setup a trace between 6 am to 7 am to catch the Audit backup/restore event. Hope I find it in login name! I will update. thanks

AmeliaGu-MSFT 13,896 Reputation points Microsoft Vendor

2021-09-09T06:31:54.213+00:00

Hi MeRishabh-5801,

Are there any updates?
Did you capture some useful information about who and how to backup database?
If you have any questions, please feel free to let us know.

Best Regards,
Amelia
Rishabh Patil 121 Reputation points

2021-09-09T10:15:17.317+00:00

Hi,

In sql trace this morning, we have got one sql agent account in hostname which is requesting those backup.

Though we dont find that job when logged in as sa user, we are trying to figure how to login using that sql agent account.

Any suggestions?

Answer 5

Rishabh Patil 121

Hi All,

Thanks for all the helpful replies.

We found that backup was requesting from one data warehouse etl, as the etl triggered at the same time that backup file created.

We have asked ETL guys to remove that code from there, and also we have deleted all the files from the folder.

what is the source of these backup files

4 additional answers

Activity