RE: MS SQL - Problem

[Mike Lyman]

TO UNSUBSCRIBE: email "unsubscribe issforum" in the body of your message to
[EMAIL PROTECTED]  Contact [EMAIL PROTECTED] for help with any problems!
----------------------------------------------------------------------------

> -----Original Message-----
> From: Eng. Ahed M. Okasha [mailto:[EMAIL PROTECTED]] 
> My question is how to delete the records in
> The database after backing it up? (Not using
> The RS Console Configuration -> Enterprise Database
> Configuration) I want it using SQL commands or 
> A 3rd party software.

There's a stored procedure called iss_PurgeEvents. That's too painful in
our environment so I run the stuff below my sig as a scheduled DTS job
everyday.

Mike Lyman
Microsoft Corporate Security
Monitoring and Compliance
PGP KEYID 0xD7BBADAD

DECLARE @deleteto DATETIME
SET @deleteto = DATEADD(Day, - 60, GetUTCDate())

SELECT eventid INTO #tempevents FROM events WHERE eventdate < @deleteto

DELETE eventrawdata 
FROM eventrawdata INNER JOIN #tempevents
ON eventrawdata.eventid = #tempevents.eventid

DELETE eventupdates
FROM eventupdates INNER JOIN #tempevents
ON eventupdates.eventid = #tempevents.eventid

DELETE eventparams
FROM eventparams INNER JOIN #tempevents
ON eventparams.eventid = #tempevents.eventid

DELETE eventresponse
FROM eventresponse INNER JOIN #tempevents
ON eventresponse.eventid = #tempevents.eventid

DELETE FROM events WHERE eventdate < @deleteto

DROP TABLE #tempevents

SELECT remoteeventid INTO #tempeventsrejected FROM eventsrejected WHERE
eventdate < @deleteto

DELETE eventupdatesrejected
FROM eventupdatesrejected INNER JOIN #tempeventsrejected
ON eventupdatesrejected.remoteeventid =
#tempeventsrejected.remoteeventid

DELETE eventparamsrejected
FROM eventparamsrejected INNER JOIN #tempeventsrejected
ON eventparamsrejected.remoteeventid = #tempeventsrejected.remoteeventid

DELETE eventresponserejected
FROM eventresponserejected INNER JOIN #tempeventsrejected
ON eventresponserejected.remoteeventid =
#tempeventsrejected.remoteeventid

DELETE FROM eventsrejected where eventdate < @deleteto

DROP TABLE #tempeventsrejected