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:

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?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outside SQL Server that
initiates the backup (logs on and executes the backup command). Most often, this is an SQL Server
Agent job (like for instance a maint plan).
> 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.
I'm confused? Are you copying the database files directly? Or are you using some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to find out, but be aware
that your snapshot backups (if such are done) will not be proper while the service is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>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:
> >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
>|||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:
> 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:
> >
> > >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
> >

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?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outsid
e SQL Server that
initiates the backup (logs on and executes the backup command). Most often,
this is an SQL Server
Agent job (like for instance a maint plan).

> Our tech just said it could be nothing more than the tape backups of the S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> to the entries made from my regularly scheduled backups.
I'm confused? Are you copying the database files directly? Or are you using
some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to
find out, but be aware
that your snapshot backups (if such are done) will not be proper while the s
ervice is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>I went into the Activity Log to look for one thing, and found something els
e
> very troubling. Starting one week ago, something is triggering weekly ful
l
> 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 faile
d
> 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 S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> 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 th
e
> 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 els
e
>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 backups
et
> 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:
>

How do I determine the backup date of a restored Database..?

Hi All,

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...

For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...

Any help will be much appreciated.. Thanks in advance.
- AravinQuery Builder (querybuilder@.gmail.com) writes:

Quote:

Originally Posted by

I have to a few backups restored from a set of backup files. The
backup files have been removed from the drive because of some storage
constrains...
>
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
>
Any help will be much appreciated.. Thanks in advance.

If the backups were taken on the same server, you may be able to dig
this out from the tables in msdb. You would have to start with
restorehistory and go backwards from there. I'm offering a query,
because I have worked very little with these tables myself. But they
are documented in Books Online, althoughly fairly briefly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- Aravin
Hello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegro ups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>

How do I determine the Backup Date of a restored Database from a backup File

Hi All,
I am in a delima here. I have to a few backups restored from a set of
backup file. The backup files have been removed from the drive because
of some storage constrains...
For some data comparison reasons, I need to figure out the actual date
of these backup files that is used to restore these databases. Is
there any system table I can query to figure the actual backup times
of these databases...
Any help will be much appreciated.. Thanks in advance.
- AravinHello,
Take a look into below system tables in MSDB database.
BACKUPSET
BACKUPFILE
Please do not post seperately in different groups.
Thanks
Hari
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
> Hi All,
> I am in a delima here. I have to a few backups restored from a set of
> backup file. The backup files have been removed from the drive because
> of some storage constrains...
> For some data comparison reasons, I need to figure out the actual date
> of these backup files that is used to restore these databases. Is
> there any system table I can query to figure the actual backup times
> of these databases...
> Any help will be much appreciated.. Thanks in advance.
> - Aravin
>|||... and there are also similar RESTORE tables in the msdb database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OnTgkxbRHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hello,
>
> Take a look into below system tables in MSDB database.
> BACKUPSET
> BACKUPFILE
>
> Please do not post seperately in different groups.
>
> Thanks
> Hari
> "Query Builder" <querybuilder@.gmail.com> wrote in message
> news:1170302235.450584.73840@.a34g2000cwb.googlegroups.com...
>> Hi All,
>> I am in a delima here. I have to a few backups restored from a set of
>> backup file. The backup files have been removed from the drive because
>> of some storage constrains...
>> For some data comparison reasons, I need to figure out the actual date
>> of these backup files that is used to restore these databases. Is
>> there any system table I can query to figure the actual backup times
>> of these databases...
>> Any help will be much appreciated.. Thanks in advance.
>> - Aravin
>