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
#Requires -Modules SqlServer | |
#Requires -Modules ImportExcel | |
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#> | |
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables | |
<# Scenario #1 A) all Dimensions in a single file, | |
and B) each Fact table in their own file. #> | |
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #> | |
dir | WHERE { $_.name -like 'dim*' } | |
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
function Get-History2 | |
{ | |
Get-History | | |
SELECT Id, @{Label="TotalRunningTime";Expression={$_.EndExecutionTime - $_.StartExecutionTime}}, CommandLine, ExecutionStatus, StartExecutionTime, EndExecutionTime | |
} |
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
function Get-MachineInfo($ServerName="localhost") | |
{ | |
Get-WmiObject win32_computersystem -ComputerName $ServerName | | |
SELECT DNSHostName, | |
Manufacturer, | |
Model, | |
SystemType, | |
@{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}}, | |
NumberOfLogicalProcessors, | |
NumberOfProcessors, |
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
#Requires -Version 2.0 | |
<# | |
This Export-CSV behaves exactly like native Export-CSV | |
However it has one optional switch -Append | |
Which lets you append new data to existing CSV file: e.g. | |
Get-Process | Select ProcessName, CPU | Export-CSV processes.csv -Append | |
For details, see | |
http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/ |
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
function Find-MostRecentFullBackup([string]$ServerInstance, $database="master"){ | |
#$db = "Adventureworks" | |
$rs = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database msdb -Query " | |
WITH LatestBackupSet (database_name, BackupsAgo, backup_start_date, media_set_id) | |
AS | |
( | |
SELECT database_name | |
, ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_start_date DESC) AS 'BackupsAgo' | |
, backup_start_date |
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
<# 0A) Before any of this can work, you must have Docker Destop running. | |
You must also have the latest SqlServer module installed from the PowerShell Gallery.#> | |
Install-Module SqlServer | |
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #> | |
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak"; | |
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak"; | |
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile; | |
<# 1) Create a SQL-on-Linux Docker Container with just the code below. |
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
<# 0A) Before any of this can work, you must have Docker Destop running. | |
You must also have the latest SqlServer module installed from the PowerShell Gallery.#> | |
Install-Module SqlServer | |
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #> | |
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak"; | |
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak"; | |
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile; | |
<# 1) Create a SQL-on-Linux Docker Container with just the code below. |
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
<# PLEASE NOTE: I am not the original author of this function. | |
I found it online years ago, and have been using it ever since. | |
If you are the original author, please ping me and let me know, | |
so I can give you proper credit. | |
Based on another function in the PowerShell Gallery, the orginial author might be Nathan.Run() http://nathanhoneycutt.net/blog/a-pomodoro-timer-in-powershell/ | |
#> | |
Function Start-Pomodoro | |
{ | |
Param ( |
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
<# Step 0) | |
Put the dockerfile & AdventureWorks2016.bak into your c:\temp | |
#> | |
$dockerfileURL = "https://gist.githubusercontent.com/SQLvariant/ebe7fa49216badb6b53339818ca1eda9/raw/ded3e7f988309d311b6f389257e499cb66d5dd39/dockerfile"; | |
$dockerfile = c:\temp\BuildContainer_FromImage.ps1; | |
Invoke-WebRequest -Uri $dockerfileURL -OutFile $dockerfile; | |
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\temp | |
<# First, build the image #> |
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
<# This is the SSIS Project once it's deployed #> | |
$Proj = Get-Item 'SQLSERVER:\SSIS\localhost\SQL2017\Catalogs\SSISDB\Folders\ProviderSolution\Projects\TestSSISProject' | |
$Proj | Get-Member -MemberType Methods | |
<# This is the theory I have #> | |
[byte[]] $ProjBytes = $Proj.GetProjectBytes() | |
[System.IO.File]::WriteAllBytes('C:\temp\ASSISPrj.ispac',$ProjBytes) | |
<# Dont run this piece of code, you will hate me. #> | |
$Proj.GetProjectBytes() |