January 11, 2011 at 9:45 pm
Hello all,
I am currently taken full backups of a database daily. For some auditing purpose i need all the transactions that happen in that database. So if i copy the physical transactional log initially and taking transactional log backup monthly. Does this serve my purpose.
For example:
If i am taking full backup of database X daily at 2 AM, then
3 AM JAN 1- transactional log backup
3 AM FEB 1- transactional log backup
3 AM MAR 1- transactional log backup
.
.
. so on.
So does transactional log backup taken on 3 AM FEB 1 contain all the transactions from 3AM JAN 1 transactional log backup (or) only the transactions from 2AM FEB 1 Full backup.
January 11, 2011 at 10:01 pm
I personally don't think what you have mentioned would be a right strategy as far as backup is concerned.
This will not solve your purpose rather it will make your database running without backups which might be critical for your business.
Better idea would be to take monthly full backup.
You can plan to take a full backup on the month End i.e the last date of the month just for the sake of audit purpose.
But you should keep your backup strategy in place this monthly backup in any case should not be a part of your routine backup startegy.
You can also enable C2 audit if you are running sql server 2005 or onwards.
It will create an audit file which will also capture almost everything for audit. but again it must be used with proper testing because it might slow down your performance and will also eat up some disk space .
pls. reply in case you need further clarification..
January 11, 2011 at 10:16 pm
Thanks for reply Sachnam,
Well as i said full backup of the database is taken daily, my concern is if i take transactional log backup monthly will it contain all the transactions from previous transactional log backup(which is taken a month ago) or only the transactions from previous full backup (which is taken an hour ago)?
January 11, 2011 at 10:28 pm
Well as i said full backup of the database is taken daily, my concern is if i take transactional log backup monthly will it contain all the transactions from previous transactional log backup(which is taken a month ago) or only the transactions from previous full backup (which is taken an hour ago)?
T-Log backups are used to rollback to a point in time. Once a month is not going to be of any value.
When you do a daily FULL backup, you already have the transactions for that day in you backup.
You are missing the point of T-Log backups.
For point in time recoverability, you might consider doing t-log backups once an hour, and still perform a full backup at the end of the day.
t-log backups are written in sequences (with LSN) and the chain cant be broken, or the backups will be worthless.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 11, 2011 at 10:29 pm
Dj463 (1/11/2011)
Thanks for reply Sachnam,Well as i said full backup of the database is taken daily, my concern is if i take transactional log backup monthly will it contain all the transactions from previous transactional log backup(which is taken a month ago) or only the transactions from previous full backup (which is taken an hour ago)?
It will keep the backup or all transaction occurred since your last log backup or since last consistent full backup in case the log chain is broken by some manual log truncate command.
by that i mean. Log backup maintains a sequential chain (Log sequence Chain) which is started from full\Differential backup and continue thereafter unless you truncate the log manually.
January 11, 2011 at 10:46 pm
I would hate to see how big the tran log reaches with an entire month's transactions in it.
Seriously, once a month is a really bad idea. The log will be HUGE, the log backup will take forever and it too will be huge.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2011 at 10:55 pm
OK, is there any way i could capture all the transactions occurring in that database other than C2 audit?
January 11, 2011 at 11:20 pm
On 2005 you're pretty much limited to a SQL Trace (server-side trace) or triggers. Triggers if you only want to capture data changes, trace if you want the exact script of all operations (select and modification)
C2 doesn't capture all transactions (that I'm aware). You're talking about huge amounts of information in anything other than a simple system. On a medium-sized system I'm working on, just the SQL:Batchcompleted and RPC:completed events come to just under 1GB an hour of trace data.
Do you really need to record every single transaction? Do you need an audit log of just data changes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2011 at 9:07 am
OP, perhaps you can more fully explain your goal and environment. Then you will most likely get some good suggestions.
January 12, 2011 at 7:33 pm
homebrew01 (1/12/2011)
OP, perhaps you can more fully explain your goal and environment. Then you will most likely get some good suggestions.
We are replicating(transactional replication) a database from a client, now they are archiving their database so that only 2 months data is retained. So we modified replication so that it doesn't replicate delete transactions. Now for auditing purpose we just need to have all the transactions that happen in that database.
January 13, 2011 at 2:56 am
What do you need to see?
The resultant data change for each transaction?
The exact insert/update/delete that was run?
Selects as well?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2011 at 6:12 pm
GilaMonster (1/13/2011)
What do you need to see?The resultant data change for each transaction?
The exact insert/update/delete that was run?
Selects as well?
I need to capture insert/update/delete that happened to that database
January 13, 2011 at 10:53 pm
One question I would first ask is how will you analysis/retrieve the data (if you have it, either trace file,tlog file etc) when required (for example to find out at what time a record value has been changed from A to B or what insert/update/delete happened at certain time). If you just want to have something onhand to ensure you have an option to do so (regardless cost associated with it etc), Tlog backup might be the cheapest and easiest way for you. Schedule hourly or daily Tlog backup and retain them (together with associated full backup) according to your retention policy (or discovery window). If it ever requires finding out when record value has been changed etc, you can then find a way/tool to read through the Tlog to find out the detailed transaction info. That’s how the restore can be done up to point-in-time with Tlog backup.
The side benefit is that your tlog file size will be maintained in good shape as well.
January 14, 2011 at 12:21 am
If you need the exact test of the queries, you'll need a server-side trace. It will be a lot of data. I have a system that produces 1GB of trace data in under an hour, and that's just RPC:Completed and T-SQL:BatchCompleted
If you just need before and after values, consider triggers on the tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2011 at 8:22 am
My first, general advice would be:
Don't conflate requirements.
Backup is a subset of the Restore requirement: Usually, the requirement to be able to more or less recover from a more or less specific (usually less) set of problems. Having older backups is solely a hedge against the most current having problems.
Archiving is a completely separate requirement: Usually, the requirement to be able to see what the data looked like at more or less specific points in time in the past. Sometimes, the requirement to see every change, and sometimes, the requirement to see what caused every change.
If you have a Backup requirement, you need to time your T-log backups based on that and on controlling the growth of the T-log for Full recovery mode databases.
For your Archiving requirement, maybe you can use T-log backups + server traces; others can help you more there. But do a proof of concept first.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply