SQLAspire

Wednesday, February 1, 2023

Backup Status (Last 7 Days)

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
WHERE sd.[name] <> 'tempdb'

Monday, January 16, 2023

Backup Location

SELECT DISTINCT a.database_name, a.max_backup_finish_date, d.compressed_backup_size_in_MBs, d.duration, d.[user_name], d.physical_device_name
FROM
(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' --'I for diffs and 'L' for logs
GROUP BY d.[database_name]) a
INNER JOIN
(
SELECT
d.database_name
,d.backup_finish_date
, (compressed_backup_size/1024/1024) AS compressed_backup_size_in_MBs
, CASE
WHEN DATEDIFF (HOUR, d.backup_start_date, d.backup_finish_date) = 0
THEN CONVERT (VARCHAR (2), DATEDIFF (MINUTE, d.backup_start_date, d.backup_finish_date))+' Minutes'
END AS duration
, [user_name]
, physical_device_name
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D') d --'I for diffs and 'L' for logs
ON a.database_name = d.database_name
AND a.max_backup_finish_date = d.backup_finish_date

Backup Status with AG

Tuesday, December 13, 2022

File Distribution Drive

SELECT sf.[name], sf.filename, sd.state_desc, SUBSTRING (sf.filename, 1, 3) AS [drive],
CASE REVERSE(SUBSTRING (REVERSE(filename), 1, 3))
WHEN 'mdf' THEN 'primary data'
WHEN 'ndf' THEN 'secondary_data'
WHEN 'ldf' THEN 'log_file'
ELSE 'data_file'
END AS file_type--, *
FROM sys.sysaltfiles sf
INNER JOIN sys.databases sd
ON sf.dbid = sd.database_id
WHERE sd.state_desc = 'ONLINE'
AND sf.dbid > 4
ORDER BY SUBSTRING (sf.filename, 1, 3)

Fix Orphaned Users

EXEC sp_change_users_login 'Report'

--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

Suspend data transfer for AG databases on the primary node

SELECT sd.[name], ags.role_desc, @@SERVERNAME
FROM sys.dm_hadr_availability_replica_states ags
INNER JOIN sys.databases sd
ON sd.replica_id = ags.replica_id
WHERE ags.[role] = 1

--   BEGIN
--        DECLARE @sql VARCHAR (MAX)
--       SET @sql = 'ALTER DATABASE '+ drs.database_name+' SET HADR SUSPEND'+CHAR (10)+'GO'+CHAR(10)--, ags.name
--       FROM sys.dm_hadr_database_replica_cluster_states drs
--       INNER JOIN sys.dm_hadr_database_replica_states rs
--       ON rs.group_database_id = drs.group_database_id
--       INNER JOIN sys.availability_groups ags
--       ON rs.group_id = ags.group_id
--       WHERE ags.name = 'TWAG'
--       PRINT @sql
--   END
--   ELSE
--       BEGIN
--       SELECT 1/0
--   END

DECLARE @ServerName VARCHAR (20)
SELECT @ServerName = @@SERVERNAME

IF (@ServerName = 'SQLAAG-01')
   BEGIN
        DECLARE @sql VARCHAR (MAX)
       SELECT DISTINCT 'ALTER DATABASE '+ drs.database_name+' SET HADR SUSPEND'+CHAR (10)+'GO'+CHAR(10)--, ags.name
       FROM sys.dm_hadr_database_replica_cluster_states drs
       INNER JOIN sys.dm_hadr_database_replica_states rs
       ON rs.group_database_id = drs.group_database_id
       INNER JOIN sys.availability_groups ags
       ON rs.group_id = ags.group_id
       WHERE ags.name = 'SQLAAG-CLU-AG'
       --PRINT @sql
   END
   ELSE
       BEGIN
       SELECT 1/0
   END

Latest Backup File Location

Removing Databases from AG - Script to generate code

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'

Last Backup Time (With Always On Configured)

Sunday, August 2, 2015

Script to create audit tables and DDL Triggers

USE [Test]

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

　

　

　

　

　

　

　

　

　

　