SELECT 'ALTER AVAILABILITY GROUP '+ag.[name]+' REMOVE DATABASE '+sd.[name]+CHAR(10)+'GO'+CHAR(10)
FROM sys.dm_hadr_availability_replica_states ags
INNER JOIN sys.databases sd
ON sd.replica_id = ags.replica_id
INNER JOIN sys.availability_groups ag
ON ags.group_id = ag.group_id
WHERE ags.[role] = 1
AND ag.[name]= 'AG_GP'
Tuesday, December 13, 2022
Removing Databases from AG - Script to generate code
Last Backup Time (With Always On Configured)
SELECT sd.[name]
, sd.state_desc AS database_status
, f.backup_finish_date AS last_full_backup_date
, d.backup_finish_date AS last_diff_backup_date
, CASE WHEN (sd.database_id < 4 OR sd.recovery_model = 3) THEN 'N/A'
ELSE CONVERT (VARCHAR (20), l.backup_finish_date, 120 ) END AS last_log_backup_date
, CASE WHEN (f.backup_finish_date < (DATEADD (DAY, -7, GETDATE())) OR (f.backup_finish_date IS NULL) AND sd.name <> 'TempDB') THEN 'No full backup in the last seven days'
WHEN (sd.name = 'TempDB') THEN 'N/A'
END AS [missing recent backup]
FROM sys.databases sd
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name ) f
ON sd.[name] = f.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'I'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) d
ON sd.[name] = d.database_name
LEFT OUTER JOIN
(SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'L'
AND d.database_name <> 'tempdb'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name
) l
on sd.[name] = l.database_name
INNER JOIN
(SELECT
d.[name], hars.role_desc, ag.[name] AS [ag_group_name]--, *
FROM
sys.DATABASES d
INNER JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
WHERE role_desc = 'PRIMARY'
) ag
ON sd.[name] = ag.[name]
WHERE sd.[name] <> 'tempdb'
Sunday, August 2, 2015
Script to create audit tables and DDL Triggers
GO
/****** Object: Table [dbo].[audit_table_database_functions] Script Date: 2/6/2014 5:24:15 PM ******/
CREATE TABLE [dbo].[audit_table_database_functions](
[funtion_name] [varchar](200) NULL,
[user] [varchar](200) NULL,
[date_time] [datetime] NULL,
[activity] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[audit_table_database_procedures] Script Date: 2/6/2014 5:24:31 PM ******/
CREATE TABLE [dbo].[audit_table_database_procedures](
[procedure_name] [varchar](200) NULL,
[user] [varchar](200) NULL,
[date_time] [datetime] NULL,
[activity] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
/****** Object: Table [dbo].[audit_table_database_tables] Script Date: 2/6/2014 5:24:40 PM ******/
CREATE TABLE [dbo].[audit_table_database_tables](
[table_name] [varchar](200) NULL,
[user] [varchar](200) NULL,
[date_time] [datetime] NULL,
[activity] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
/****** Object: Table [dbo].[audit_table_database_views] Script Date: 2/6/2014 5:24:49 PM ******/
CREATE TABLE [dbo].[audit_table_database_views](
[view_name] [varchar](200) NULL,
[user] [varchar](200) NULL,
[date_time] [datetime] NULL,
[activity] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
/*******************************Triggers******************************/
USE [Test]
GO
/****** Object: DdlTrigger [DDL_Funtion_Trigger] Script Date: 2/6/2014 5:25:07 PM ******/
CREATE TRIGGER [DDL_Funtion_Trigger]
ON DATABASE
FOR ALTER_FUNCTION, DROP_FUNCTION, CREATE_FUNCTION
AS
INSERT INTO audit_table_database_functions
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
ENABLE TRIGGER [DDL_Funtion_Trigger] ON DATABASE
GO
USE [Test]
GO
/****** Object: DdlTrigger [DDL_Procedure_Trigger] Script Date: 2/6/2014 5:25:26 PM ******/
CREATE TRIGGER [DDL_Procedure_Trigger]
ON DATABASE
FOR ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_PROCEDURE
AS
INSERT INTO audit_table_database_procedures
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
ENABLE TRIGGER [DDL_Procedure_Trigger] ON DATABASE
GO
USE [Test]
GO
/****** Object: DdlTrigger [DDL_View_Trigger] Script Date: 2/6/2014 5:25:37 PM ******/
CREATE TRIGGER [DDL_View_Trigger]
ON DATABASE
FOR ALTER_VIEW, DROP_VIEW, CREATE_VIEW
AS
INSERT INTO audit_table_database_views
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
ENABLE TRIGGER [DDL_View_Trigger] ON DATABASE
GO
/****** Object: DdlTrigger [DDL_Table_Trigger] Script Date: 2/6/2014 5:27:40 PM ******/
CREATE TRIGGER [DDL_Table_Trigger]
ON DATABASE
FOR ALTER_TABLE, DROP_TABLE, CREATE_TABLE
AS
INSERT INTO audit_table_database_tables
SELECT
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(200)')
,SUSER_SNAME()
,GETDATE()
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
ENABLE TRIGGER [DDL_Table_Trigger] ON DATABASE
GO