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
USE master; | |
DECLARE @CONST_P2 int = 1; --Error log, not Agent log. | |
DECLARE @NumLogFiles int = 30; --SET THIS APPROPRIATELY! | |
--^ (there's no way to get it programatically outside of registry-reads/other-xp's, none of which is doable in RDS, AFAIK. | |
--Example: show events from 7/19/2018 between 2am and 2:30am PDT | |
DECLARE @StartDate datetime = '2018-07-19T02:00:00' | |
, @EndDate datetime = '2018-07-19T02:30:00'; | |
CREATE TABLE #ErrorLogs ([LogDate] datetime, [ProcessInfo] nvarchar(50), [Text] nvarchar(max)); |
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
; 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
--Put this stuff in a new schema so we can easily clean up afterward. | |
CREATE SCHEMA [play] AUTHORIZATION [dbo]; | |
GO | |
--Set up our Cards model | |
--DROP TABLE play.Cards; | |
CREATE TABLE play.Cards | |
( | |
CardID int IDENTITY(1,1) PRIMARY KEY CLUSTERED | |
, Face varchar(2) |
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
/* The system table that contains TZ info | |
SELECT tzi.name, tzi.current_utc_offset, tzi.is_currently_dst | |
FROM master.sys.time_zone_info tzi | |
*/ | |
--Drop & create tables | |
IF (OBJECT_ID('dbo.OfficeLocation') IS NOT NULL) | |
DROP TABLE dbo.OfficeLocation; | |
IF (OBJECT_ID('dbo.MyTrans') IS NOT NULL) | |
DROP TABLE dbo.MyTrans; |
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
--A brief demo script about IDs, IDENTITY values, and boxes. | |
USE tempdb; --> we'll work in TempDB because it's quick, easy, and doesn't require any extra permissions | |
IF (OBJECT_ID('dbo.IdentityIsPK') IS NOT NULL) | |
DROP TABLE dbo.IdentityIsPK; | |
CREATE TABLE dbo.IdentityIsPK ( | |
ID int IDENTITY(1,1) | |
CONSTRAINT PK_IdentityIsPK PRIMARY KEY CLUSTERED | |
, Foo varchar(100) NOT NULL |
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
DECLARE @ver nvarchar(MAX), @cmd nvarchar(MAX); | |
SELECT @ver = @@VERSION; | |
SET @cmd = N'CREATE INDEX IX_TableName_Description ON dbo.TableName | |
(Column1, Column2) | |
INCLUDE (Column3, Column4) | |
'; | |
IF (NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_TableName_Description' | |
AND object_id = OBJECT_ID('dbo.TableName'))) |
OlderNewer