Changes since last backup?

[bobdirls]

Hi,

Is there any way of telling if a database or table has been updated,
altered or deleted from since the last backup was made in SQL server.
Windows does not correctly report .mdf file size changes until a
database is brought offline so it seems impossible to work on physical
file size changes as reported in windows.

Ive done some research in this group and people have talked about a
status bit which is set in sysfiles. This does not seem to work for
me. Are there any other suggestions?

Differential backups are ruled out due to working restrictions.

Any help or suggestions are greatly received.

Thanks

Bob

[Greg D. Moore \(Strider\)]

Re: Changes since last backup?


"bobdirls" <bobdirls@hotma il.com> wrote in message
news:157c1396.0 312110458.72bbb 518@posting.goo gle.com...[color=blue]
> Hi,
>
> Is there any way of telling if a database or table has been updated,[/color]

I don't think so.

What exactly is your goal?

[color=blue]
> altered or deleted from since the last backup was made in SQL server.
> Windows does not correctly report .mdf file size changes until a
> database is brought offline so it seems impossible to work on physical
> file size changes as reported in windows.
>
> Ive done some research in this group and people have talked about a
> status bit which is set in sysfiles. This does not seem to work for
> me. Are there any other suggestions?
>
> Differential backups are ruled out due to working restrictions.
>
> Any help or suggestions are greatly received.
>
> Thanks
>
> Bob[/color]

[Terri]

Re: Changes since last backup?

Hi,

If you are talking about INSERT, UPDATE, or DELETE statements against a
table (that is modification of data) then you could use triggers to write
new records to an audit table whenever a record is added, modified, or
deleted.

If you are talking about changes to the structure of the table (ALTER TABLE)
then perhaps someone else could help.

Good luck

"bobdirls" <bobdirls@hotma il.com> wrote in message
news:157c1396.0 312110458.72bbb 518@posting.goo gle.com...[color=blue]
> Hi,
>
> Is there any way of telling if a database or table has been updated,
> altered or deleted from since the last backup was made in SQL server.
> Windows does not correctly report .mdf file size changes until a
> database is brought offline so it seems impossible to work on physical
> file size changes as reported in windows.
>
> Ive done some research in this group and people have talked about a
> status bit which is set in sysfiles. This does not seem to work for
> me. Are there any other suggestions?
>
> Differential backups are ruled out due to working restrictions.
>
> Any help or suggestions are greatly received.
>
> Thanks
>
> Bob[/color]

[bobdirls]

Re: Changes since last backup?

Thanks for the replies.

I cant really use triggers as they only work at a table level. We have
about 40 databases each with at least 50 tables.
My goal is to replace the current back regime so its more efficient.
At the moment everything backs up every night. We have a few databases
that are over 40 GB, as they grow the length of time to do backups is
eating into daytime processing.

Basically I want to be able to interrogate windows or SQL server to
see if a database file has been altered since the last backup. If it
has been altered then run a backup job, if not then dont. There will
be times when all databases have been altered and will need backing
up, but it would be nice to only have databases being backed up that
have been altered.

Im trying to push to use differential backups at least during the week
but there is some opposition to that, is another solution possible?

Thanks again for the responses.



"Terri" <Terri@spamaway .om> wrote in message news:<bracug$c6 g$1@reader2.nmi x.net>...[color=blue]
> Hi,
>
> If you are talking about INSERT, UPDATE, or DELETE statements against a
> table (that is modification of data) then you could use triggers to write
> new records to an audit table whenever a record is added, modified, or
> deleted.
>
> If you are talking about changes to the structure of the table (ALTER TABLE)
> then perhaps someone else could help.
>
> Good luck
>
> "bobdirls" <bobdirls@hotma il.com> wrote in message
> news:157c1396.0 312110458.72bbb 518@posting.goo gle.com...[color=green]
> > Hi,
> >
> > Is there any way of telling if a database or table has been updated,
> > altered or deleted from since the last backup was made in SQL server.
> > Windows does not correctly report .mdf file size changes until a
> > database is brought offline so it seems impossible to work on physical
> > file size changes as reported in windows.
> >
> > Ive done some research in this group and people have talked about a
> > status bit which is set in sysfiles. This does not seem to work for
> > me. Are there any other suggestions?
> >
> > Differential backups are ruled out due to working restrictions.
> >
> > Any help or suggestions are greatly received.
> >
> > Thanks
> >
> > Bob[/color][/color]

[Simon Hayes]

Re: Changes since last backup?

bobdirls@hotmai l.com (bobdirls) wrote in message news:<157c1396. 0312120023.2ac4 6e2a@posting.go ogle.com>...[color=blue]
> Thanks for the replies.
>
> I cant really use triggers as they only work at a table level. We have
> about 40 databases each with at least 50 tables.
> My goal is to replace the current back regime so its more efficient.
> At the moment everything backs up every night. We have a few databases
> that are over 40 GB, as they grow the length of time to do backups is
> eating into daytime processing.
>
> Basically I want to be able to interrogate windows or SQL server to
> see if a database file has been altered since the last backup. If it
> has been altered then run a backup job, if not then dont. There will
> be times when all databases have been altered and will need backing
> up, but it would be nice to only have databases being backed up that
> have been altered.
>
> Im trying to push to use differential backups at least during the week
> but there is some opposition to that, is another solution possible?
>
> Thanks again for the responses.
>[/color]

