This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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! |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
*\ |* ___________ | |
\| ___/ 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__\__/ \_/ \_/ \ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
; 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: |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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. | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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: |
NewerOlder