Gianluca Hotz ghotz

Consultant, speaker and trainer | SQL Server/Data Platform Microsoft MVP | President UGISS

ghotz / backup-delete-logins.ps1

Created January 22, 2026 15:41

Backup permissions before deleting logins and users

 $instance = 'localhost'
 $logins   = 'group1','group2'
 foreach ($login in $logins)
 {
     Write-Output "Processing $login"
     #$file     = "C:\temp\backup-$($login.Replace('\','_')).sql"
     #Export-DbaLogin -SqlInstance $instance -Login $login -FilePath $file -ObjectLevel
     Remove-DbaDbUser -SqlInstance $instance -User $login -Force
     Remove-DbaLogin -SqlInstance $instance -Login $login -Force -Confirm:$false

ghotz / errorlog-filed-backups-all-databases.sql

Last active January 13, 2026 14:59

Audit database backup failures for all databases, of for specific databases, pulling surrounding error details

	DECLARE @StartSearchDate datetime;
	DECLARE @SecondsAround int;
	DECLARE @Databases TABLE (DatabaseName sysname NOT NULL PRIMARY KEY);

	SET @StartSearchDate = GETDATE() - 2;
	SET @SecondsAround = 1;

	--
	-- Get error logs
	--

ghotz / move-tempdb.sql

Created December 19, 2025 16:15

Move all tempdb files

	DECLARE @original_path sysname = N'E:\SQLServer\MSSQL15.SQL2022\MSSQL\DATA';
	DECLARE @destination_path sysname = N'E:\SQLServer\MSSQL16.SQL2022\MSSQL\DATA';


	DECLARE @sqlcmd nvarchar(max) = (
	SELECT
	N'ALTER DATABASE [tempdb]'
	+ N' MODIFY FILE (NAME = [' + [name] + ']'
	+ N', FILENAME = ''' + REPLACE(physical_name, @original_path, @destination_path) + ''');'
	+ CHAR(10)

ghotz / create-ag-alerts.sql

Created October 24, 2025 11:55

Availability Groups Alerts

	USE [msdb]
	GO

	/**** Object: Alert [AG Connection Timeout] Script Date: 1/13/2023 10:36:31 AM ****/
	EXEC msdb.dbo.sp_add_alert @name=N'AG Connection Timeout',
	@message_id=35206,
	@severity=0,
	@enabled=1,
	@delay_between_responses=60,
	@include_event_description_in=1,

ghotz / get_roles_permissions.sql

Created July 24, 2025 12:54

Get detailed permissions for the specified roles

	SELECT DISTINCT
	RP.[name]
	, RP.[type_desc] AS role_type
	, PM.class_desc AS permission_type
	, PM.[permission_name]
	, pm.state_desc
	, CASE
	WHEN O1.type_desc IS NULL OR O1.type_desc = 'SYSTEM_TABLE'
	THEN PM.class_desc
	ELSE O1.[type_desc]

ghotz / sp_help_revlogin.sql

Created June 25, 2025 12:57

Old version of sp_help_revlogin that still otuputs sa and other system logins

	/Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin. /
	-- Create stored procedure sp_hexadecimal
	USE master
	GO
	IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
	DROP PROCEDURE sp_hexadecimal
	GO
	CREATE PROCEDURE sp_hexadecimal
	@binvalue varbinary(256),
	@hexvalue varchar (514) OUTPUT

ghotz / scan-pst-multi.ps1

Last active May 5, 2025 21:37

Scan and repair PSTs with multiple passes and full PST amd log archival

	$scanPSTExe = "C:\Program Files\Microsoft Office\root\Office16\SCANPST.EXE"
	$SourceDir = "E:\Outlook\test"
	$BackupDir = "F:\Backups\Outlook\test"
	$MaxIterations = 10

	$PSTs = Get-ChildItem -Path (Join-Path $SourceDir "\") -Include .pst,*.ost -File

	$PSTs \| ForEach-Object {
	$NumIteration = 1

ghotz / change-dbmail-account.sql

Last active May 2, 2025 13:48

Change from and replyto e-mail address for DBMail ccount and test it

	DECLARE @old_email_address nvarchar(128) = N'[email protected]';
	DECLARE @new_email_address nvarchar(128) = N'[email protected]';
	DECLARE @old_replyto_address nvarchar(128) = N'[email protected]';
	DECLARE @new_replyto_address nvarchar(128) = N'[email protected]';
	DECLARE @test_email_address nvarchar(128) = N'[email protected]';

	USE msdb;
	SET NOCOUNT ON;

	IF OBJECT_ID('tempdb..#tmp_account') IS NOT NULL

ghotz / get-seeding-stats.sql

Created April 5, 2025 10:07

Get seeding stats

	SELECT
	FLOOR(transferred_size_bytes * 1. / NULLIF(database_size_bytes, 0) * 100) AS Perc
	, transfer_rate_bytes_per_second / 1024 / 1024. AS MBSec
	, internal_state_desc
	, start_time_utc, estimate_time_complete_utc
	, FLOOR(transferred_size_bytes / 1024. / 1024. / 1024.) AS transferred_size_GB
	, FLOOR(database_size_bytes / 1024. / 1024. / 1024.) AS database_size_bytes_GB
	, *
	FROM sys.dm_hadr_physical_seeding_stats

ghotz / get-services.sql

Last active November 12, 2024 17:21

Get SQL Server Instance TCP ports configuration from registry and standardized services names

	WITH cte AS
	(
	SELECT
	CASE
	WHEN PATINDEX('SQL Server (%', servicename) > 0
	THEN 'Engine'
	WHEN PATINDEX('SQL Server Agent (%', servicename) > 0
	THEN 'Agent'
	WHEN PATINDEX('SQL Full-text Filter Daemon Launcher (%', servicename) > 0
	THEN 'FullText'