Trigger to find who is updating data on a table and when (Audit Table)

Trigger to find who is updating data on a table and when (Audit Table)
Test thoroughly and Implement in Production


Create the table :
CREATE TABLE AUDIT_LOG_TRANSACTIONS
(
TABLE_NAME VARCHAR(100),
HOST_NAME Varchar(128),
nt_username Varchar(128),
APP_NAME Varchar(128),
program_name  VARCHAR(128),
cmd Varchar(MAX),
net_address VARCHAR(128),
MODIFIED_BY Varchar(128),
MODIFIED_DATE DATETIME
)
GO




CREATE  TRIGGER [dbo].[tr_d_AUDIT_AccountStatus]
ON [dbo].[AccountStatus]
FOR UPDATE, INSERT -----AFTER INSERT,UPDATE

AS

BEGIN
    DECLARE
        @IDENTITY_SAVE              varchar(50),
        @AUDIT_LOG_TRANSACTION_ID       Int,
        @PRIM_KEY               nvarchar(4000),
        @ROWS_COUNT             int

    SET NOCOUNT ON


    Select @ROWS_COUNT=count(*) from Inserted
    Set @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

    INSERT   INTO dbo.AUDIT_LOG_TRANSACTIONS
    (   TABLE_NAME,
        [HOST_NAME],
nt_username,
[APP_NAME],
[program_name],
cmd,
net_address,
        MODIFIED_BY,
        MODIFIED_DATE
    )
        SELECT  [TableName] = OBJECT_NAME([parent_obj]) ,
                CASE WHEN LEN(HOST_NAME()) < 1 THEN ' '
                     ELSE HOST_NAME()
                END AS HostName ,
                ( SELECT TOP 1
                            nt_username
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                CASE WHEN LEN(APP_NAME()) < 1 THEN ' '
                     ELSE APP_NAME()
                END ,
                ( SELECT TOP 1
                            program_name
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                ( SELECT TOP 1
                            cmd
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                ( SELECT TOP 1
                            net_address
                  FROM      [master].[dbo].[sysprocesses]
                  WHERE     spid = @@spid
                ) ,
                SUSER_SNAME() ,
                GETDATE()
        FROM    sysobjects
        WHERE   [xtype] = 'tr'
                AND [name] = OBJECT_NAME(@@PROCID);


END

go


to see the Audit results after some time;
SELECT * FROM AUDIT_LOG_TRANSACTIONS

Find last backup taken for the databases in sql server

SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name

send the result of stored proceedure via email

declare @subject varchar(1000)
set @subject = 'Space Alerts on FPVI-Xweb1'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'abc@xyz.com',
@query = 'EXEC [FPVI-XWEB1].Master.dbo.XP_FixedDrives',
@subject = @subject
GO

List of foreign keys Associated with column name

--Get list of tables associated with column name
SELECT *
FROM information_schema.columns
WHERE column_name = 'Programid'
GO

----List of foreign keys associated with column name
SELECT
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
    ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
    AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
    ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
    AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
    AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
    AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
WHERE KCU2.COLUMN_NAME='Programid'

Alert when a table is Created or Dropped

Use below trigger to send email when a table is dropped

use the same trigger and change for Create Table and also change the subject in email ..

 

 

CREATE TRIGGER [TableDropped]

ON DATABASE

FOR  DROP_TABLE

 

AS 

DECLARE @bd VARCHAR(MAX)

DECLARE @tsql VARCHAR(MAX)

SET @tsql = EVENTDATA().value

        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')

SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

   ServerName: ' + @@SERVERNAME + '

   Time: ' + CONVERT(VARCHAR(25),GETDATE()) + '

   HostName: ' + HOST_NAME() + '

   Database: ' + DB_NAME() + '

   T-SQL: ' +  @tsql

   

BEGIN

PRINT 'Table has been Dropped'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',

 @recipients = 'abc@xyz.com'

 @subject = 'A new Table has been Dropped!',

 @body_format = 'HTML',

 @importance = 'High',

 @body = @bd

END

GO

DISABLE TRIGGER [TableDropped] ON DATABASE

GO

Notify if a database backup missing

declare @ServerName varchar(250)

declare @DatabaseList varchar(4000)

declare @CountMissingBackups int

declare @MailSubject varchar(250)

declare @MailText varchar (4000)

 

;WITH CTE as

(

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],

   msdb.dbo.backupset.database_name

FROM    msdb.dbo.backupset

WHERE     msdb.dbo.backupset.type = 'D'

GROUP BY msdb.dbo.backupset.database_name

HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))

 

UNION

 

--Databases without any backup history

SELECT    

   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],

   master.dbo.sysdatabases.NAME AS database_name

FROM

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset

       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name

WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'

