473,322 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Changes since last backup?

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
7 2442

"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
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
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
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
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

"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

42
by: Alan McIntyre | last post by:
Hi all, I have a list of items that has contiguous repetitions of values, but the number and location of the repetitions is not important, so I just need to strip them out. For example, if my...
0
by: David | last post by:
Hi! My first server is linked to an application (inserts, updates and delete). My second server is a read-only server that will be used on Internet. I need to send, each night, only changed...
5
by: Nick Stansbury | last post by:
Hi, Sorry for the obscure title but I'm afraid I can't think of a better way to describe what happened to one of my clerks last night. The guy was working late, made a series of changes (accross a...
6
by: Ray | last post by:
Is there a simple way of selecting the last backup timestamp of a database from a table? Something like select last_backup_timestamp from tablename where database_name='WHATEVER' Or am I...
3
by: t2581 | last post by:
Hi , I run restore with rollforward In job in output, last commited transaction time less then backup image time Is it normal ? RESTORE DATABASE PRICE_V8 FROM "E:\BACKUP" TAKEN AT...
2
by: Deano | last post by:
In my app I have lots of forms with data presented in various ways using all kinds of controls. Is there a simple way of detecting if the user has changed any data? I would like to do this so I...
5
by: Jack | last post by:
Is there anyway in SQL Server to rollback an SQL statement which was already executed. I know there is a transaction log but what it contains and how it works is still a mystery to me. Assuming I...
0
by: yellr | last post by:
Hi guys, im facing a strange problem with the db2 backup size, our last backups have had the following sizes: 18G 24G 26G 20G
1
by: Rain | last post by:
Hi I know your gonna say finger trouble to this question, but its not. OK, yesterday, I converted a project from vs2005 to vs2008 ( and did a backup via the conversion I might add ). Anyway,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.