Sql DDL SP audit trigger

use [db_name];


create trigger event_SP_changes
on database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
print @command
INSERT INTO [AuditDB].dbo.db_name_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile

SQL Purge old database backups

/*
This script works with SQL 2005 to remove old backups from a file location
xp_delete_file
is the key to this operation. This is an undocumented SQL server function.
It deletes all file older than the date given in the parameter.
I use this to implement 24 hour disk to disk backup

The TSQL assumes the full backups have an extension of BAK and transaction logs
have an extension of TRN. It will purge both transaction logs and full backups
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
--, DATABASE_NAME varchar(25)
)

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND s_mf.database_id <> 2 --eliminate temp db



DECLARE @dbId int;
Declare @DateToDelete datetime;
declare @DateString char(8);
DECLARE @filePath nvarchar(max);

SET @DateToDelete=DateAdd(day,-1,GetDate()); --The -1 in the date diff is 24 hours
set @DateString = Convert(nvarchar(30),@DateToDelete,1)+' '+ Convert(nvarchar(30),@DateToDelete,8); --Set up SQL string


SET @filePath = '\server_backup_directory' ;

DECLARE cursor_usage CURSOR FOR
SELECT databaseId FROM @tDatabases;

OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @execString nvarchar(max)
--Remove full backups
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''bak'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);
--Remove transaction logs
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''trn'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;
CLOSE cursor_usage;
DEALLOCATE cursor_usage;

Christmas 2006

Reindex Stored Procedure

USE [master]
GO
/****** Object: StoredProcedure [dbo].[DbUtil_ALTER_INDEX] Script Date: 06/05/2008 16:18:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------
-- OBJECT NAME : DbUtil_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
-- : Alan T
--
-- DATE : February 27, 2007
-- : 2008 - 06- 06
--
-- INPUTS : @dbName - name of the database
-- @statsMode - mode to use for sys.dm_db_index_physical_stats
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @PerformOnlineOnly - Forces to rebuild to be online for enterprise ed. without 1 = true
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC DbUtil_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
, @PerformOnlineOnly = 1
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[DbUtil_ALTER_INDEX]
(
@dbName sysname
, @statsMode varchar(8) = 'SAMPLED'
, @defragType varchar(10) = 'REORGANIZE'
, @minFragPercent int = 25
, @maxFragPercent int = 100
, @minRowCount int = 0
, @PerformOnlineOnly bit = 1
)
AS

SET NOCOUNT ON

DECLARE @i int
DECLARE @objectId int
DECLARE @objectName sysname
DECLARE @indexId int
DECLARE @indexName sysname
DECLARE @schemaName sysname
DECLARE @partitionNumber int
DECLARE @partitionCount int
DECLARE @recordCount bigint
DECLARE @sql nvarchar(4000)
DECLARE @edition int
DECLARE @parmDef nvarchar(500)

SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

--Make sure the temp table does not exist
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE avg_fragmentation_in_percent > @minFragPercent
AND avg_fragmentation_in_percent < @maxFragPercent AND index_id > 0
AND record_count > @minRowCount
ORDER BY ObjectId

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i

WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + @dbName + '.sys.objects o
JOIN ' + @dbName + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'

SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

SET @sql = 'SELECT @indexName = [name]
FROM ' + @dbName + '.sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT

SET @sql = 'SELECT @partitionCount = COUNT(*)
FROM ' + @dbName + '.sys.partitions WHERE [object_id] = @objectId AND index_id = @indexId'

SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT

SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType

-- Developer and Enterprise have the ONLINE = ON option for REBUILD
IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 1
SET @sql = @sql + ' WITH (ONLINE = ON)'
ELSE IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 0
SET @sql = @sql + ' WITH (ONLINE = OFF)'

IF @partitionCount > 1
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

print @sql

BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
print 'ERROR1 : ' + cast(ERROR_LINE() as nvarchar(10));
print 'ERROR2 : ' + ERROR_MESSAGE();
END CATCH;


SET @i = @i + 1

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i
END


IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

SQL Backup Script

-------------------------------------------------------------------------------------------
-- OBJECT NAME : Alan backup script
--
-- AUTHOR : Alan T
--
-- DATE : 2008 06 18
-- 2008 09 18 - Updated parts of the query to clarify messaging
--
-- INPUTS : @dbName - name of the database
-- : @filePath - the file path to wirte the backups to
--
-- OUTPUTS : None
--
-- DEPENDENCIES : msdb.dbo.backupset
-- master.dbo.xp_create_subdir - only avaialable in SQL Server 2005 +
--
-- DESCRIPTION : Used to create a backup in the folder specified. The database will be placed
-- within a folder of the same name as the database. The backup will be verified.
-- Remove the create directory for SQL Server for 2000.

-------------------------------------------------------------------------------------------
declare @dbname nvarchar(50)
declare @filepath nvarchar(64)

SET @dbname = 'database_name';

--Make sure the location of the files ends with the slash as an additional
--folder will be created for the database files
set @filePath = N'directory_to_write_backup_file' + '\' + @dbName

EXECUTE master.dbo.xp_create_subdir @filepath;
declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh, GetDate())AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh, GetDate())AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))
if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
SELECT @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
SELECT @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;

--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(SELECT max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )
if @backupSetId is null
begin
declare @errMsg nvarchar(128)
SELECT @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end;

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND, STATS = 5;