Keeping record of data changes

I need to keep records of data changes in my SQL Server 2005 database.
I think I need a log-viewer to analyze data. I found several products
but only one that supports SQL2005: ApexSQL Log. Do you know others?
Also, I need to know how to configure logging and backup in SQL Server
so as to have the log data from (at least) the last 3 months. I mean:
which data files do I need (only primary file or transaction log file)?
which files can I overwrite when I perform a backup?, etc, etc.
Thanks a lot.
GasparHi
http://www.lumigent.com/

"Gaspar" <gaspar@.no-reply.com> wrote in message
news:ei1XHF6bHHA.260@.TK2MSFTNGP02.phx.gbl...

>I need to keep records of data changes in my SQL Server 2005 database.
> I think I need a log-viewer to analyze data. I found several products but
> only one that supports SQL2005: ApexSQL Log. Do you know others?
> Also, I need to know how to configure logging and backup in SQL Server so
> as to have the log data from (at least) the last 3 months. I mean: which
> data files do I need (only primary file or transaction log file)? which
> files can I overwrite when I perform a backup?, etc, etc.
> Thanks a lot.
> Gaspar|||I already have Lumigent Log Explorer 4.0.2 and doesn't support SQL
Server 2005.
Thanks again
Uri Dimant wrote:
> Hi
> http://www.lumigent.com/
>
>
>
>
>
>
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:ei1XHF6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>
>|||"Gaspar" <gaspar@.no-reply.com> wrote in message
news:ei1XHF6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>I need to keep records of data changes in my SQL Server 2005 database.
> I think I need a log-viewer to analyze data. I found several products but
> only one that supports SQL2005: ApexSQL Log. Do you know others?
>
Not familiar with that, but http://www.lumigent.com/ has a product that
might be useful.
However, you may be better off with using triggers to track entries to an
audit table.
That way you can select directly against the audit table as needed.

> Also, I need to know how to configure logging and backup in SQL Server so
> as to have the log data from (at least) the last 3 months. I mean: which
> data files do I need (only primary file or transaction log file)? which
> files can I overwrite when I perform a backup?, etc, etc.
Well, be forewarned, depending on the size of your database and how much
traffic it has, this could be a huge amount of data.
Basically, this though is not much different than any DR plan.
I'd probably do something like
FULL backup
Transaction log backups every X minutes (depends on how critical site is,
etc...) 15 minutes is often a decent compromise.
Now you'll need to keep that full backup and EVERY log backup since it was
started, for 3 months.
However, at SOME point obviously you'll need to delete that Full backup so
you can keep a rolling window.
Now, depending on size of the database, you might want to take a full-backup
every night and simply delete any older than 90 days.
Or one once a week and delete any older than 97 days. Or something in
between.
Keep in mind the longer you go between full-backups, the more transaction
logs you'll need to restore.
This may or may not be a problem.
For example, if you take a full backup every night and then transaction logs
once an hour, you'll never need more than 24 restores (full plus 23 log
backups).
If you take a full backup once a week and transaction log backups every 15
minutes, worse case scenario is something like 672 restores.
If you take a full backup once every three months and... well you get the
idea.
However, if you database is huge, doing a nightly backup may not be
practical.
You can also take differential backups which is basically everything since
the last full. (A transactional backup is basically everything since the
last transactional backup.)
So... in summary, to do a restore you at the very least need a FULL backup.
To get it up to date, you'll need transactional backups. And to limit the
number of restores you may have to do, you might want to look at doing some
differential backups in there. (personally I've never bothered, but others
have.)
So, for 3 months, you'll need at least a FULL backup that old and all
transactional backups (and potentially any differentials).
This should allow you to restore to any point in time up to the last
transactional backup. Combine this with the ability often to backup the
"tail of the log" in a disaster situation, and you should be pretty much
able to restore to any point in time, UP TO the point of failure.

