How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?
On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:

>I went into the Activity Log to look for one thing, and found something else
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT
|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
>
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>
|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set _id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backupset
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote: