Who’s Backing Up That Database?

A question came up on Twitter recently– how you can you figure out where a rogue backup is coming from?

I’ve run into this question with clients before. In an enterprise environment where backup tools come and go, it’s not unusual to find backups being run by some mysterious account.

By the way, when’s the last time you changed your service account password?

Uncomfortable discussion time: should something unknown out there have permission to do this?

Lecture over, let’s track down the culprit

If the backup command is failing, it’s probably generating a level 16 error much like this:

The good news is that you can easily set up an alert to notify you right away when these problems happen.

The bad news is that the alert doesn’t tell you who tried to run the backup. Neither does the SQL Server Error log, it just says:

But, uh, who exactly is the backup application?

If the Backup is Failing, You Can Trace Based On the Error

SQL Server doesn’t capture the details for Severity 16 alerts by default, but you can trace failing commands in your SQL Server like I show in this earlier post. In this case, we want to customize the Extended Events Trace so we can get more information about who is trying to run the backup.

Here’s an example script to set up a trace (SQL Server 2012 and higher only):

Now, when my backup fails, I can open up my trace file and see info like this:

backup being run from the sa account

It’s in the walls!

IF THE BACKUP IS Succeeding, YOU CAN TRY ANOTHER TRICK

You might have a situation where the backup isn’t failing, you just aren’t sure who’s running it and what permissions they’re using. Maybe you notice a message like this in your SQL Server Error Log:

Someone’s backing up my master database, but writing it out to NUL: and throwing it away? That’s crazy! Why would someone do that? I need to track them down right away. I think a little user action is required.

In this case, since the backup command succeeded, I can get more information right away from SQL Server’s default trace. It saw the name of the host I was running from (“UNLIMITED”), the fact that I was running under the SA account, and even the fact that I was dumping the data to NUL:

backup event in the default trace

The default trace sees the terrible thing I did

While we’re on the subject of backups…

Brent says: you’d be surprised how often we hear, “wait a minute, why is IO freezing every fifteen minutes on this server? Who’s taking all these snapshot backups of this VM?”

Previous Post
How to Set Up Standard Edition Always On Availability Groups in SQL Server 2016
Next Post
Features SQL Server Needs to Add (And Drop)

10 Comments. Leave new

  • Bill Hughes
    June 2, 2015 9:21 am

    Thanks for this post! I have a situation recently just like this. I am trying to use the script, the sqlserver.database_name doesn’t work in SQL 2008R2 I had to switch to database_id. Also, package0.event_file is failing on me. Is there an alternative for that command as well?

    Reply
  • Stephen Merkel
    June 2, 2015 11:21 am

    “Brent says: you’d be surprised how often we hear, “wait a minute, why is IO freezing every fifteen minutes on this server? Who’s taking all these snapshot backups of this VM?”

    I would love to read more on this topic. As a DBA I have a pretty good handle on full and log backups and the concept of an unbroken chain of backups needed for a point in time restore. My sysadmins on the other hand, like to backup VMs using Backup Exec. And when I see in the SQL log that database XX has been backed up to some virutal device with a guid for a name, I know there is trouble because 1)sql server thinks I just restarted the restore chain with a new full backup, but 2) that virutal device seems not to be a real ‘thing’ that can be gotten and restored (like I would a .bak file). My guess is that this is a very widespread issue, again for two reasons 1) use of BE is common, and 2) It has been my experience that sysadmins are not well versed in the nuances of SQL backup and recovery. This issue of BE breaking the restore chain seems to fall into the gap between what DBAs know and what sysadmins know. I have so far found little discussion of this issue online.

    Reply
    • Stephen – if you’re experiencing it a lot, why not start writing your own blog posts about it? We strongly encourage folks who have a viewpoint to jump in, do research, and start explaining what they’re seeing. It’s a great way to gain knowledge and share with others!

      Reply
    • Kendra Little
      June 2, 2015 11:53 am

      Just a quick clarification on one thing – in terms of the restore chain, the issue if you take extra full backups is that it resets the differential base if you don’t use copy_only.

      If you’re not taking differential backups, which you don’t mention, the extra full backup doesn’t present any problems with your restore chain, as long as you still have the full and log backups you took.

      Reply
  • Denise Crabtree
    June 2, 2015 12:04 pm

    There is also information in the msdb..backup… tables. Below is my “Where are the backups” query that I use for this purpose. The Physical file location will be a wierd guid like string if it is a third party like TDP or Acronis. Yes, this only works if the third party software writes to the system, but it has to to maintain the backup chain, so most of them do.

    ————————————————————-
    — 2005+ version
    ————————————————————-
    — Find Where DB Backups Went Physical Location
    — Variables:
    — @dbname is optional if blank will return all databases
    — @daysPast variable sets “start date” predicate
    — @backupType is required
    — @backuptype values
    — D –> FULL
    — I –> DIff or incrimental
    — L –> Log backups
    ————————————————————-
    DECLARE @dbname sysname
    DECLARE @dayspast decimal(4,2) — can use decimal value
    DECLARE @backupType char(1)

    SET @dbname = ”
    SET @dayspast = 3.5 — .1 retuns 2.4 hours or 2 hours 24 minutes — 144 minutes.
    SET @backupType = ‘D’

    SELECT
    @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Start]
    ,msdb.dbo.backupset.backup_finish_date [Finish DateTime]
    ,msdb.dbo.backupset.user_name [Login used to Backup]
    ,CASE
    WHEN DATEDIFF(ss, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date)/(24*3600) > 0
    THEN CAST(DATEDIFF(ss, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date)/(24*3600) AS nvarchar) + ‘.’
    ELSE ”
    END + RIGHT(CONVERT(nvarchar,msdb.dbo.backupset.backup_finish_date – msdb.dbo.backupset.backup_start_date,121),12) AS Duration
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]
    ,msdb.dbo.backupmediafamily.family_sequence_number [File Sequesnce#]
    ,msdb.dbo.backupset.position [BackupPosition in File]
    ,CASE
    WHEN msdb.dbo.backupset.type = ‘D’
    THEN ‘FULL’
    WHEN msdb.dbo.backupset.type = ‘I’
    THEN ‘Diff’
    WHEN msdb.dbo.backupset.type = ‘L’
    THEN ‘Logs’
    END [Backup Type]
    ,GETDATE() [Report Date]
    FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
    WHERE
    msdb.dbo.backupset.type = @backupType
    AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    and msdb.dbo.backupset.backup_start_date > getdate() – @dayspast
    AND master.sys.sysdatabases.name like ‘%’ + @dbname + ‘%’
    ORDER BY
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type

    Reply
    • Hi Guys, In the script above , If I would like to Alias the wierd GUID name to let’s say “TIVOLI” so that I know for sure that those certain backups are being backed up to TIVOLI, What part of the script do I tweak? I am quite new to SQL server and still working on learning more .

      Reply
  • Amazing timing! An alert on this very issue hit my inbox a few hours before the email on this post. Unfortunately I didn’t have those extended events running on the 2008 R2 server, but I am trying those out now.

    Thanks!
    Tim

    Reply
  • Thank you for answering my question for me 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}