--ORDER BY msdb.dbo.backupset.database_name

)

select @CountMissingBackups = count(*), @ServerName = [Server] ,@Databaselist = COALESCE(@DatabaseList + ', ', '') + CAST(database_name AS varchar(250))

from CTE

group by [Server], database_name

 

 

-- send the mail

 

if @CountMissingBackups > 0

begin

 

set @MailSubject = 'Backups on '+@ServerName

set @MailText = 'The following databases have not been backed up in the past 24 hours: '+@Databaselist

 

EXEC msdb.dbo.sp_send_dbmail 

@profile_name='Default',

@recipients='abc@XYZcom',

@subject=@MailSubject,

@body=@MailText

 

end

   

To get distinct statements from audit(set)

 To get distinct statements from audit


SELECT TOP 100 text, query_plan
INTO TESTJuly14
FROM sys.dm_exec_cached_plans cp
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'



DECLARE @DML TABLE (DML VARCHAR(10))
INSERT INTO @DML (DML) VALUES
('SELECT'),('DELETE'),('UPDATE'),('INSERT')


SELECT DISTINCT text
  FROM TESTJuly14 t
    CROSS APPLY @DML d
 WHERE text LIKE d.DML+'%'

Restart of SQL agent when replication is ON

I had a issue today that i had to restart SQL agent and i was worried about the replication that it could be out of sync and might be break.

the issue was the database mail was not configured and we are not getting any email notifications for job failures.so i have configured database mail , and enabled sql agent to use database mail profile to notify and that required sql agent restart.

I have restarted the agent, replication agent jobs started as soon as the sql agent started and i double checked the replication agent to make sure, database mail works as needed now ,

hope this helps some one  

Move files from one location to another using PowerShell

Move files from one location to another using PowerShell

--this move the files one location to another and do not copy the file whcih is active
Set-Location C:
Get-ChildItem C:\Temp\Source\*.txt |
    Sort-Object LastWriteTime -Descending |
    Select-Object -Skip 1 -ExpandProperty FullName |
    Move-Item -Destination C:\Temp\Target


---just copes the files but doesnt delet the source files
powershell.exe -command "& { ls C:\Temp\Source\*.txt | sort LastWriteTime -desc | select -skip 1 -expand FullName | mv -dest C:\Temp\Target }"



---moves the files
$file = "C:\temp\source" ## this will be your backup file folder
$archive = "C:\temp\Target\" ## this will be your destination folder
foreach ($file in gci $file -include *.txt -recurse)
{
Move-Item -path $file.FullName -destination $archive ## Move the files to the archive folder

}

we can use any script accordingly to our needs and keep in a job and schedule as needed 

Backup databases on SQL server Express edition

please see below on how to create backups on SQL Express edition,
SQL Server express edition do not have SQL agent .

1.create a linked server from any non express edition ,make sure you have sys admin previlage on the sql login you use
2.create SP(as below) on Express edition master database.
3.Schedule a JOB to call SP from non express edition.



Create  PROC SP_Backup
AS
BEGIN
DECLARE @path1  VARCHAR(100)
Declare @DeleteDate datetime
SET @Path1='R:\'
SET @DeleteDate= DATEADD(day, -2, GETDATE())---older than 2 days
EXEC master.sys.xp_delete_file 0,@path1,'BAK',@DeleteDate,0;


DECLARE @destination VARCHAR(300),
@now VARCHAR(50),
@now_date DATETIME,
@exec_str VARCHAR(200),
@db_name VARCHAR(30),
@path  VARCHAR(100)

SET @Path='R:\'--Location of the files going to be
SELECT @now_date = GETDATE()
SELECT @Now = RIGHT(CONVERT(VARCHAR(4),DATEPART(yy,@now_date)),4) + RIGHT( '00' + CONVERT(VARCHAR(2),DATEPART(mm,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(dd,@now_date)),2) +'_'+ RIGHT( '00' + CONVERT(varchar(2),DATEPART(hh,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(mi,@now_date)),2) + RIGHT( '00' + CONVERT(varchar(2),DATEPART(ss,@now_date)),2)

SET NOCOUNT ON
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.sys.databases
where name not in ('tempdb')
ORDER BY name

OPEN db_cursor
FETCH db_cursor INTO @db_name

WHILE (@@fetch_status = 0)
BEGIN

SELECT  @destination = 'R:\' + @db_name + '_Full_' + @Now +'.bak'
SELECT  @exec_str = 'BACKUP DATABASE ' + @db_name + ' TO DISK = ''' + @destination + ''' WITH COMPRESSION'
--PRINT @exec_str
EXEC (@exec_str)

FETCH db_cursor INTO @db_name
END

CLOSE db_cursor
DEALLOCATE db_cursor
 END