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:
|
1 2 3 4 |
Msg 911, Level 16, State 11, Line 1 Database 'idontexist' does not exist. Make sure that the name is entered correctly. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. |
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:
|
1 |
BACKUP failed to complete the command BACKUP DATABASE idontexist. Check the backup application log for detailed messages. |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE EVENT SESSION [Sev 16 and Higher Errors] ON SERVER --Just using the error_reported event ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username, sqlserver.session_id, sqlserver.sql_text, sqlserver.username) WHERE ([severity]>=(16) ) ) --Customize the target ADD TARGET package0.event_file(SET filename=N'S:\XEvents\Traces\Sev16AndHigher.xel') --This is just using the defaults --Note that startup state is off WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF) GO |
Now, when my backup fails, I can open up my trace file and see info like this:
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:
|
1 |
Database backed up. Database: master, creation date(time): 2015/05/02(10:43:10), pages dumped: 59538, first LSN: 1098:34688:120, last LSN: 1098:34768:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'NUL:'}). This is an informational message only. No user action is required. |
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:
While we’re on the subject of backups…
- Read more about backup best practices here on our blog
- Drink from the fire hose about backups and availability in our week long Senior DBA course
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?”
10 Comments. Leave new
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?
Great question. I edited the post to say this works on SQL Server 2012 and higher.
For SQL Server 2008R2, I’d personally look at scripting out a server side trace, because I’m lazy. My previous post on this does have an XEvents query that’s 2008R2 friendly, though, if I recall– you could possibly just add a few columns to that: https://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server. (I’m at jury duty today, so my testing powers on that script are limited.)
Thanks! I’ll look into it.
“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.
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!
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.
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
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 .
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
Thank you for answering my question for me 🙂