Problem: Suppose your team is working on the
under-development project so it might be possible continuous work on the
database and perform changes in Table, Stored procedure as per requirement, and
daily you have to update the testing server database as per changes are done in
developing server database then how it is possible to trace those changes.
There
are a lot of solutions for this problem which is listed below
Solution
1:
For
SQL Server 2000, 2005 and above use the CHECKSUM command
SELECT
CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM Your_Table_Name
WITH (NOLOCK);
|
That
will return the same number each time it runs as long as the table contents
haven't changed.
Unfortunately
CHECKSUM does not always work properly to detect changes. It is only a primitive
checksum and no CRC calculation. Therefore you can't use it to detect all
changes, e. g. Symmetrical changes result in the same CHECKSUM!
Solution
2:
1. Run
the following query. Before executing query replace DB_Name with your database
name you want to trace in this profiler, also change the file path with an appropriate file name. In the following example, your file will be saved at the location
'C:/Folder/MyFile.trc' with .trc extension.
USE[DB_NAME]
GO
DECLARE
@RC int,
@TraceID int,
@on
BIT,
@maxfilesize bigint
@DateTime
datetime;
EXEC
@rc = sp_trace_create @TraceID output, 2, N'C:\Folder\MyFile',@maxfilesize, @Datetime
SELECT RC = @RC, TraceID = @TraceID
-- Set the event
SELECT
@on = 1
EXEC sp_trace_setevent @TraceID, 128, 1, @on
EXEC
sp_trace_setevent @TraceID, 128, 11, @on
EXEC
sp_trace_setevent @TraceID, 128, 14, @on
EXEC
sp_trace_setevent @TraceID, 128, 35, @on
EXEC
@RC = sp_trace_setstatus @TraceID, 1
GO
|
2.
If you want to import records of
trace into the table the use following query.
SELECT
* INTO tablename FROM ::fn_trace_gettable('C:\Folder\MyFile.trc', DEFAULT)
GO
|
3. Now you are ready to see your trace
records, for example
SELECT
TOP 1000 [TextData]
,[HostName] ,[LoginName] ,[StartTime] ,[EndTime] ,[ServerName] ,[EventClass]
FROM
[DBname].[dbo].[tablename]
WHERE
DBname.dbo.tablename.TextData IS NOT NULL
|
4. Execute this query to stop the trace when you want to
audit data:
sp_trace_setstatus @traceid
= 1, @status = 0
|
5.
Inspect the "TextData"
column for the events: CREATE DATABASE, DROP DATABASE, ALTER DATABASE in order
to know what database was changed. To find out who changed it and when it was
changed, refer to the "LoginName" and "StartTime" columns
respectively.
Solution 3: Use some "SQL Source Control" a third-party tool provided by red-get, please follow the link: https://www.red-gate.com/products/sql-development/sql-source-control/
Solution 4: If you want to see only changes the run the following query
select *
from sys.objects
where (type = 'U' or type = 'P')
and modify_date > dateadd(m, -3, getdate())
|