<snip>

There are a couple of things that aren't completely clear - at least
to me - from your post. First, why are your backups "eating into
daytime processing"? Backups can run while users are accessing the
database as usual, so unless you take the databases offline to back
them up (which is unnecessary) then this shouldn't be a major problem.
Unless perhaps you have some awkward I/O issues or OEM backup software
to work around.

If you really do need to speed up the backup, one possible approach is
to back up in parallel to several different disk files or tapes. This
should be much faster than backing up to one place, but of course you
then have to manage the increased number of backup files.

Second, it's not clear what the benefit is of only backing up a
database when it has been modified. The only real benefit I can think
of would be to save space on your backup media, but that would be at
the expense of increased backup management, and disk space is much
cheaper than DBA time. In any event, differential backups are designed
to do exactly that - why not let MSSQL do the work for you, instead of
trying to build your own solution?

If this isn't helpful, perhaps you could post some more information
about why your backups are currently affecting daytime processing;
what you hope to gain by only backing up changed databases; and why
your organization is reluctant to consider differential backups.

Simon

[bobdirls]

Re: Changes since last backup?

Thanks for the replies.

I guess that means that there is no way of doing it then?

We need to save time on backups as we only have a short window
overnight to do the backups. Our shop is not the sole owner of the box
so we have to take other peoples needs into account.

I agree differential backups are probably the solution, but
unfortunately are not appropriate. Dont ask me why but they have been
ruled out totally for the time being.

Our databases are built during the month then once the data is
complete they are released to live. It would therefore be nice to only
backup databases when they differ from the current backup. This would
save time for doing the backups in the limited time we have. I was
looking for a way around this without using differential backups.

Basically there are a lot of both technical and business restraints on
what we use our box for. I havent been able to find a solution to my
original solution so I may have to recommend that changes are made in
the way the database is managed at a higher level.

Thanks for your help.



sql@hayes.ch (Simon Hayes) wrote in message news:<60cd0137. 0312120652.59a9 09e4@posting.go ogle.com>...[color=blue]
> bobdirls@hotmai l.com (bobdirls) wrote in message news:<157c1396. 0312120023.2ac4 6e2a@posting.go ogle.com>...[color=green]
> > Thanks for the replies.
> >
> > I cant really use triggers as they only work at a table level. We have
> > about 40 databases each with at least 50 tables.
> > My goal is to replace the current back regime so its more efficient.
> > At the moment everything backs up every night. We have a few databases
> > that are over 40 GB, as they grow the length of time to do backups is
> > eating into daytime processing.
> >
> > Basically I want to be able to interrogate windows or SQL server to
> > see if a database file has been altered since the last backup. If it
> > has been altered then run a backup job, if not then dont. There will
> > be times when all databases have been altered and will need backing
> > up, but it would be nice to only have databases being backed up that
> > have been altered.
> >
> > Im trying to push to use differential backups at least during the week
> > but there is some opposition to that, is another solution possible?
> >
> > Thanks again for the responses.
> >[/color]
>
> <snip>
>
> There are a couple of things that aren't completely clear - at least
> to me - from your post. First, why are your backups "eating into
> daytime processing"? Backups can run while users are accessing the
> database as usual, so unless you take the databases offline to back
> them up (which is unnecessary) then this shouldn't be a major problem.
> Unless perhaps you have some awkward I/O issues or OEM backup software
> to work around.
>
> If you really do need to speed up the backup, one possible approach is
> to back up in parallel to several different disk files or tapes. This
> should be much faster than backing up to one place, but of course you
> then have to manage the increased number of backup files.
>
> Second, it's not clear what the benefit is of only backing up a
> database when it has been modified. The only real benefit I can think
> of would be to save space on your backup media, but that would be at
> the expense of increased backup management, and disk space is much
> cheaper than DBA time. In any event, differential backups are designed
> to do exactly that - why not let MSSQL do the work for you, instead of
> trying to build your own solution?
>
> If this isn't helpful, perhaps you could post some more information
> about why your backups are currently affecting daytime processing;
> what you hope to gain by only backing up changed databases; and why
> your organization is reluctant to consider differential backups.
>
> Simon[/color]

[Greg D. Moore \(Strider\)]

Re: Changes since last backup?


"bobdirls" <bobdirls@hotma il.com> wrote in message
news:157c1396.0 312130128.5c4aa 4db@posting.goo gle.com...[color=blue]
> Thanks for the replies.
>
> I guess that means that there is no way of doing it then?
>
> We need to save time on backups as we only have a short window
> overnight to do the backups. Our shop is not the sole owner of the box
> so we have to take other peoples needs into account.[/color]

Again, as a previous poster said, I'm not clear on why you have a short
window. Normally backups do not interfere with normal operations.

