View Trigger_AfterDelete_LogEvidence.sql
CREATE OR ALTER TRIGGER dbo.Victims_DEL
ON dbo.Victims
AFTER DELETE
AS
BEGIN
INSERT INTO aud.Evidence (SchemaName, TableName, ActionType, RecordID
, DatabaseUser, ServerLogin, ActionTime)
SELECT SchemName = 'dbo', TableName = 'Victims', ActionType = 'delete', RecordID = Deleted.Id
, DbUser = USER_NAME(), SrvLogin = SUSER_SNAME(), ActionTime = SYSDATETIME()
FROM Deleted;
View OrphanedUsersFixByName.sql
/* NJohnson9402 / natethedba.wordpress.com
An example of how to check for orphaned database users (users that do not map to a server login).
Basically the idea here is to take the output of `sys.sp_helpuser` and utilize it to find & fix.
There are much cooler ways out there, such as dbatools.io/functions/repair-dbaorphanuser/, but
I have occasionally seen these not work in odd situations. Plus, if your login names and user names
don't exactly match, this method could be extended to enter custom mappings-- you'd simply add a new
column to #HelpUser, say [NewLoginName], and use that in the 'fix-it' statement-generator query.
*/
USE MyDatabase;
View RenameDatabaseByBackupDetachAttach.sql
/* NJohnson9402 / natethedba.wordpress.com
An example of how to rename a database by work-around involving backup, restore, detach, attach.
Basically the idea here is that 'OriginalDB' is in heavy use, and can't tolerate much "downtime",
but "readonly time" is OK for a bit.
Drive letters for clarification in this case: 'D' for slow data, 'L' for slow tlog,
'E' for fast data, 'M' for fast tlog. The 'fast' storage system is our destination.
*/
USE master;
EXEC OriginalDB.sys.sp_helpfile;
View FindTriggerDependencies_AuditTrailExample.sql
/* NJohnson9402 / natethedba.wordpress.com
Uses system catalog views and DMVs to find trigger table and column dependencies;
tailored as "audit trail" example, but applicable to most trigger situations.
Triggers always depend on & are a child object of the table on which they exist.
In this example, the predicate "OBJECTPROPERTY(trig.object_id, 'ExecIsUpdateTrigger') = 1"
restricts the view only to "ON/AFTER UPDATE" triggers; but you could easily extrapolate
to include the other types (INSERT, DELETE) as well, using the same model;
just replace 'ExecIsUpdateTrigger' with 'ExecIsInsertTrigger' etc.
*/
SELECT [Trigger] = trig.name
View BitsTransferLoopSample.ps1
# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
# LICENSE: https://choosealicense.com/licenses/unlicense/
# TYPE: PowerShell script
# DESCRIPTION/USAGE: example of 2 file copying loops using BITS Transfer cmdlets, targeted at SQL backups
# See corresponding blog post at https://natethedba.wordpress.com/powershell-and-bits/
# ASSUMPTION: your SQL backup files are named with a date component in the form "yyyyMMdd", and use extension ".bak"
$source = "\\BigSQL\Backup\BigSQLInstance\" #network share or local directory
$destiny = "X:\Restore\BigSQLInstance\" #LOCAL drive destination
$testonly = 1 #set to 0 to actually do the copying!
View AsciiBunker.txt
*\ |* ___________
\| ___/ I___I___I_\___
/___I___I___I___I___\ Incoming!!
/__I___I___I___I___I__\ /
___ /_I___I___I___I___I___I_\ ___
/ \ __ /____I___/ \___I____\ __ / \
/ \ / \ _ /___I____/ O \____I___\ _ / \ / \
/ \_/ \_/ \__/__I___I__|____^____|__I___I__\__/ \_/ \_/ \
View InsallSQLwithISOandINI.ps1
# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
# LICENSE: https://choosealicense.com/licenses/unlicense/
# TYPE: PowerShell script
# DESCRIPTION/USAGE:
# There are TWO static placeholders that you need to change/type-in when you want to use this,
# they both start with <PATH TO ...>. I also added a #CHANGE THIS! comment to the end of their lines.
# The first one is for your installer config .ini file
# (see other gist at https://gist.github.com/NJohnson9402/a3c13429a055771efd26eefa66c69d62).
# The second is for the location of your SQL server installation media (ISO).
# See corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/
View Install.SQL2016.standalone.ini
; AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com
; LICENSE: https://choosealicense.com/licenses/unlicense/
; TYPE: SQL Server 2016 Configuration File (for command-line installation)
; DESCRIPTION:
; Inline comments are mostly copied from existing file generated by install wizard, with clarification where necessary.
; I use drive D:\ for data files (MDF), L:\ for transaction logs (LDF), T:\ for TempDB, and X:\ for backups.
; Most other options are "normal", i.e. I don't deal with clustering, Availability Groups, or other exotic things.
; I am only installing the database engine and replication components; see FEATURES option for more.
; Read the corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/
; USAGE:
View BulkTableCopyCheckup.sql
/* NJohnson9402 / natethedba.wordpress.com
Designed for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.
See corresponding blog-post for background. Basically, this is a quick way to "check up on" the status of a
bulk table-copy (data copy from one table to another, potentially across different databases and/or schemas),
by knowing the original start-time, and getting row-counts using `sp_spaceused` for each table (source, destination).
This way, you can get a fairly accurate ETA on when the job will finish, based on how it's done so far.
*/
View BatchLoopSample.sql
/* NJohnson9402 / natethedba.wordpress.com
Primarily for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.
Typical use-case involves creating a temp-table first, to store your "target table PK values" along with an
Identity value to loop over. You then fill that temp-table with PK values from your target table, so you
can act on those rows.
For example: