Skip to content

Instantly share code, notes, and snippets.

🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@EitanBlumin
EitanBlumin / MoveHistoricalDataForTable.sql
Created Jul 30, 2020
Stored procedure to move time-based data from one table to another, for archiving historical data
View MoveHistoricalDataForTable.sql
IF OBJECT_ID('dbo.ArchivingActivityLog') IS NULL
BEGIN
CREATE TABLE dbo.ArchivingActivityLog
(
Id INT NOT NULL IDENTITY(1,1),
SourceTable SYSNAME NOT NULL,
Command NVARCHAR(MAX) NULL,
StartTime DATETIME NOT NULL CONSTRAINT DF_ArchivingActivityLog_StartTime DEFAULT (GETDATE()),
EndTime DATETIME NULL,
RowsMoved INT NULL,
@EitanBlumin
EitanBlumin / detect_not_secured_connections.sql
Last active Jul 15, 2020
T-SQL monitoring script to make sure that all connections to the SQL Server instance are secured with SSL
View detect_not_secured_connections.sql
/*
Detect Non Secured Connections (SSL) to the SQL Server instance
===============================================================
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Last Update: 2020-07-15
Description: Use this to make sure that all connections to the SQL Server instance are secured with SSL.
*/
SELECT CONCAT('Not secured connection(s) detected of '
, ISNULL(QUOTENAME(COALESCE(ses.original_login_name, ses.nt_user_name, ses.login_name)), 'an unknown login')
, ' from ', ISNULL(QUOTENAME(client_net_address), 'an unknown address')
@EitanBlumin
EitanBlumin / find_unused_indexes_all_databases.sql
Created Jun 30, 2020
Find all unused indexes across all of your databases. Supports both on-premise instances, as well as Azure SQL Databases.
View find_unused_indexes_all_databases.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Description: Use this script to retrieve all unused indexes across all of your databases.
The data returned includes various index usage statistics and a corresponding drop command.
Supports both on-premise instances, as well as Azure SQL Databases.
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @CMD NVARCHAR(MAX);
SET @CMD = N'
PRINT DB_NAME();
@EitanBlumin
EitanBlumin / GeneratePeriods_Inline.sql
Last active Jul 28, 2020
Table Function to generate periods for time series, based on an end date, period type, and number of periods back
View GeneratePeriods_Inline.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2013-09-01
Last Update: 2020-07-28
Description:
CTE-based Inline Table Function to generate periods for time series, based on an end date, period type, and number of periods back.
Supported period types:
MI - Minute
H - Hour
@EitanBlumin
EitanBlumin / Script.PreDeployment.CLR_Signing.sql
Created Jun 19, 2020
Pre-Deployment Script Template for Importing a Signed CLR Assembly (SSDT Project)
View Script.PreDeployment.CLR_Signing.sql
/*
Pre-Deployment Script Template for Importing a Signed CLR Assembly (SSDT Project)
--------------------------------------------------------------------------------------
In order to use this script, you must configure the following SQLCMD Variables in your project:
$(PathToSignedDLL)
$(CLRKeyName)
$(CLRLoginName)
To configure your SQLCMD Variables: Right-click on your DB project, select "Properties", and go to "SQLCMD Variables".
@EitanBlumin
EitanBlumin / restore_from_backup_with_move.sql
Created Jun 4, 2020
SQLCMD script to restore from a database backup while easily moving all files to specified folders per file type.
View restore_from_backup_with_move.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-06-04
Description:
Use this script to restore from a database backup while easily moving all files to specified folders per file type.
The script must be run in SQLCMD mode.
Don't forget to modify the SQLCMD variables as needed.
*/
:setvar DatabaseName MyDatabase
:setvar BackupFilePath H:\MyDatabase_backup_20200602_233000.bak
@EitanBlumin
EitanBlumin / Generate-FileRecoveryFromVault.ps1
Last active Jun 3, 2020
Generate a File Recovery script for a specified VM in an Azure Recovery Services Vault
View Generate-FileRecoveryFromVault.ps1
# Copyright 2020 Eitan Blumin <@EitanBlumin, https://www.eitanblumin.com>
# while at Madeira Data Solutions <https://www.madeiradata.com>
#
# Licensed under the MIT License (the "License");
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR CO
@EitanBlumin
EitanBlumin / create_database_snapshot.sql
Created Jun 3, 2020
Easily create a database snapshot for a given database using T-SQL
View create_database_snapshot.sql
DECLARE @CurrDB SYSNAME = 'MyDBName'
DECLARE @CMD NVARCHAR(MAX);
SELECT @CMD = ISNULL(@CMD + N',
', N'') + N'(NAME = ' + QUOTENAME(name) + N'
, FILENAME = ' + QUOTENAME(LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
+ DB_NAME(database_id) + '_' + REPLACE(NEWID(),'-','') + '.ss', N'''')
+ N')'
@EitanBlumin
EitanBlumin / CaptureTSQLErrors_XE_buffer.sql
Last active Jul 15, 2020
Collect T-SQL Error Events using an Extended Events Buffer
View CaptureTSQLErrors_XE_buffer.sql
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 2020-05-31
-- Last Update: 2020-07-15
-- Description: Collect T-SQL Error Events using an Extended Events Buffer
-- The script automatically detects whether you're in an Azure SQL DB, or a regular SQL Server instance.
SET NOCOUNT ON;
DECLARE
@SourceLinkedServer SYSNAME
@EitanBlumin
EitanBlumin / fulltext_recycle_crawl_log_all_DBs.sql
Created May 31, 2020
This script iterates through every populated full-text catalog in every accessible database and recycles its crawl log. (more info: https://www.sqlskills.com/blogs/jonathan/recycle-fulltext-catalog-log-files/ )
View fulltext_recycle_crawl_log_all_DBs.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-05-31
Description:
This script iterates through every populated full-text catalog in every accessible database and recycles its crawl log.
Recommended to run this script as a weekly/monthly scheduled maintenance job.
More info:
https://www.sqlskills.com/blogs/jonathan/recycle-fulltext-catalog-log-files/
*/
You can’t perform that action at this time.