jpomfret

Import-Module dbatools

Get-DbaDatabase -SqlInstance Server1

jpomfret

$servers = Get-DbaRegServer -SqlInstance CmsServerName
$os = Get-DbaOperatingSystem -ComputerName $servers.name
$sql = Get-DbaProductKey -ComputerName $servers.name
 
$excelFile = C:\temp\VersionInfo.xlsx'
 
$osProps = 'ComputerName','Architecture','Version','Build','OSVersion', 'SPVersion', 'InstallDate','LastBootTime', 'ActivePowerPlan'
$sqlProps = 'ComputerName','InstanceName','SqlInstance','Version','Edition'
 
$osExcel = @{

jpomfret

$SQLSvcMSA = 'DOMAIN\MSASQLName$'
$AgtSvcMSA = 'DOMAIN\MSAAgentName$'

# These are required but not used since we are using MSAs
$SQLSvcAccount = New-Object System.Management.Automation.PSCredential($SQLSvcMSA, $('mypassword' | ConvertTo-SecureString -asPlainText -Force))
$AgtSvcAccount = New-Object System.Management.Automation.PSCredential($AgtSvcMSA, $('mypassword' | ConvertTo-SecureString -asPlainText -Force))


Configuration ServerBuild {
    Import-DscResource -ModuleName 'PSDscResources'

jpomfret

# Source for xlsx files
$SourceFolder = 'C:\Folder'

# Destination folder for where the csvs will go
$DestFolder = 'C:\Folder\csv'

# Foreach Excel file, read it in and then export to CSV with the same name.
Get-ChildItem $SourceFolder -Filter *.xlsx | 
ForEach-Object { Import-Excel  $_.FullName | Export-Csv -Path ("{0}\{1}.csv" -f $DestFolder, $_.basename) -NoTypeInformation }

jpomfret

# Source for xlsx files
$SourceFolder = 'C:\Folder'

# Destination folder for where the csvs will go
$DestFolder = 'C:\Folder\csv'

# Foreach Excel file, read it in and then export to CSV with the same name.
Get-ChildItem $SourceFolder -Filter *.xlsx | 
ForEach-Object { Import-Excel  $_.FullName | Export-Csv -Path ("{0}\{1}.csv" -f $DestFolder, $_.basename) -NoTypeInformation }

jpomfret

###########
## SETUP ##
###########

## get some data into xlsx files :)
$svr = Connect-DbaInstance -SqlInstance mssql1 -SqlCredential (Get-Credential)

foreach ($tbl in (Get-DbaDbTable -SqlInstance $svr -Database AdventureWorks2017 | where schema -eq 'sales' | select -first 10)){
    Invoke-DbaQuery -SqlInstance $svr -Database AdventureWorks2017 -Query "Select top 10 * from $($tbl.Schema).$($tbl.name)" |
    Export-Excel -Path "c:\temp\excel\$($tbl.name).xlsx"  -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors

jpomfret

# Define the Lab, specify the virtualization engine and the path for the VMs to live
New-LabDefinition -Name SQLLab -DefaultVirtualizationEngine HyperV -VmPath C:\AutomatedLab-VMs\VMs
 
# Configure the Lab Network
Add-LabVirtualNetworkDefinition -Name SQLLab
Add-LabVirtualNetworkDefinition -Name 'Default Switch' -HyperVProperties @{ SwitchType = 'External'; AdapterName = 'Wi-Fi' }
 
$netAdapter = @()
$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch SQLLab
$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch 'Default Switch' -UseDhcp

jpomfret

$labname = 'FailOverLab1'
New-LabDefinition -Name $labname -DefaultVirtualizationEngine HyperV -VmPath C:\AutomatedLab-VMs\VMs

Add-LabDomainDefinition -Name pomfret.com -AdminUser Install -AdminPassword Somepass1

Set-LabInstallationCredential -Username Install -Password Somepass1

Add-LabVirtualNetworkDefinition -Name $labname -AddressSpace 192.168.50.0/24

$PSDefaultParameterValues = @{

jpomfret

$PrimaryInstance = 'Sql1'
$SecondaryInstance = 'Sql2'
 
$SqlInstance = $PrimaryInstance, $SecondaryInstance
$Database = 'DbName'
 
$results = Get-DbaHelpIndex -SqlInstance $SqlInstance -Database $Database
 
$export = @()
 

jpomfret

## I'd like to copy logins/jobs but also script them out for source control

# get logins from source
$logins = Get-DbaLogin -SqlInstance $dbatools1

# first export, then copy
$logins | Export-DbaLogin -FilePath ('Export\Logins_{0}.sql' -f (get-date -f 'yyyyMMdd_hhmmss'))
$logins | Copy-DbaLogin -Destination $dbatools2