sqlsimon

<?xml version="1.0" encoding="utf-8"?>
<!-- Goes in C:\Windows\System32\Sysprep where -->
<unattend xmlns="urn:schemas-microsoft-com:unattend">
     <settings pass="oobeSystem">
        <component name="Microsoft-Windows-International-Core" processorArchitecture="amd64" publicKeyToken="31bf3856ad364e35" language="neutral" versionScope="nonSxS" xmlns:wcm="http://schemas.microsoft.com/WMIConfig/2002/State" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <InputLocale>0809:00000809</InputLocale>
            <SystemLocale>en-GB</SystemLocale>
            <UILanguage>en-GB</UILanguage>
            <UserLocale>en-GB</UserLocale>
        </component>

sqlsimon

#
# Script to set the computername to be the same as the name of the virtual machine and turn off password complexity and 
# expiration. We do that because we just use this for scrap, throwaway VMs
#
Set-ExecutionPolicy RemoteSigned

Enable-psremoting -Force 
 
 
$virtualmachinename= (Get-ItemProperty 'HKLM:\software\microsoft\virtual machine\guest\parameters').virtualmachinename 

sqlsimon

sqlserver2014express - sql 2014 express with management studio 
sqlserver2012express - sql 2014 express with management studio
git
git.install
visualstudioteamfoundationserverexpress2013
visualstudioexpress2012tfs
octopusdeploy.tentacle
octopusdeploy
octopustools

sqlsimon

cinst VisualStudioTeamFoundationServerExpress2013
cinst MsSqlServer2012Express
cinst visualstudio2013teamexplorer

$tfsConfig="$env:ProgramFiles\Microsoft Team Foundation Server 12.0\Tools\TfsConfig.exe"
.$tfsConfig unattend /configure /type:standard
.$tfsConfig unattend /configure /type:build `
  /inputs:collectionurl=http://localhost:8080/tfs`;ServiceAccountName="LOCAL SERVICE"`;ServiceAccountPassword="pass"

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)

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;

/*

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

*/

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

sqlsimon

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

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

$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