June 15, 2011 at 5:13 am
We have an application we bought which runs a SQL Server 2008 Express backend. We have had an interesting incident over the weekend where 2000 records have changed a value field to 0. The logs within the application show nothing and I do not have a log file for that day as I am uning a full backup plan.
So the question being, can I backup the transaction log (containing the days transactions) on its own before performing a full backup ? If so what would I use to interrogate these transactions ?
June 15, 2011 at 5:28 am
Paul Farnell (6/15/2011)
We have an application we bought which runs a SQL Server 2008 Express backend. We have had an interesting incident over the weekend where 2000 records have changed a value field to 0. The logs within the application show nothing and I do not have a log file for that day as I am uning a full backup plan.So the question being, can I backup the transaction log (containing the days transactions) on its own before performing a full backup ? If so what would I use to interrogate these transactions ?
Once its committed to the log file, then the committed value only get backup. (i.e changed field value to 0)
When its got changed? (2000 records).
Tel me the day
When you performed a full back up?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 15, 2011 at 5:47 am
If your db is using full ( or bulk ) recovery model, you can and should be making log backups !
There are 3th party tools like APEX log that can help you with your quest.
If you are using simple recovery model, chances are your log entries have been reused, so you cannot use it to find the cause of your column updates
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2011 at 7:18 am
Yes I realise that the way the backup is setup removes the log file and it has been ok up to this issue, so was more looking for a best practice going forward. so what do I do just backup the log file on its own then do a full database backup after that ? I won't ever want to rollback to a point in time I just want to be able to work out what process has updated these records.
June 15, 2011 at 7:59 am
Please refer to Gails wonderful articles
- "Managing Transaction Logs": http://www.sqlservercentral.com/articles/64582/
and
- "Why is my transaction log full?" http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 15, 2011 at 10:36 am
Paul Farnell (6/15/2011)
I won't ever want to rollback to a point in time I just want to be able to work out what process has updated these records.
So no clueless user is ever going to drop a table and need it recovered?
Are you talking about going backwards for working out what updated those rows, or going forward. Going backward, unless you were in full recovery and have the log backups and buy something like Apex SQL Log (around $1000) you can't get that. Even if you do have the logs, they don't contain all that much information about who made the changes.
If you're talking about for future, consider putting triggers or something like SQL Audit, CDC or change tracking in place.
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
June 16, 2011 at 12:16 am
If this occur on the front end then you should have an application log trail table in the database design and if this occur on the back end then restrict the user to connect the instance if any one is using connecting you can check with this query
SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
ORDER BY CPU DESC
do Audit for this on the SQL Server as well as application Server site to avoid this in the future,Transaction table should have a date colum also you can check the event viewer of the OS to find the particular login in the case of IIS and integrated security are using
Revise your Backup policy
1-Full Backup weekly if database size is huge otherwise you can take at night daily
2-Differential Backup before1 hour of Office Start and After 1 hour of office Closed
3-Hourly Log Backup
there are other factors should be involve in the backup like space issues and tape backup also
you can catch anything with the help of logon trigger ,eventdata and database audit specification
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply