Skip to content

Instantly share code, notes, and snippets.

🕵️‍♂️
Figuring it out

Eitan Blumin EitanBlumin

🕵️‍♂️
Figuring it out
View GitHub Profile
@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 / 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/
*/
@EitanBlumin
EitanBlumin / CHECKDB on non-readable AG secondaries.sql
Last active May 31, 2020
Run DBCC CHECKDB on all databases which are either standalone, or SECONDARY in AG. Supports non-readable secondaries by creating DB snapshots.
View CHECKDB on non-readable AG secondaries.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: March, 2020
Description:
Run DBCC CHECKDB on all databases which are either standalone, or SECONDARY in AG.
Supports non-readable secondaries by creating DB snapshots.
*/
DECLARE @CurrDB SYSNAME, @IsInAG BIT, @CMD NVARCHAR(MAX);
-- Find all databases which are either standalone, or SECONDARY in AG
@EitanBlumin
EitanBlumin / SQL_Server_Error_Log_Based_on_Severity_with_Full_Message.sql
Last active May 31, 2020
T-SQL script to output a single row per each high severity error from the SQL Server Error Log
View SQL_Server_Error_Log_Based_on_Severity_with_Full_Message.sql
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Description: Get a single row per each high severity error from the SQL Server Error Log
DECLARE
@SampleTime DATETIME = DATEADD(MINUTE,-30,SYSDATETIME())
, @MinimumSeverity INT = 17
, @MaximumSeverity INT = 25;
IF OBJECT_ID(N'tempdb..#errors') IS NOT NULL
DROP TABLE #errors;
@EitanBlumin
EitanBlumin / Create_Named_SQL_Server_Linked_Server.sql
Last active May 31, 2020
Create a Linked Server to a remote SQL Server, but give it a different name than its actual address
View Create_Named_SQL_Server_Linked_Server.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Description: Create a Linked Server to a remote SQL Server, but give it a different name than its actual address
*/
DECLARE
@ServerAddress [nvarchar](255) = 'MyRemoteServerAddress\SomeNamedInstanceIfYouWant,1433',
@NewServerName [nvarchar](255) = 'MyRemoteServerName',
@RemoteUser [nvarchar](128) = 'remote_user',
@RemotePassword [nvarchar](128) = 'remote_user_password',
@MapLocalLogin [nvarchar](255) = NULL -- name a local login to map to the remote login. If NULL, will map current login
@EitanBlumin
EitanBlumin / Standardize_Database_File_Sizes_and_AutoGrowth.sql
Last active May 31, 2020
Minimal standardization of all Database Files in a SQL Server instance
View Standardize_Database_File_Sizes_and_AutoGrowth.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Date: 2020-01-28
Description:
This script generates commands to implement a minimal standardization of all database files in the instance.
List of implemented standards:
1. Files must not be allowed to have percentage growth (defaults to 1GB growth instead as a replacement)
2. Files must all have UNLIMITED max size
3. Log files must be at least 64MB in size
4. Log file auto-growth must be in power multiples of 2 between 64MB and 2048MB (i.e. 64,128,256,512,1024,2048) (defaults to 1GB growth instead as a replacement)
@EitanBlumin
EitanBlumin / Generate recommendations for clustered indexes.sql
Last active May 31, 2020
Use existing non-clustered index usage stats, and missing index stats, to generate clustered-index recommendations for heap tables (more info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/ )
View Generate recommendations for clustered indexes.sql
-------------------------------------------------------
------ Generate Clustered Index Recommendations -------
-------------------------------------------------------
-- Author: Eitan Blumin | https://www.eitanblumin.com
-- More info: https://eitanblumin.com/2019/12/30/resolving-tables-without-clustered-indexes-heaps/
-------------------------------------------------------
-- Description:
-- ------------
-- This script finds all heap tables, and "guestimates" a clustered index recommendation for each.
-- The script implements the following algorithm:
@EitanBlumin
EitanBlumin / PrintMax_Minified_Temporary_Procedure.sql
Last active May 31, 2020
This is a minified version of the PrintMax procedure (originally written by Ben Dill). It's created as a temporary procedure.
View PrintMax_Minified_Temporary_Procedure.sql
IF OBJECT_ID('tempdb..#PrintMax') IS NOT NULL DROP PROC #PrintMax;
GO
/*
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
Description:
This is a minified version of the PrintMax procedure (originally written by Ben Dill).
It's created as a temporary procedure.
*/
CREATE PROCEDURE #PrintMax @str NVARCHAR(MAX)
AS
You can’t perform that action at this time.