sqlsimon

#Requires -Version 5.1
<#
.SYNOPSIS
    Scans SSIS packages (.dtsx) or deployments (.ispac) for Script Tasks,
    Script Components, custom/third-party pipeline components, Execute Process
    Tasks, and connection managers using the legacy SQLNCLI11.1 provider.

.DESCRIPTION
    Accepts one or more inputs:
      * A .dtsx file  - scanned directly

sqlsimon

-- =============================================================================
-- SYSTEM$ALLOWLIST DAILY SNAPSHOT & DIFF TRACKER
-- =============================================================================
-- Overview:
--   1. ALLOWLIST_SNAPSHOTS      - stores a daily copy of SYSTEM$ALLOWLIST
--   2. ALLOWLIST_CHANGES        - stores detected adds / removes / type changes
--   3. SP_SNAPSHOT_ALLOWLIST()  - stored procedure that:
--        a) takes today's snapshot
--        b) diffs it against yesterday's snapshot
--        c) writes any differences to ALLOWLIST_CHANGES

sqlsimon

Spliting large files into 1GB chunks for copying. 

Go and get the portable 7za from https://www.7-zip.org/download.html Grab the one labelled
(7-Zip Extra: standalone console version, 7z DLL, Plugin for Far Manager) 

The following command will use the fastest compression and split into 1GB chunks

7za a c:\temp\<archive name>.7z c:\temp\<source_files>.* -v1000m -mx1

sqlsimon

Kit wise we have this stuff that we can pool: 

* camping table
* 4 x stoves
* 1 x bbq + charcoal, lighters etc (this will double as a firepit for after BBQ)
* 2 x cooking pots
* wooden skewers
* bbq tools 
* small kettle

sqlsimon

$InstallPath = $OctopusParameters["Octopus.Action[Deploy dbatools package].Output.Package.InstallationDirectoryPath"] + "\contents\"

Write-Host $InstallPath

Copy-Item -Path $InstallPath -Destination "C:\Program Files\WindowsPowershell\Modules\dbatools" -Recurse -Force

sqlsimon

CREATE SCHEMA [common] AUTHORIZATION [dbo]


CREATE TABLE [common].[sequence_test_id]
(
	 sequence_id bigint identity(1,1)
	,CONSTRAINT [pk_sequence_test_id] PRIMARY KEY (sequence_id)
)
GO

sqlsimon

:setvar SSISDB_NAME "SSISDb"
 
 
USE $(SSISDB_NAME)
 
SET NOCOUNT ON
SET XACT_ABORT ON
 
BEGIN TRANSACTION
 

sqlsimon

USE msdb;

/*

Get the runtime of sql agent job and job steps over the last 14 days

*/

DECLARE @JobName SYSNAME = '<job name here>'

sqlsimon

Use [msdb]
GO

create view [dbo].[vwRestoreHistory]
as
select
	 this_instance_name = @@SERVERNAME
	,source_server_name = left(bs.server_name, 40)
	,database_name = left(bs.database_name, 60)

sqlsimon

USE [msdb]
GO

CREATE view [dbo].[vwBackupHistory]
as

select
	 this_instance_name = @@SERVERNAME
	,source_server_name = left(bs.server_name, 40)
	,database_name = left(bs.database_name, 60)