May 25, 2010 at 2:16 am
Hi guys,
Can you advise if there T-SQL code to assist retreiving all/any Updates recorded on a Production database between 2 points in time (e.g. 9am-5pm)?
Thanks,
L
May 25, 2010 at 2:28 am
Which version of SQL Server are you using, and exactly what information would you like to see?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 25, 2010 at 2:55 am
Hi Paul,
It'll be SQL 2k. Over a week long period, I will only have a production database (i.e. no back up)
As it will be better to risk losing up to a days worth of data rather than a week, I'd like a list of the daily changes or even every four hours.
Cheers,
L
May 25, 2010 at 4:59 am
Btw, sorry for posting in T-SQL(SS2K8) - shall I repost or await a response???
May 25, 2010 at 6:39 am
There is nothing built into SQL Server 2000 that will answer that question automatically. The reason Paul asked for the version is because 2008 has a process called Change Data Capture that will enable the exact type of information you're looking for with little set up.
You're going to have to design and build this for yourself. At the simplest level, you could add a column to your tables that captures the date & time it was updated. You can then query all the tables to retrieve the data. Of course, this won't include hard deletes so you'll have to deal with that issue as well. You could also look into possibly setting up some sort of auditing tables that identify changes made to the main tables through triggers (possibly dangerous approach) or through the stored procedures used to manipulate the data now (assuming you use stored procs). There are other ways to solve the problem as well, but the gist of the message is, you'll have to build something.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 25, 2010 at 11:56 am
i've been using the below and running accross multiple boxes using open query to give me all table changes as well as others for change monitor. Works in 2005 and 2008 db's very useful.
SELECT Datediff(hh, (SELECT MIN(starttime)
FROM ::fn_trace_gettable((SELECT REPLACE(Reverse(Reverse(PATH)), Reverse(Substring(Reverse(PATH), 1, ( Charindex('\', Reverse(PATH)) - 1 ))), 'log.trc')
FROM sys.traces
WHERE is_default = 1), DEFAULT)
WHERE EventClass IN ( 46, 47, 164 )
AND EventSubclass = 0
AND objecttype NOT IN ( 21587 )), Getdate()) / 24 [DIFFERENCE],
(SELECT MIN(starttime)
FROM ::fn_trace_gettable((SELECT REPLACE(Reverse(Reverse(PATH)), Reverse(Substring(Reverse(PATH), 1, ( Charindex('\', Reverse(PATH)) - 1 ))), 'log.trc')
FROM sys.traces
WHERE is_default = 1), DEFAULT)
WHERE EventClass IN ( 46, 47, 164 )
AND EventSubclass = 0
AND objecttype NOT IN ( 21587 )) [Date],
ObjectType obj_type_desc,
( Dense_rank() OVER (ORDER BY ObjectName, ObjectType ) )%2 AS l1,
( Dense_rank() OVER (ORDER BY ObjectName, ObjectType, starttime ) )%2 AS l2,
ObjectName obj_name,
ObjectID obj_id,
DatabaseName database_name,
StartTime start_time,
EventClass event_class,
EventSubClass event_subclass,
ObjectType obj_type,
ServerName Server_Name,
LoginName Login_Name,
NTUserName [User_Name],
ApplicationName Application_Name,
CASE
WHEN eventclass = 46 THEN 'CREATE'
WHEN eventclass = 47 THEN 'DROP'
WHEN eventclass = 164 THEN 'ALTER'
ELSE ''
END ddl_operation
FROM ::fn_trace_gettable((SELECT REPLACE(Reverse(Reverse(PATH)), Reverse(Substring(Reverse(PATH), 1, ( Charindex('\', Reverse(PATH)) - 1 ))), 'log.trc')
FROM sys.traces
WHERE is_default = 1), DEFAULT)
WHERE EventClass IN ( 46, 47, 164 )
AND EventSubclass = 0
AND objecttype NOT IN ( 21587 )
May 26, 2010 at 1:52 am
Guys,
I'll have a look at both solutions.
Thanks for the advice,
L
May 26, 2010 at 12:14 pm
Another option might be to add triggers to your tables to capture the changes into "temporary" (not necessarily tempdb) tables. This would resolve the issue of the deletes. Once you're able to backup again, you can just remove the triggers and tables, and your production tables and procedures would remain unchanged.
May 26, 2010 at 12:26 pm
lozt 79000 (5/25/2010)
Hi Paul,It'll be SQL 2k. Over a week long period, I will only have a production database (i.e. no back up)
Hey, sorry for the slow reply, I didn't get a notification for some reason...
Are you saying that you won't be able to take backups (full/differential/log) for some reason?
Can you explain more?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply