Who TOOK the backup?

Question

Who TOOK the backup?

LearningDBA

SSCertifiable

Points: 6593
More actions
July 1, 2014 at 9:18 am

#296125

The situation is that someone took the backup of the DB (which nobody should), which made the available space on the drive very low. Now nobody knows about it and the manager is pissed. Is there a way to find out who took the backup?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

JeepHound SSCrazy Points: 2963 More actions · Answer 1

I am not sure about this, but I think you can go in like you are restoring the backup. At the backup sets to restore it give a username, I think this is the user that took the backup.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

You can get this information from msdb.dbo.backupset

SELECT name,

description,

user_name,

database_creation_date,

backup_start_date,

backup_finish_date,

backup_size,

database_name,

server_name,

machine_name

FROM msdb.dbo.backupset

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

LearningDBA SSCertifiable Points: 6593 More actions · Answer 3

Alan.B (7/1/2014)
You can get this information from msdb.dbo.backupset
SELECT name,
description,
user_name,
database_creation_date,
backup_start_date,
backup_finish_date,
backup_size,
database_name,
server_name,
machine_name
FROM msdb.dbo.backupset

Thanks. It gave me all the details.

I also right clicked on the backup set -->properties and then details which also gave me the user who took the backup.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Gazareth One Orange Chip Points: 27737 More actions · Answer 4

New Born DBA (7/1/2014)
I also right clicked on the backup set -->properties and then details which also gave me the user who took the backup.

That'll most likely be the output from RESTORE HEADERONLY, another way to do it 🙂

RESTORE HEADERONLY FROM DISK = N'path to .bak file'

Gaz

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

New Born DBA (7/1/2014)
Alan.B (7/1/2014)
You can get this information from msdb.dbo.backupset
SELECT name,
description,
user_name,
database_creation_date,
backup_start_date,
backup_finish_date,
backup_size,
database_name,
server_name,
machine_name
FROM msdb.dbo.backupset
Thanks. It gave me all the details.
I also right clicked on the backup set -->properties and then details which also gave me the user who took the backup.

NP. Glad to help!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

New Born DBA (7/1/2014)
The situation is that someone took the backup of the DB (which nobody should), which made the available space on the drive very low. Now nobody knows about it and the manager is pissed. Is there a way to find out who took the backup?

This is one of many reasons why it's important that only DBAs can do backups, which normally means that only DBAs have SA privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LearningDBA SSCertifiable Points: 6593 More actions · Answer 7

Jeff Moden (7/1/2014)This is one of many reasons why it's important that only DBAs can do backups, which normally means that only DBAs have SA privs.

I completely agree with you, but there are around 4 or 5 users who have SA privs.

I don't know much about it, but I was thinking maybe setting up proxy to help us catch the culprit next time? I mean I already know SA user took the backup but I don't know who logged in as SA.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

You could capture a lot more information using extended events. You can usually capture machine name and other information with it. I'm not sure which event I'd set this one up for. There's not a specific backup related event. You would have to try to capture the T-SQL statement I suppose, but that means a string search to see the command "BACKUP DATABASE." Maybe not the most efficient method. However, it would work.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

george sibbald SSC Guru Points: 104210 More actions · Answer 9

New Born DBA (7/1/2014)
Jeff Moden (7/1/2014)This is one of many reasons why it's important that only DBAs can do backups, which normally means that only DBAs have SA privs.
I completely agree with you, but there are around 4 or 5 users who have SA privs.
I don't know much about it, but I was thinking maybe setting up proxy to help us catch the culprit next time? I mean I already know SA user took the backup but I don't know who logged in as SA.

change the sa password (to something strong) and then disable it. There should not be a need to use sa. If people really need sysadmin grant it to their personal accounts or a windows group they are all members of.

---------------------------------------------------------------------

LearningDBA SSCertifiable Points: 6593 More actions · Answer 10

george sibbald (7/2/2014)change the sa password (to something strong) and then disable it. There should not be a need to use sa. If people really need sysadmin grant it to their personal accounts or a windows group they are all members of.

Already changed the password.

Thanks!

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 11

You can set up a server-side trace to capture who is logging in as sa- well, which hostname they logged in from, anyway. That might be worth doing if you have your suspicions (and are able to link a hostname to an individual).