MikeFal

$set = 'abcdefghijklmnopqrstuvwxyz'.ToCharArray()
$rowcount = 2000
$tables = @('GUIDTest','NonGuidTest','SeqGUIDTest')

foreach($table in $tables){
    $Start = Get-Date

    for($row=0;$row -lt $rowcount; $row++){
        $orgname = ''
        for($char=0;$char -lt 20;$char ++){

MikeFal

#ServerList
$servers = @('PICARD','RIKER','KIRK','SPOCK')

#Using a Workflow
workflow SQLScript{
    foreach -Parallel ($server in $servers){
        Invoke-Sqlcmd -ServerInstance $server -InputFile C:\TEMP\TestScript.sql
    }
}

MikeFal

#raw example
$sourcepath = 'C:\DBBackups\AdventureWorks2012'
$targetpath = 'C:\DBBackups\Archive'

#You can use 'dir' instead of Get-ChildItem
Get-ChildItem $sourcepath *.bak | Sort-Object LastWriteTime -Descending | Select-Object -First 1 | Copy-Item -Destination $targetpath

#function
function Copy-LastSQLBackup{
    param([string]$sourcepath

MikeFal

#Script Parameters
param([string[]]$SqlNodes
  ,[PSCredential]$SetupCredential
  ,[PSCredential]$SqlSvcAccount
  ,[PSCredential]$AgtSvcAccount
  ,[string[]]$SqlAdmins)


Configuration SQLServer{
  param([string[]] $ComputerName

MikeFal

#Driver variables, will become params
$MaxConnections = 50
$Server= 'localhost'

#Set Initial collections and objects    
$SqlInstance = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
$DbConnections = @();
$dbs = $SqlInstance.Databases | Where-Object {$_.IsSystemObject -eq 0} 

#Build DB connection array

MikeFal

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$InstanceName = 'localhost'
$errlogs = (New-Object Microsoft.SqlServer.Management.Smo.Server $InstanceName).JobServer.EnumErrorLogs()
$FullLog = @()

$errlogs | ForEach-Object {$FullLog += $smosrv.JobServer.ReadErrorLog($_.ArchiveNo)}
$FullLog | Sort-Object LogDate -Descending

MikeFal

$eomstring = '20160131'
$files = dir C:\Backups\ *.bak | Where-Object {$_.Name -like "*$eomstring*"}

foreach($file in $files){
    #Script each restore. Remove Out-File to just restore
    Restore-SqlDatabase -ServerInstance localhost -Database $file.Name -BackupFile $file.fullname -Script | Out-File -Append C:\TEMP\EOMRestore.sql
}

MikeFal

$InstanceName = 'localhost'
$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
if($smo.Configuration.DefaultBackupCompression.ConfigValue -eq 0){
    $smo.Configuration.DefaultBackupCompression.ConfigValue = 1
    $smo.Configuration.Alter()
}


$smo.Configuration.DefaultBackupCompression

MikeFal

#backup your databases
#get a collection of databases
$dbs = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4"

#Get a formatted string for the datetime
$datestring =  (Get-Date -Format 'yyyyMMddHHmm')

#loop through the databases
foreach($db in $dbs.name){
    $dir = "C:\Backups\$db"

MikeFal

$relocate = @()
$file = Get-ChildItem C:\Temp\WideWorldImporters.bak
$dbname = (Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "RESTORE HEADERONLY FROM DISK='$($file.FullName)';").DatabaseName
$dbfiles = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "RESTORE FILELISTONLY FROM DISK='$($file.FullName)';"
foreach($dbfile in $dbfiles){
    if($dbfile.Type -eq 'L'){
        $newfile = Join-Path -Path 'E:\SQLServer\Log' -ChildPath $($dbfile.PhysicalName.SubString($dbfile.PhysicalName.LastIndexOf('\')))
    } else {
        $newfile = Join-Path -Path 'E:\SQLServer\Data' -ChildPath $($dbfile.PhysicalName.SubString($dbfile.PhysicalName.LastIndexOf('\')))
    }