By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,257 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,257 IT Pros & Developers. It's quick & easy.

Changes since last backup?

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"bobdirls" <bo******@hotmail.com> wrote in message
news:15**************************@posting.google.c om...
Hi,

Is there any way of telling if a database or table has been updated,
I don't think so.

What exactly is your goal?

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

Jul 20 '05 #2

P: n/a
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" <bo******@hotmail.com> wrote in message
news:15**************************@posting.google.c om...
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

Jul 20 '05 #3

P: n/a
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" <Te***@spamaway.om> wrote in message news:<br**********@reader2.nmix.net>...
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" <bo******@hotmail.com> wrote in message
news:15**************************@posting.google.c om...
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

Jul 20 '05 #4

P: n/a
bo******@hotmail.com (bobdirls) wrote in message news:<15**************************@posting.google. com>...
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.


<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
Jul 20 '05 #5

P: n/a
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.

sq*@hayes.ch (Simon Hayes) wrote in message news:<60**************************@posting.google. com>...
bo******@hotmail.com (bobdirls) wrote in message news:<15**************************@posting.google. com>...
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.


<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

Jul 20 '05 #6

P: n/a

"bobdirls" <bo******@hotmail.com> wrote in message
news:15**************************@posting.google.c om...
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.
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.


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.

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

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.

I think you will have to.

Thanks for your help.

sq*@hayes.ch (Simon Hayes) wrote in message

news:<60**************************@posting.google. com>...
bo******@hotmail.com (bobdirls) wrote in message news:<15**************************@posting.google. com>...
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.


<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

Jul 20 '05 #7

P: n/a
bobdirls (bo******@hotmail.com) writes:
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.
As others have pointed out, there is no compelling reason why the
backups cannot be taken while users are working in the datanase.
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.
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.
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.


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 programmatically
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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.