[color=blue]
>
> I agree differential backups are probably the solution, but
> unfortunately are not appropriate. Dont ask me why but they have been
> ruled out totally for the time being.
>[/color]

Strange, but I'll take your word for it.

[color=blue]
> Our databases are built during the month then once the data is
> complete they are released to live. It would therefore be nice to only
> backup databases when they differ from the current backup. This would
> save time for doing the backups in the limited time we have. I was
> looking for a way around this without using differential backups.
>
> Basically there are a lot of both technical and business restraints on
> what we use our box for. I havent been able to find a solution to my
> original solution so I may have to recommend that changes are made in
> the way the database is managed at a higher level.
>[/color]

I think you will have to.

[color=blue]
> Thanks for your help.
>
>
>
> sql@hayes.ch (Simon Hayes) wrote in message[/color]
news:<60cd0137. 0312120652.59a9 09e4@posting.go ogle.com>...[color=blue][color=green]
> > bobdirls@hotmai l.com (bobdirls) wrote in message[/color][/color]
news:<157c1396. 0312120023.2ac4 6e2a@posting.go ogle.com>...[color=blue][color=green][color=darkred]
> > > Thanks for the replies.
> > >
> > > I cant really use triggers as they only work at a table level. We have
> > > about 40 databases each with at least 50 tables.
> > > My goal is to replace the current back regime so its more efficient.
> > > At the moment everything backs up every night. We have a few databases
> > > that are over 40 GB, as they grow the length of time to do backups is
> > > eating into daytime processing.
> > >
> > > Basically I want to be able to interrogate windows or SQL server to
> > > see if a database file has been altered since the last backup. If it
> > > has been altered then run a backup job, if not then dont. There will
> > > be times when all databases have been altered and will need backing
> > > up, but it would be nice to only have databases being backed up that
> > > have been altered.
> > >
> > > Im trying to push to use differential backups at least during the week
> > > but there is some opposition to that, is another solution possible?
> > >
> > > Thanks again for the responses.
> > >[/color]
> >
> > <snip>
> >
> > There are a couple of things that aren't completely clear - at least
> > to me - from your post. First, why are your backups "eating into
> > daytime processing"? Backups can run while users are accessing the
> > database as usual, so unless you take the databases offline to back
> > them up (which is unnecessary) then this shouldn't be a major problem.
> > Unless perhaps you have some awkward I/O issues or OEM backup software
> > to work around.
> >
> > If you really do need to speed up the backup, one possible approach is
> > to back up in parallel to several different disk files or tapes. This
> > should be much faster than backing up to one place, but of course you
> > then have to manage the increased number of backup files.
> >
> > Second, it's not clear what the benefit is of only backing up a
> > database when it has been modified. The only real benefit I can think
> > of would be to save space on your backup media, but that would be at
> > the expense of increased backup management, and disk space is much
> > cheaper than DBA time. In any event, differential backups are designed
> > to do exactly that - why not let MSSQL do the work for you, instead of
> > trying to build your own solution?
> >
> > If this isn't helpful, perhaps you could post some more information
> > about why your backups are currently affecting daytime processing;
> > what you hope to gain by only backing up changed databases; and why
> > your organization is reluctant to consider differential backups.
> >
> > Simon[/color][/color]

[Erland Sommarskog]

Re: Changes since last backup?

bobdirls (bobdirls@hotma il.com) writes:[color=blue]
> We need to save time on backups as we only have a short window
> overnight to do the backups. Our shop is not the sole owner of the box
> so we have to take other peoples needs into account.[/color]

As others have pointed out, there is no compelling reason why the
backups cannot be taken while users are working in the datanase.
[color=blue]
> I agree differential backups are probably the solution, but
> unfortunately are not appropriate. Dont ask me why but they have been
> ruled out totally for the time being.[/color]

Did you ever look at backing up only the transaction log? Assuming
that is, that you are running with your databases in Full or
Bulk_logged recovery mode. This is a more common method that
differential backups. By taking transaction log backups, you can
take full backups less frequently, if the update frequency to the
database is modest.
[color=blue]
> Our databases are built during the month then once the data is
> complete they are released to live. It would therefore be nice to only
> backup databases when they differ from the current backup. This would
> save time for doing the backups in the limited time we have. I was
> looking for a way around this without using differential backups.[/color]

Well, you could use the undocumented command DBCC LOG() to examine
the transaction log to see if something have happened. Again, this
this requires you to run with at least bulk_logged recovery, or the
transaction log would automatically truncated. And in any case, the
output from DBCC LOG() is cryptic, and you would need to learn how
to interpret it. (I don't know.) And since you would be running with
full or bulk_logged recovery, you would at least need to truncate
the transaction log now and then, or you disk will fill up.

You would be better off with the 3rd party tool Lumigent Log Explorer
which knows how to read the log. But how you would programmaticall y
use this information, I don't have the faintest.

In essence: in order to save some work for the machine, you are
creating a whole lot of work for yourself. And in the end for the
machine too.

So open the books and learn about transaction log backups.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp