Scripts
SQL Server
Tables by size descending
CREATE TABLE #TableSizes
(
TableName NVARCHAR(255),
TableRows INT,
ReservedSpaceKB VARCHAR(20),
DataSpaceKB VARCHAR(20),
IndexSizeKB VARCHAR(20),
UnusedSpaceKB VARCHAR(20)
)
INSERT INTO #TableSizes
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT * FROM #TableSizes
ORDER BY TableRows DESC
DROP TABLE #TableSizes
Create Database, Login and User
CREATE DATABASE finprod
ON
( NAME = finprod_data,
FILENAME = 'C:\0SQL\finprod_data.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB)
LOG ON
( NAME = finprod_log,
FILENAME = 'C:\0SQL\finprod_log.ldf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 100MB)
COLLATE Latin1_General_CI_AS
GO
ALTER DATABASE finprod
SET
AUTO_CREATE_STATISTICS ON,
AUTO_UPDATE_STATISTICS ON,
ANSI_NULL_DEFAULT ON,
READ_COMMITTED_SNAPSHOT ON,
PAGE_VERIFY NONE,
RECOVERY Simple
GO
EXEC finprod.dbo.sp_fulltext_database ENABLE
GO
IF NOT EXISTS (SELECT LOGINNAME FROM MASTER..SYSLOGINS WHERE NAME = 'finprod')
BEGIN
CREATE LOGIN finprod
WITH
PASSWORD = 'F1nPr0D',
DEFAULT_DATABASE = finprod,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
END
GO
USE finprod
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'techone')
DROP USER techone
CREATE USER techone FOR LOGIN finprod
EXEC SP_ADDROLEMEMBER 'db_datareader', 'techone'
EXEC SP_ADDROLEMEMBER 'db_datawriter', 'techone'
EXEC SP_ADDROLEMEMBER 'db_ddladmin', 'techone'
GRANT EXECUTE ON SCHEMA::dbo TO techone
Resetting the Techone PW to 'T3chn0l0gy'
UPDATE
F1_USER_PROFILE
SET
PASSWORD = '!!4ACc:5j%2N9!g',
ACCOUNT_DISABLED
= 'N',
COMMENCEMENT_DATEI
= '1900-01-01 00:00:00.000',
TERMINATION_DATEI
= '1900-01-01 00:00:00.000',
PASSWD_MIN_LENGTH
= 6,
PASSWD_DURATION
= 9999
WHERE
USER_ID = 'TECHONE'
The T-SQL Replace Command
USE fintrain
GO
UPDATE F1_DIRCD_CTL
SET DIR_PATH = REPLACE(DIR_PATH, ' FMVDPSPRD01', ' FMVDPSTRN01')
GO
Changing SQL Object Owner to dbo
Deleting Rows from large tables in a transaction
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
BEGIN TRANSACTION
-- Delete some small number of rows at a time
DELETE TOP (10000) tbdpj_job_log
WHERE not exists (select 'x' from tbdpj_job_ctl c where c.job_nbr = tbdpj_job_log.job_nbr)
SET @Deleted_Rows = @@ROWCOUNT;
COMMIT TRANSACTION
CHECKPOINT -- for simple recovery model
END
Powershell
Script to obtain subfolder sizes
$startFolder = "\\Techone\Techone\Data\finprod\*"
$colItems = (Get-ChildItem $startFolder | Where-Object {$_.PSIsContainer -eq $True}| Sort-Object)
foreach ($i in $colItems)
{
$subFolderItems = (Get-ChildItem $i.FullName -recurse | Measure-Object -property length -sum -EA SilentlyContinue)
$i.FullName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1MB) + " MB -- " + ($subFolderItems.Count) + " items"
}
Adding Roles and Features without Media
# LIST All IIS FEATURES:
# Get-WindowsOptionalFeature -Online | where FeatureName -like 'IIS-*'
Enable-WindowsOptionalFeature -Online -FeatureName IIS-WebServerRole
Enable-WindowsOptionalFeature -Online -FeatureName IIS-WebServer
Enable-WindowsOptionalFeature -Online -FeatureName IIS-CommonHttpFeatures
Enable-WindowsOptionalFeature -Online -FeatureName IIS-DefaultDocument
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HttpErrors
Enable-WindowsOptionalFeature -Online -FeatureName IIS-StaticContent
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HttpRedirect
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HealthAndDiagnostics
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HttpLogging
Enable-WindowsOptionalFeature -Online -FeatureName IIS-LoggingLibraries
Enable-WindowsOptionalFeature -Online -FeatureName IIS-RequestMonitor
Enable-WindowsOptionalFeature -Online -FeatureName IIS-Performance
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HttpCompressionStatic
Enable-WindowsOptionalFeature -Online -FeatureName IIS-HttpCompressionDynamic
Enable-WindowsOptionalFeature -Online -FeatureName IIS-Security
Enable-WindowsOptionalFeature -Online -FeatureName IIS-RequestFiltering
Enable-WindowsOptionalFeature -Online -FeatureName IIS-BasicAuthentication
Enable-WindowsOptionalFeature -Online -FeatureName IIS-URLAuthorization
Enable-WindowsOptionalFeature -Online -FeatureName IIS-WindowsAuthentication
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ApplicationDevelopment
Enable-WindowsOptionalFeature -online -FeatureName NetFx4Extended-ASPNET45
Enable-WindowsOptionalFeature -Online -FeatureName IIS-NetFxExtensibility
Enable-WindowsOptionalFeature -Online -FeatureName IIS-NetFxExtensibility45
Enable-WindowsOptionalFeature -Online -FeatureName IIS-CGI
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ServerSideIncludes
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ApplicationInit
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ISAPIExtensions
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ISAPIFilter
Enable-WindowsOptionalFeature -Online -FeatureName IIS-WebSockets
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ASP
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ASPNET
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ASPNET45
Enable-WindowsOptionalFeature -Online -FeatureName IIS-WebServerManagementTools
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ManagementConsole
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ManagementScriptingTools
Enable-WindowsOptionalFeature -Online -FeatureName IIS-ManagementService
Script to gather server information
# Powershell Script: Gather System Information
# Author: Bret Morris
# Date: 10th April 2014
#
# Added IIS section - 20th November 2015
# Added Event Viewer section - 21st November 2015
# Formatting for Disk Sizes - 22nd November 2015
#
# If when running the Powershell you get "execution of scripts is disabled on this system" run the following
# Set-ExecutionPolicy RemoteSigned
# Once completed to reset execution policy run
# Set-ExecutionPolicy Restricted
# To view current ExecutionPolicy run
# Get-ExecutionPolicy
#
Write-Output "System details for TechnologyOne"
Write-Output " "
$boolComputerNotFound = $True
Write-Output "Enter the computer name (or IP address) of the computer you want to collect"
Write-Output "information for. If the computer cannot be found you will be prompted to "
Write-Output "re-enter the computer name or IP. You can use . instead of the name for "
Write-Output "the local computer. You will need to have local administrator rights on"
Write-Output "any server you require details for."
Write-Output " "
$strComputer = Read-Host 'What computer would you like to gather details for?'
if (!$strComputer.trim()) {
$strComputer = "."
}
Write-Output " "
While ($boolComputerNotFound)
{
if ($strComputer -eq ".") {
$boolComputerNotFound = $False
}
else
{
echo " "
echo "Looking for $strComputer. Please wait ..."
if (Test-Connection -ComputerName $strComputer.trim() -Quiet) {
$boolComputerNotFound = $False
echo "$strComputer found."
echo " "
}
else
{
echo "$strComputer not found."
echo " "
$strComputer = Read-Host 'Enter another computer you would like to gather details for?'
if (!$strComputer.trim()) {
$strComputer = "."
}
}
}
}
if ($strComputer -eq ".") {
$strCompName = $env:computername
}
else
{
$strCompName = $strComputer
}
$Out = "$Home\Documents\SystemInfo-" + $strCompName + ".txt"
Write-Output "Output to $Out"
Write-Output " "
# Display the Date
Get-Date | Out-File $Out
# Display Computer properties
Write-Output "Getting Computer Properties"
echo "===================" | Out-File $Out -Append
echo "COMPUTER PROPERTIES" | Out-File $Out -Append
echo "===================" | Out-File $Out -Append
Get-WmiObject -Class Win32_ComputerSystem -ComputerName $strComputer | Select-Object -Property Name, Manufacturer, Model, SystemType, Domain | Out-File $Out -Append -width 120
# Get System Directory
Get-WmiObject -Class Win32_OperatingSystem -ComputerName $strComputer | Select-Object SystemDirectory | Out-File $Out -Append -width 120
# Get Locale
[System.Globalization.CultureInfo]([int]("0x" + (Get-WmiObject -ComputerName $strComputer Win32_OperatingSystem).locale)) | Select-Object -Property @{Name="Locale";Expression={($_.DisplayName)}} | Out-File $Out -Append -width 120
# Display CPU properties
Write-Output "Getting CPU Properties"
echo "==============" | Out-File $Out -Append
echo "CPU PROPERTIES" | Out-File $Out -Append
echo "==============" | Out-File $Out -Append
Get-WmiObject -Class Win32_Processor -ComputerName $strComputer | Select-Object -property name, NumberOfLogicalProcessors | Out-File $Out -Append -width 120
# Display Memory properties
Write-Output "Getting Memory Properties"
echo "=================" | Out-File $Out -Append
echo "MEMORY PROPERTIES" | Out-File $Out -Append
echo "=================" | Out-File $Out -Append
Get-WmiObject -Class Win32_PhysicalMemory -ComputerName $strComputer | Select-Object -property Caption, Tag, @{Name="Size - Gb";Expression={"{0:N2}" -f ($_.Capacity / 1Gb)}}| Out-File $Out -Append -width 120
# Display Pagefile properties
Write-Output "Getting Pagefile Properties"
echo "===================" | Out-File $Out -Append
echo "PAGEFILE PROPERTIES" | Out-File $Out -Append
echo "===================" | Out-File $Out -Append
Get-WmiObject -ComputerName $strComputer Win32_PageFileusage | Select-Object Name, @{Name="Size - MB";Expression={($_.AllocatedBaseSize)}} | Out-File $Out -Append -width 120
# Display Network Address properties
Write-Output "Getting Network Address"
echo "===============" | Out-File $Out -Append
echo "NETWORK ADDRESS" | Out-File $Out -Append
echo "===============" | Out-File $Out -Append
Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $strComputer | where{$_.IPEnabled -eq "True"} | Select-Object -property Description, IPAddress, DHCPEnabled | Out-File $Out -Append -width 120
# Display Operating System properties
Write-Output "Getting Operating System Properties"
echo "===========================" | Out-File $Out -Append
echo "OPERATING SYSTEM PROPERTIES" | Out-File $Out -Append
echo "===========================" | Out-File $Out -Append
Get-WmiObject -Class Win32_OperatingSystem -ComputerName $strComputer | Select-Object -property Caption, OSArchitecture, CSDVersion | Out-File $Out -Append -width 120
# Display Disks properties
Write-Output "Getting Disks Properties"
echo "================" | Out-File $Out -Append
echo "DISKS PROPERTIES" | Out-File $Out -Append
echo "================" | Out-File $Out -Append
Get-WmiObject -Class Win32_LogicalDisk -Filter "DriveType=3" -ComputerName $strComputer | Select-Object –property DeviceID , @{Name="Size - Gb";Expression={"{0:N2}" -f ($_.Size / 1Gb)}}, @{Name="Free - Gb";Expression={"{0:N2}" -f ($_.FreeSpace / 1Gb)}}, FileSystem | Out-File $Out -Append -width 120
Get-WmiObject -Class Win32_LogicalDiskToPartition -ComputerName $strComputer |Select-Object -Property @{Name="Partition";Expression={($_.Antecedent.split("=") | select -skip 1)}}, @{Name="Drive";Expression={($_.Dependent.split("=") | select -skip 1)}} | Out-File $Out -Append -Width 120
$cd=Get-WmiObject win32_logicaldisk -ComputerName $strComputer -filter 'DriveType=5' | Select-Object -property @{Name="CD Drive";Expression={($_.DeviceID)}}
if($cd)
{
Get-WmiObject win32_logicaldisk -ComputerName $strComputer -filter 'DriveType=5' | Select-Object -property @{Name="CD Drive";Expression={($_.DeviceID)}} | Out-File $Out -Append -Width 120
}
else
{
echo "No CD Drive" | Out-File $Out -Append
}
Write-Output "`n" | Out-File $Out -Append
Write-Output "`n" | Out-File $Out -Append
# Display DP Service
Write-Output "Looking for DP"
echo "=====================" | Out-File $Out -Append
echo "DISTRIBUTED PROCESSOR" | Out-File $Out -Append
echo "=====================" | Out-File $Out -Append
$dp = get-service -ComputerName $strComputer | where{$_.Name -eq "t1distproc"}
if($dp)
{
get-service -ComputerName $strComputer | Select-Object -property DisplayName, Name, Status | where{$_.Name -eq "t1distproc"} | Out-File $Out -Append -width 120
}
else
{
echo "Distributed Processor not installed on this server." | Out-File $Out -Append
}
# Display IIS Properties
Write-Output "Looking for IIS"
echo "===================================" | Out-File $Out -Append
echo "Internet Information Services (IIS)" | Out-File $Out -Append
echo "===================================" | Out-File $Out -Append
Write-Output "`n" | Out-File $Out -Append
$iis = get-wmiobject Win32_Service -ComputerName $strComputer -Filter "name='W3SVC'"
if($iis.State -eq "Running")
{
Write-Output "IIS is running" | Out-File $Out -Append
}
else
{
Write-Output "IIS is not running on this server" | Out-File $Out -Append
}
Write-Output "`n" | Out-File $Out -Append
Write-Output "`n" | Out-File $Out -Append
# Display system and application event log errors (last 5 of each)
Write-Output "Getting System Event Log Errors - 5 most recent"
echo "=======================================" | Out-File $Out -Append
echo "SYSTEM EVENT LOG ERRORS (5 most recent)" | Out-File $Out -Append
echo "=======================================" | Out-File $Out -Append
Get-EventLog system -ComputerName $strComputer -newest 5 -EntryType Error | Format-List | Out-File $Out -Append
Write-Output "Getting Application Event Log Errors - 5 most recent"
echo "============================================" | Out-File $Out -Append
echo "APPLICATION EVENT LOG ERRORS (5 most recent)" | Out-File $Out -Append
echo "============================================" | Out-File $Out -Append
Get-EventLog application -ComputerName $strComputer -newest 5 -EntryType Error | Format-List | Out-File $Out -Append
Write-Output "Looking for Technology One Application Event Log Errors - 20 most recent"
echo "============================================================" | Out-File $Out -Append
echo "TECHNOLOGY ONE APPLICATION EVENT LOG ERRORS (20 most recent)" | Out-File $Out -Append
echo "============================================================" | Out-File $Out -Append
$t1event1 = Get-EventLog application -ComputerName $strComputer -newest 20 -EntryType Error | Where-Object -FilterScript {$_.Message -like "*TechnologyOne*" -or $_.Message -like "*Technology One*" -or $_.Message -like "*T1*"}
if($t1event1)
{
Get-EventLog application -ComputerName $strComputer -newest 20 -EntryType Error | Where-Object -FilterScript {$_.Message -like "*TechnologyOne*" -or $_.Message -like "*Technology One*" -or $_.Message -like "*T1*"} | Format-List | Out-File $Out -Append
}
else
{
Write-Output "`n" | Out-File $Out -Append
Write-Output "No Technology One Application Events" | Out-File $Out -Append
}
Write-Output "`n" | Out-File $Out -Append
Write-Output "`n" | Out-File $Out -Append
Write-Output "Looking for Technology One System Event Log Errors - 20 most recent"
echo "=======================================================" | Out-File $Out -Append
echo "TECHNOLOGY ONE SYSTEM EVENT LOG ERRORS (20 most recent)" | Out-File $Out -Append
echo "=======================================================" | Out-File $Out -Append
$t1event2 = Get-EventLog system -ComputerName $strComputer -newest 20 -EntryType Error | Where-Object -FilterScript {$_.Message -like "*TechnologyOne*" -or $_.Message -like "*Technology One*" -or $_.Message -like "*T1*"}
if($t1event2)
{
Get-EventLog system -ComputerName $strComputer -newest 20 -EntryType Error | Where-Object -FilterScript {$_.Message -like "*TechnologyOne*" -or $_.Message -like "*Technology One*" -or $_.Message -like "*T1*"} | Format-List | Out-File $Out -Append
}
else
{
Write-Output "`n" | Out-File $Out -Append
Write-Output "No Technology One System Events" | Out-File $Out -Append
}
Write-Output "`n" | Out-File $Out -Append
Write-Output "`n" | Out-File $Out -Append
# Display TechnologyOne Pre-requisites Installed
Write-Output "Getting Installed Programs List"
echo "==================" | Out-File $Out -Append
echo "INSTALLED PROGRAMS" | Out-File $Out -Append
echo "==================" | Out-File $Out -Append
Get-WmiObject -Class Win32_Product -ComputerName $strComputer | Select-Object -property Name, Version, Vendor |Sort-Object Name | Out-File $Out -Append -width 120
Other
Robocopy
robocopy <SOURCE> <DESTINATION> /E /SEC /XD hold log
/E = everything files and folders
/SEC = security
/XD = exclude the following directories
/Z = Copy files in restartable mode
/NP = No Progress - Don't display percentage copied
/R:1 = Retries on failed
/W:1 = Wait time between retries
/PURGE = Delete files from destination if not in source
/MT:32 = Multi-threaded with number of threads
/LOG:file = Output to file (overwrite)
/LOG+:file = Output to file (append)
/TEE = Output to screen as well as log file
/MAXAGE:n = Ignore files older than n days
i.e. Robocopy \\techone\techone E:\Techone /E /SEC /Z /NP /R:1 /W:1 /PURGE /MT:32 /LOG:E:\Robocopy\DeltaCopy1.txt /TEE