Cannot find where backups are coming from!

SQL Server Administration
1

Hey everyone,

I cannot for the life of me find where a certain set of database backups are coming from at a certain time each day. I have a few databases that are backing up at 5AM each day and I can't find how they are doing that. We have no maintenance plans and the SQL Agent jobs are writing backups elsewhere (network share). I have checked event viewer and nothing in there.

Any ideas? Please

2

First, look in msdb.dbo.backupset. That should give you the login that created the backup. That might help you track it down.

3

If you don't see anything in the SQL Server Agent, it could be another scheduler, such as a Windows scheduler, or another job scheduling system if you have one.

You can find the username that was used to make the backups using the following query; one of the columns is user_name. It also has start times and end times

SELECT * FROM msdb.dbo.backupset
4

Well, one domain account is used for all backups, so I did that query above and found the backup entries, but it just lists the same user as all of the other backups do :confused:

Anything else? I checked Task Scheduler but nothing :confused:

5

A third-party utility - such as BackupExec, CommVault, etc...check with your system admins to see if they are running server backups and what utility they are using. Most likely that utility has been configured to perform backups.

6

Sorry if this is seeming more impossible. I am speaking with the DBA's and they say we aren't using any third party backup apps such as Symantec or CommVault. Not in this environment, anyway.

I am checking the apps installed to the server and it's pretty basic. Is there some alternate config for VSS Writer? I thought I read something on the web about that, too.

Mysterious database backups continue! And the drive they are going to, nothing should go there! It's temp space automatically added to all Azure VM's!!

7

Try using Profiler or Trace on that account and hopefully, it provides further information for your investigation.

8

The tape backup on some of our servers seems to be aware of SQL Server and does a snapshot with a device_type of 7.
Some SANS may do something similar.

SELECT TOP (1) S.backup_start_date, S.backup_finish_date, M.physical_device_name
FROM msdb.dbo.backupset S
    JOIN msdb.dbo.backupmediafamily M
        ON S.media_set_id = M.media_set_id
WHERE S.[type] = 'D' -- Full Backup (I = Diff, L = Log)
    AND S.database_name = 'YourDB'
    -- 7 is the snapshot done by tape backup
    AND M.device_type = 2
ORDER BY S.backup_finish_date DESC;
9

If the servers are backed up in VMWare - they will also perform snapshots of the databases in SQL Server. Check with your system administrators to see if there is anything they are running.

I wouldn't be surprised if the system administrators are running multiple types of backup utilities on every server - and that the DBA's are not aware of those utilities being used.

10

The backups should also appear in the backup guid for SQL Server (actually, the restore screen has more information... just remember to hit CANCEL instead of OK when you're done looking). In that screen, you might also see the name of the product or some other hint that may lead you to what is doing the actual backup. This information is also available in one of the backup/media tables in MSDB. I just can't remember which one off the top of my head.