> Thanks a lot.
> Gaspar
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||1) You are correct in that ApexSQL Log is the only log-recovery product that
supports 2005. It is a very good product though.
2) You may want to investigate their Audit product as well, which will
create a triggering system that will track all data changes for you. Very
simple to implement/use.
TheSQLGuru
President
Indicium Resources, Inc.
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:ei1XHF6bHHA.260@.TK2MSFTNGP02.phx.gbl...
>I need to keep records of data changes in my SQL Server 2005 database.
> I think I need a log-viewer to analyze data. I found several products but
> only one that supports SQL2005: ApexSQL Log. Do you know others?
> Also, I need to know how to configure logging and backup in SQL Server so
> as to have the log data from (at least) the last 3 months. I mean: which
> data files do I need (only primary file or transaction log file)? which
> files can I overwrite when I perform a backup?, etc, etc.
> Thanks a lot.
> Gaspar|||Thanks a lot your time. I have some questions regarding your explanations.
By know, I'm doing a full backup once a day, and transaction-log backups
(with log truncate) every 2 hours, i.e., 12 logs backups per day.
(1) In the case of a database RESTORE (suppose my disk crashes):
Which file do I need? Is it enough with the full backup or do I also
need the 12 log backups?
(2) For LOG AUDITING:
Should I keep the 13 daily backup files for 90 days (1170 files)? Or can
I keep only one full backup and the 12 log backups for each day I want
to have log data? (1 + 12 x 90 days = 1081 log files)
Thanks again
Gaspar
Greg D. Moore (Strider) wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:ei1XHF6bHHA.260@.TK2MSFTNGP02.phx.gbl...
> Not familiar with that, but http://www.lumigent.com/ has a product that
> might be useful.
> However, you may be better off with using triggers to track entries to an
> audit table.
> That way you can select directly against the audit table as needed.
>
> Well, be forewarned, depending on the size of your database and how much
> traffic it has, this could be a huge amount of data.
> Basically, this though is not much different than any DR plan.
> I'd probably do something like
> FULL backup
> Transaction log backups every X minutes (depends on how critical site is,
> etc...) 15 minutes is often a decent compromise.
> Now you'll need to keep that full backup and EVERY log backup since it was
> started, for 3 months.
> However, at SOME point obviously you'll need to delete that Full backup so
> you can keep a rolling window.
> Now, depending on size of the database, you might want to take a full-back
up
> every night and simply delete any older than 90 days.
> Or one once a week and delete any older than 97 days. Or something in
> between.
> Keep in mind the longer you go between full-backups, the more transaction
> logs you'll need to restore.
> This may or may not be a problem.
> For example, if you take a full backup every night and then transaction lo
gs
> once an hour, you'll never need more than 24 restores (full plus 23 log
> backups).
> If you take a full backup once a week and transaction log backups every 15
> minutes, worse case scenario is something like 672 restores.
> If you take a full backup once every three months and... well you get the
> idea.
> However, if you database is huge, doing a nightly backup may not be
> practical.
> You can also take differential backups which is basically everything since
> the last full. (A transactional backup is basically everything since the
> last transactional backup.)
> So... in summary, to do a restore you at the very least need a FULL backup
.
> To get it up to date, you'll need transactional backups. And to limit the
> number of restores you may have to do, you might want to look at doing som
e
> differential backups in there. (personally I've never bothered, but others
> have.)
> So, for 3 months, you'll need at least a FULL backup that old and all
> transactional backups (and potentially any differentials).
> This should allow you to restore to any point in time up to the last
> transactional backup. Combine this with the ability often to backup the
> "tail of the log" in a disaster situation, and you should be pretty much
> able to restore to any point in time, UP TO the point of failure.
>
>|||>
> 2) You may want to investigate their Audit product as well, which will
> create a triggering system that will track all data changes for you. Very
> simple to implement/use.
>
I'm looking for some passive solution so as to not generate overhead in
sql normal operations.
Thanks|||"Gaspar" <gaspar@.no-reply.com> wrote in message
news:ewSAhf7bHHA.2300@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Thanks a lot your time. I have some questions regarding your explanations.
> By know, I'm doing a full backup once a day, and transaction-log backups
> (with log truncate) every 2 hours, i.e., 12 logs backups per day.
> (1) In the case of a database RESTORE (suppose my disk crashes):
> Which file do I need? Is it enough with the full backup or do I also need
> the 12 log backups?
> (2) For LOG AUDITING:
> Should I keep the 13 daily backup files for 90 days (1170 files)? Or can I
> keep only one full backup and the 12 log backups for each day I want to
> have log data? (1 + 12 x 90 days = 1081 log files)
> Thanks again
> Gaspar
> Greg D. Moore (Strider) wrote:|||"Gaspar" <gaspar@.no-reply.com> wrote in message
news:ewSAhf7bHHA.2300@.TK2MSFTNGP06.phx.gbl...
> Thanks a lot your time. I have some questions regarding your explanations.
> By know, I'm doing a full backup once a day, and transaction-log backups
> (with log truncate) every 2 hours, i.e., 12 logs backups per day.
>
I'm not sure what you mean by "with log truncate".
If you mean you're doing something like "backup database FOO to
disk='f:\backups\backup.bak' with truncate_only I'm surprised that's not
throwing an error.
And if that IS what you're doing, you're wasting your time. It means you're
NOT taking log backups.
Can you post the exact command you're using?

> (1) In the case of a database RESTORE (suppose my disk crashes):
> Which file do I need? Is it enough with the full backup or do I also need
> the 12 log backups?
>
If you want to get it to the point where it was at the time of the last
transaction backup, you need to do the full restore AND restore ALL
transaction logs since then. So yes, full + 12.
And like I say, if your disk crashes, but the RAID your transaction log is
on (it is on a RAID separate from your data, right?) is still available, you
can backup the remaining transactions and get recover to the point of the
disk crash w/o loss of data.

> (2) For LOG AUDITING:
> Should I keep the 13 daily backup files for 90 days (1170 files)? Or can I
> keep only one full backup and the 12 log backups for each day I want to
> have log data? (1 + 12 x 90 days = 1081 log files)
>
If you have the FULL backup from 3 months ago, you can then keep 1080 log
files. BUT... what happens in month 4?
At some point you will want to delete the old full-backup. So you might as
well keep the other daily backups.
And I'm not sure how you get to 13 daily backup files for 90 days? Do you
mean weekly?
Basically, you'll have to sit down and decide exactly how much recovery you
need/want/can afford.
But ANY RESTORE starts with a FULL backup. From there you can add
transaction logs (and differential backups) as circumstances require.
[vbcol=seagreen]
> Thanks again
> Gaspar
> Greg D. Moore (Strider) wrote:
Greg Moore
SQL Server DBA Consulting See me for rate info
Email: sql (at) greenms.com http://www.greenms.com|||ApexSQL Log is the tool for you then. You can do command-line activities to
generate scripts and change information from the tlogs which can be used for
auditing/archival purposes.
TheSQLGuru
President
Indicium Resources, Inc.
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eNs3Jw7bHHA.1220@.TK2MSFTNGP03.phx.gbl...
> I'm looking for some passive solution so as to not generate overhead in
> sql normal operations.
> Thanks