Moving C2 Audits

I'm not seeing or finding how to move the location of the C2 Audit files. I
used the pretty standard script to enable C2 Auditing:
EXEC sp_configure 'show advanced option', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE WITH OVERRIDE
However, it would be nice if I could move them from the C:\ drive to a
folder already being backed up since we need these audit trails for at least
one year (Government thing). Any ideas? I'll keep looking, but I know we
get pretty quick responses in here also!
Thanks
AllenSorry , what is C2 Audit files ?
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||Hi,
By default SQL Server logs the C2 trace into data folder which you mentioned
during SQL Server installation.
The file can be copied to a different location by writing a batch file and
scheduled using SQL Server Agent every 1 hour.
Thanks
Hari
SQL Server MVP
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>|||C2 audits are predefined audits you can run to trace specific events on a
SQL Server. It is compliant with Government auditing policies and
procedures.
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
A Google search will produce more results for you.
In SQL 2005 it is a standard checkbox you can turn on by checking the
Properties of a Server, then clicking on Security. In SQL 2000, you have to
run the script I show below.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uX%23l0rStGHA.4748@.TK2MSFTNGP03.phx.gbl...
> Sorry , what is C2 Audit files ?
>
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Yeah, not really what I had in mind though. Just like I can change the
location of my data files and log files, I want my traces to go to another
location as well. This should be something we can run a script to configure
IMHO. I don't want to write batch files for each of my 70 SQL Servers ;-)
The default size of an audit file is 200 MB, and with C2 auditing, that will
be reached daily for some servers - no way around it. Furthermore, you
can't copy an active trace file, so I would have to continually check back
to see if the thing is 200 MB yet, then copy it to another location on the
same computer - awaiting pickup by our backup system. That is way too much
disk activity for me.
I would like to see the ability to write them directly to another location -
other than the original /data folder.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uY92JSUtGHA.4968@.TK2MSFTNGP03.phx.gbl...
> Hi,
> By default SQL Server logs the C2 trace into data folder which you
> mentioned during SQL Server installation.
> The file can be copied to a different location by writing a batch file and
> scheduled using SQL Server Agent every 1 hour.
> Thanks
> Hari
> SQL Server MVP
>
> "A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
> news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
>|||Ok - I've been told that audits get written to the default data directory.
However, we have dedicated drives for log and data files (E: and F:, for
example). What I had to do to get the C2 audits (trace files) to write to
our data directory was:
1) launch Enterprise Manager
2) right-click and Properties
3) Database Settings tab
4) Change 'default data directory' to F:\<location>
5) Stop and restart the services
Good to go. The traces will begin writing to the drive you specified as
your default data directory.
"A McGuire" <allen.mcguire@.gmail.com.invalid> wrote in message
news:e4aqFxLtGHA.2260@.TK2MSFTNGP03.phx.gbl...
> I'm not seeing or finding how to move the location of the C2 Audit files.
> I used the pretty standard script to enable C2 Auditing:
> EXEC sp_configure 'show advanced option', 1
> RECONFIGURE WITH OVERRIDE
> EXEC sp_configure 'c2 audit mode', 1
> RECONFIGURE WITH OVERRIDE
> However, it would be nice if I could move them from the C:\ drive to a
> folder already being backed up since we need these audit trails for at
> least one year (Government thing). Any ideas? I'll keep looking, but I
> know we get pretty quick responses in here also!
> Thanks
> Allen
>sql

Move table records into new database in the same server

I have a question here.

I have a Audit tables database. I want to archive the Audit Tables data to another new database in the same server and remove the data in the Actual database but continues with the table seed where it's the primary ID.

Example:

I have a AuditProducts Table with AuditID as BIGINT and auto increment value.

contains10,000 records in AuditTable Database.

I want to archive the tables to a new database: Audit200707

After archived, the AuditProducts Table in AuditTable database will remove all the records and continues with 10001 in the next record.

How can I done the whole process using T-SQL so that it can schedule and run for every month? BTW I am using SQL Server 2005 SP2.

Thanks and regards,

The following query might help you,

Code Snippet

Declare @.tablename as varchar(100);

Set @.tablename = 'AuditProduct' + Cast(Year(getdate()) as Varchar) + Substring(cast(100 + Month(getdate())-1 as varchar),2,2)

If NOT EXISTS(Select 1 From Sysobjects Where Name=@.tablename and type='U')

Begin

Exec('Select * into ' + @.tablename + ' From AuditProduct')

--If You want to rest the identity to 1

--Truncate Table AuditProduct

--If you want to keep the identity sequence

Delete From AuditProduct

End

|||

I want to create a new database and the same table. this process I need to put it into script as well.

How do I Generate the Selected Table scripts using T-SQL?

thanks for the help once again.

|||

OK the code mentioned by Manivannan.D.Sekaran works to create the table and record. But this doesn't copy the column information such as Default Value Or Binding and Description.

How to make it copy the column information as well?

Thanks a lot for your help.

regards,

|||

I think you're going to want to create an SSIS package, where you can copy database objects and then execute the T-SQL to delete the contents from your table(s).|||

I managed to wrote out a T-SQL according to Manivannan.D.Sekaran suggectiion:

Code Snippet

USE DB1

DECLARE @.DBName AS VARCHAR(50)

DECLARE @.TableName AS VARCHAR(100)

DECLARE @.NextSeedNo BIGINT

SET @.DBName = 'AuditDB1' + CAST(YEAR(GETDATE()) AS VARCHAR) + SUBSTRING(CAST(100 + MONTH(GETDATE()) AS VARCHAR),2,2)

EXEC('CREATE DATABASE ' + @.DBName)

DECLARE csrTable CURSOR

LOCAL FAST_FORWARD READ_ONLY FOR

SELECT TABLE_NAME

FROM information_schema.tables (NOLOCK)

WHERE TABLE_TYPE <> 'VIEW'

AND TABLE_CATALOG = 'DB1'

AND (TABLE_NAME LIKE 'Audit%')

ORDER BY TABLE_NAME

OPEN csrTable

FETCH NEXT FROM csrTable INTO @.TableName

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC('SELECT * INTO [' + @.DBName + '].[dbo].[' + @.TableName + '] FROM '+ @.TableName + ' GO ')

SET @.NextSeedNo = IDENT_CURRENT(@.TableName) + 1

EXEC('TRUNCATE TABLE ' + @.TableName + ' GO ')

EXEC('dbcc checkident (' + @.TableName + ', reseed, ' + CAST(@.NextSeedNo AS VARCHAR(10))+ ') GO')

FETCH NEXT FROM csrTable into @.TableName

END

CLOSE csrTable

DEALLOCATE csrTable

This was successfully meet what I want but the new database tables doesn't include the column information such as Default Value and Description. How can I get this done as well?

Thanks for your help.

regards