PowerShell Scripts based on Sean McCown's Videos

<############################################# How to script tables, USPs etc ##############################################>
#Scripting all tables
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> gci | %{$_.script()}

# How to script tables with options
# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the script option object
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so=new-object microsoft.sqlserver.management.smo.scriptingoptions

# Step 3 - Check the properties and methods avialable to the script option object
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $so | gm

# Step 4 - Now we can set some properties such as IncludeDatabaseContext or NoFileGroup and pass that to the script() method.
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.IncludeDatabaseContext=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> $SO.NoFileGroup=1

# The script below works fine in SQL Server 2008/R2, however, the script{$so) causes an error in SQL Server 2012 as below:
# script : Cannot find an overload for "Script" and the argument count: "1".
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2012\tables> gci | % {$_.Script($so)}

# If we want the results from multiple cmdlets concatenated, we use the append parameter.
PS SQLSERVER:\sql\xu_xps\R2\databases\adventureworks2008r2\tables> gci | % {$_.Script($so)} | Out-File c:\ScriptingTables.txt -append

<################################################## Tricks ###################################################>
# Use -noexit to make the display window stay if the result screen disappears very quick (e.g., run powershell c:\service.ps1 in Run)
powershell -noexit c:\service.ps1

<############################################ How to find a job owner and change it #############################################>
# find the jobs owned by the login 'sa'
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |ft name,OwnerLoginName -autosize

# change the job owner to xu_xps\charlie
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |% {$_.Set_OwnerLoginName("xu_xps\charlie")}

# Verify the jobs have new owners
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |ft name,OwnerLoginName -autosize

#The change is only in the current session. You have to call two additional methods to make the change permenent on the server
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | % {$_.alter()}
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | % {$_.refresh()}

# After that, you can verify that in SQL Server Agent

# We can do them all in one line
PS SQLSERVER:\sql\xu_xps\sql2008\jobserver\jobs> gci | ? {$_.OwnerLoginName -eq 'sa'} |% {$_.Set_OwnerLoginName("xu_xps\charlie");$_.alter();$_.refresh()}

<###################################################### How to Grant and Revoke Schema Permissions ######################################>
# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the object permssion
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s=new-object microsoft.sqlserver.management.smo.ObjectPermissionSet

# Step 3 - Check the properties and methods avialable to the bject
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s | gm

# Step 4 - Now we can set some properties for the permission object. Let's we want to grand Select, Execute, and View Definition to xu_xps\charlie for all the schemmas
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.ViewDefinition=1

# The script below works fine in SQL Server 2008/R2, however, the script{$s) causes an error in SQL Server 2012
# xu_xps\charlie should now have select, execute, and view defintion permissions on all of the schemas in the current PS session and the database
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

# Step 5 - Now let's set Select and Execute to false (leaving ViewDefinition to true)
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

<# You thought the Select and Execute permssions for Charlie on all of the schemas are changed to false.
 Unfortunately they don't. All of the schmas still have Select, Execute, and View Definition permission.
 The changes are only effective in the PS session, they do not affect the values in the database even with the following command:
 gci | % {$_.Grant($s,"xu_xps\charlie");$_.Alter();S_.Refresh()}. But when you execute the revoke command, it flips the values
 for Select, Execute, and View Definition in the current PS session and that will hit the database, making the permenent changes.
 As the current value for Select, Execute, and View Definition is False, False, and True. The revoke command will make them True, True, and False in the database.
#>

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.revoke($s,"xu_xps\charlie");$_.alter();$_.refresh()}

<###################################################### How to Grant and Revoke Schema Permissions ######################################>
# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the object permssion
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s=new-object microsoft.sqlserver.management.smo.ObjectPermissionSet

# Step 3 - Check the properties and methods avialable to the bject
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $s | gm

# Step 4 - Now we can set some properties for the permission object. Let's we want to grand Select, Execute, and View Definition to xu_xps\charlie for all the schemmas
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=1
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.ViewDefinition=1

# The script below works fine in SQL Server 2008/R2, however, the script{$s) causes an error in SQL Server 2012
# xu_xps\charlie should now have select, execute, and view defintion permissions on all of the schemas in the current PS session and the database
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

# Step 5 - Now let's set Select and Execute to false (leaving ViewDefinition to true)
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Select=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> $S.Execute=0
PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.Grant($s,"xu_xps\charlie")}

<# You thought the Select and Execute permssions for Charlie on all of the schemas are changed to false.
 Unfortunately they don't. All of the schmas still have Select, Execute, and View Definition permission.
 The changes are only effective in the PS session, they do not affect the values in the database even with the following command:
 gci | % {$_.Grant($s,"xu_xps\charlie");$_.Alter();S_.Refresh()}. But when you execute the revoke command, it flips the values
 for Select, Execute, and View Definition in the current PS session and that will hit the database, making the permenent changes.
 As the current value for Select, Execute, and View Definition is False, False, and True. The revoke command will make them True, True, and False in the database.
#>

PS SQLSERVER:\sql\xu_xps\default\databases\adventureworks2008\schemas> gci | % {$_.revoke($s,"xu_xps\charlie");$_.alter();$_.refresh()}

<########################################### How to Grant and Revoke Database Permissions ##################################>
# Step 1 - We need to load the .NET assembly
PS SQLSERVER:\sql\xu_xps\default\databases> [reflection.assembly]::loadwithpartialname("Microsoft.SQlServer.smo")

# Step 2 - We create a variable to instantiate the object permssion
PS SQLSERVER:\sql\xu_xps\default\databases> $s=new-object microsoft.sqlserver.management.smo.DatabasePermissionSet

# Step 3 - Check the properties and methods avialable to the bject
PS SQLSERVER:\sql\xu_xps\default\databases> $s | gm

# Step 4 - Now we can set some properties for the permission object. Let's we want to grand Select, Execute, and View Definition to xu_xps\charlie for all the schemmas
PS SQLSERVER:\sql\xu_xps\default\databases> $S.Select=1
PS SQLSERVER:\sql\xu_xps\default\databases> $S.Execute=1
PS SQLSERVER:\sql\xu_xps\default\databases> $S.ViewDefinition=1

# The script below works fine in SQL Server 2008/R2, however, the script{$s) causes an error in SQL Server 2012
# the database user xu_xps\charlie should now have select, execute, and view defintion permissions on all of the databases if the databases have that user account
PS SQLSERVER:\sql\xu_xps\default\databasess> gci | % {$_.Grant($s,"xu_xps\charlie")}

# Step 5 - Now let's set Select and Execute to false (leaving ViewDefinition to true)
PS SQLSERVER:\sql\xu_xps\default\databases> $S.Select=0
PS SQLSERVER:\sql\xu_xps\default\databases> $S.Execute=0
PS SQLSERVER:\sql\xu_xps\default\databases> gci | % {$_.Grant($s,"xu_xps\charlie")}

<############################################ How to Write BCP Routine #############################################>
# Step 1 - Output a table to a .dat or txt file. Do the same thing for other four tables
bcp AdventureWorks2012.Sales.Currency out BCP1.dat -S localhost\InstanceName -T -c

# Step 2 - In SSMS, create a blank table in the database with the same structure as the original table for inputing. Do the same for other four tables
<#
USE AdventureWorks2012;
GO
SELECT * INTO AdventureWorks2012.Sales.BCP1
FROM AdventureWorks2012.Sales.Currency WHERE 1=2;
#>

# Step 3 -Input the dat or txt file into the table. Do the same thing for other four tables
bcp AdventureWorks2012.Sales.BCP1 in BCP1.dat -S localhost\InstanceName -T -c

# Step 4 - In SSMS, verify that you have 5 BCP tables
SELECT * FROM sys.sysobjects
WHERE name like '%BCP%'

# Step 5 - Create a tables.txt file with the five BCP table names in it as below:
BCP1
BCP2
BCP3
BCP4
BCP5

# Step 6 - check the content of the file in PowerShell:
PS SQLSERVER:\> get-content -path C:\Users\Charlie\Documents\tables.txt

# Step 7 - It's better to edit the code in PowerShell ISE at this point. Output the tables into native text file.
get-content .\tables.txt | % {$BCPTable=$_;                                    # Create a variable to hodl the BCP Table name
    $Path="C:\Users\Charlie\Documents\$BCPTable" + " -n.txt";                  # Create a variable to Specify the file location
    BCP AdventureWorks2008R2.Sales.$BCPTable OUT $Path -S LocalHost\R2 -T -n   # This is it! It will bcp out all tables in a single command. R2 is the instance name
}

# Step 8 - Save the above code as BCPOut.ps1 and execute it at the right location or specify the BCPOut.ps1 location.
PS C:\users\charlie\documents> .\bcpout

# Step 9 - We can zip the output file if needed. We need to download and use the gzip tool. After the zip, five zip files are created for bcp out.
get-content .\tables.txt | % {$BCPTable=$_;
    $Path="C:\Users\Charlie\Documents\$BCPTable" + " -n.txt";
    BCP AdventureWorks2008R2.Sales.$BCPTable OUT $Path -S LocalHost\R2 -T -n;
    C:\Users\Charlie\Downloads\gzip124xN\gzip.exe $Path
}

# Step 10 - checking the cost or progress through a status file (assuming the bcp takes hours to complete).
get-content .\tables.txt | % {
    $BCPTable=$_;
    $Path="C:\Users\Charlie\Documents\$BCPTable" + " -n.txt";
  
    $Date=get-Date;
    "$Date" + " Starting BCP: " + "$BCPTable" | Out-File C:\Users\Charlie\Documents\Status.txt -Append
    BCP AdventureWorks2008R2.Sales.$BCPTable OUT $Path -S LocalHost\R2 -T -n;
    $Date2=get-Date;
    $DateDiff = ($Date2-$Date).Milliseconds
    "$Date2" + " Ending BCP: " + "$BCPTable" + " --- This BCP takes " + $DateDiff +" milliseconds." | Out-File C:\Users\Charlie\Documents\Status.txt -Append
 
    $Date=get-Date;
    "$Date" + " Starting Zip: " + "$BCPTable" | Out-File C:\Users\Charlie\Documents\Status.txt -Append
    C:\Users\Charlie\Downloads\gzip124xN\gzip.exe $Path
    $Date2=get-Date;
    $DateDiff = ($Date2-$Date).Milliseconds
    "$Date2" + " Ending Zip: " + "$BCPTable" + " --- This Zip takes " + $DateDiff +" milliseconds." | Out-File C:\Users\Charlie\Documents\Status.txt -Append

}

<###### Output as below
01/24/2015 13:14:45 Starting BCP: BCP1
01/24/2015 13:14:45 Ending BCP: BCP1 --- This BCP takes 141 milliseconds.
01/24/2015 13:14:45 Starting Zip: BCP1
01/24/2015 13:14:46 Ending Zip: BCP1 --- This Zip takes 14 milliseconds.
01/24/2015 13:14:46 Starting BCP: BCP2
01/24/2015 13:14:46 Ending BCP: BCP2 --- This BCP takes 274 milliseconds.
01/24/2015 13:14:46 Starting Zip: BCP2
01/24/2015 13:14:46 Ending Zip: BCP2 --- This Zip takes 535 milliseconds.
01/24/2015 13:14:46 Starting BCP: BCP3
01/24/2015 13:14:47 Ending BCP: BCP3 --- This BCP takes 436 milliseconds.
01/24/2015 13:14:47 Starting Zip: BCP3
01/24/2015 13:14:47 Ending Zip: BCP3 --- This Zip takes 576 milliseconds.
01/24/2015 13:14:47 Starting BCP: BCP4
01/24/2015 13:14:48 Ending BCP: BCP4 --- This BCP takes 164 milliseconds.
01/24/2015 13:14:48 Starting Zip: BCP4
01/24/2015 13:14:48 Ending Zip: BCP4 --- This Zip takes 90 milliseconds.
01/24/2015 13:14:48 Starting BCP: BCP5
01/24/2015 13:14:48 Ending BCP: BCP5 --- This BCP takes 102 milliseconds.
01/24/2015 13:14:48 Starting Zip: BCP5
01/24/2015 13:14:48 Ending Zip: BCP5 --- This Zip takes 87 milliseconds.
##########>

<###################################################### How to Replace Text #######################################################>
# Suppose you have a folder called SPs, under the folder, you have 1000 txt files, each with certain texts such as SET ANSI_NULL ON, you want to gt rid of it.
PS C:\Users\Charlie\Documents\SPs> $i=1; $ct=(gci *.txt).count; gci *.txt | % {(Get-Content $_)| % {$_ -replace "SET ANSI_NULLS ON",""} | set-content $_; "$i of $ct"; $i=$i+1}
# The screen will show the progress and the content will be properly placed. An example:
<#
1 of 1000
2 of 1000
....
#>

<###################################################### How to Use $Args and Param #######################################################>
# Step 1: Suppose I have a foo.ps1 file as below
 #---- Begin script foo.ps1 ----
 "`nOption 1: "
Write-Host "Num Args:" $args.Length;
foreach ($arg in $args)
{
 Write-Host "Arg: $arg";
}
 #----  End script foo.ps1  ----

 "`nOption 2: "
$ServerName=$args[0]
$Location=$args[1]
"Server Name: $ServerName"
"Location: $Location"

 "`nFor Option 3: You need to comment out the code above as Param needs to be the 1st line of the code. Othewise, you will get an error as below: "
param ($ServerName1, $Location2)

"Server Name: $ServerName1"
"Location: $Location2"

# Step 2: Then I call the script as below

PS C:\> .\foo.ps1 myServer myLocation

# Step 3: I would get the result as below:
<#
Option 1:
Num Args: 2
Arg: myServer
Arg: myLocation

Option 2:
Server Name: myServer
Location: myLocation

For Option 3: You need to comment out the code above as Param needs to be the 1st line of the code. Othewise, you will get an error as below:
param : The term 'param' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
At C:\foo.ps1:17 char:1
+ param ($ServerName1, $Location2)
+ ~~~~~
    + CategoryInfo          : ObjectNotFound: (param:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Server Name:
Location:
#>

<###################################################### How to find all MDF files in C Drive ##################################################>
PS C:\>Dir -recurse -include *.mdf | ft FullName

<######################################## How to script all of the indexes for all tables in a database################################>
# Option 1 - Scripting all indexes to the console from PS SQLSERVER:\SQL\xu_xps\DEFAULT\DATABASES\ADVENTUREWORKS2012\TABLES
PS SQLSERVER:\SQL\xu_xps\DEFAULT\DATABASES\ADVENTUREWORKS2012\TABLES> GCI | % {ForEach ($ix in $_.Indexes){Write-Host $ix.Script()}}

# Option 2 - Scripting all indexes to the console from Windows PowerShell (not ISE!)

# Step 1 - Assume you have the ScriptIndexes.ps1 as below in c:\users\charlie\documents
#GET-LOCATION
CD SQLSERVER:\  #--very very critial to have "\" here, otherwise, it will get the wrong path!
#Get-Location
CD SQL\LOCALHOST\DEFAULT\DATABASES\REPORTSERVER\TABLES
#Get-Location
DIR | % {`
            CD \;
            $PATH=$_.URN.ToString() | convert-urnToPath;
            cd $Path\Indexes;
            GCI | % {$_.Script() }
            }
#Get-Location
cd c:
#Get-Location

Step 2: - Execute the code as below:
PS C:\users\charlie\documents> . ./ScriptIndexes.ps1 # The . ./ is for dot sourcing.

# It is easy to check the compatibility level for all of the databases in a SQL Server instance as below. But it's hard to change them.
# You can change it one by one, or use a cursor to loop through.
# --T-SQL query: select name, compatibility_level from sys.databases

# Alternatively, 2 ways in PowerShell to chanage them:

PS SQLSERVER:\sql\localhost\default\databases> gci | % {$_.Set_compatibilityLevel(100);$_.Alter()}

PS SQLSERVER:\sql\localhost\default\databases> gci | % {$_.CompatibilityLevel=120;$_.Alter()}

PS SQLSERVER:\sql\localhost\default\databases> gci | ft Name, CompatibilityLevel -autosize

################ How to Dig into Details of a method in PS #################

# If you this cmldlet, the screen displays the members for a database very fast, hard to see the details
PS SQLSERVER:\sql\localhost\default\databases> gci | gm

# Save the result into a variable
PS SQLSERVER:\sql\localhost\default\databases> $a= dir | gm # also can be $a = gci | gm

# the same as before
PS SQLSERVER:\sql\localhost\default\databases> $a

# all of the members (e.g., event, property and methdo) for a database has the the following members
PS SQLSERVER:\sql\localhost\default\databases> $a | gm

<#
   TypeName: Microsoft.PowerShell.Commands.MemberDefi

Name        MemberType Definition
----        ---------- ----------
Equals      Method     bool Equals(System.Object obj)
GetHashCode Method     int GetHashCode()
GetType     Method     type GetType()
ToString    Method     string ToString()
Definition  Property   System.String Definition {get;
MemberType  Property   System.Management.Automation.P
Name        Property   System.String Name {get;}
TypeName    Property   System.String TypeName {get;}
#>

# For instance, I want to if there is a member having alter in it
PS SQLSERVER:\sql\localhost\default\databases> $a | ? {$_.name -match "alter"} |fl

# Find all methods for databses
PS SQLSERVER:\sql\localhost\default\databases> $a | ? {$_.MemberType -match "Method"}

# Find all properties for databases
PS SQLSERVER:\sql\localhost\default\databases> $a | ? {$_.MemberType -match "Property"}

################ How to Add Users into a Windows Group #################

# Need to open PowerShell as admin
# It would be the same for a domain account


PS C:\Windows\system32> $objUser=[ADSI]("WinNT://Xu-Acer-PC/PSTest")
PS C:\Windows\system32> $objGroup=[ADSI]("WinNT://Xu-Acer-PC/Administrators")
PS C:\Windows\system32> $objGroup.PSBase.Invoke("Add",$objUser.PSBase.Path)

# The above is just for one computer, how many hundrends of computers for the user? Need the automation codes!!!
####################?????????????????????????????###################################

################ How to Start SQL Agent Jobs in PS #################

# Step 1 - Need to start SQL Server Agent,then run the script below to see the jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> gci

# Step 2 - If you need to run all of the jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> gci | %{$_.start()}


# Step 3- If you need to run a particular job. You need the category ID

PS SQLSERVER:\sql\localhost\default\jobserver\jobs> get-item syspolicy_purge_history.

# Step 4 - If you need to find all of the categories IDs for all Jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> gci | ft name, categoryID, -autosize

# Step 5 - A couple of ways to run a particualr job
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> (get-item syspolicy_purge_history.0).start()

PS SQLSERVER:\sql\localhost\default\jobserver\jobs> $a=get-item syspolicy_purge_history.0
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> $a.start()

# Step 6 - You can also run a particular job step in a job
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> $a.start("theNameOfAName")

# Step 7 - You can run jobs in a remote server. In the script below, Xu-Acer-PC pretends to a remote server, although it is the same as localhost in reality.
# The rest is the same as the localhost.
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> cd\
PS SQLSERVER:\> cd sql\xu-acer-pc\default\jobserver\jobs
PS SQLSERVER:\sql\xu-acer-pc\default\jobserver\jobs> gci

# Step 8 - how about to call the same job across multiple servers?
# First, you need to find the categoryID for the job using T-SQL or PS. Let's assume we get 100 for the CategoryID for the common job called ETLLoads
# T-SQL: Use MSDB GO Select * from syscategories
# PS way - see above
# Let's try this way.Assuming we have two servers: localhost xu-acer-pc
PS SQLSERVER:\sql\localhost\default\jobserver\jobs> $a = "localhost", "xu-acer-pc"
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>$a | % {cd \; cd sql\S_\default\jobserver\jobs; "Starting: $_"; (get-item ETLLoads.100).start(); "Done: $_";}

# The script above is likely to generate an error if the job on the 2nd server has a different categoryID. How can we change the categoryID dynamically?
# Step 9 - Workaround 1.  create a ps1 file for each server manually.


 cd \;
 cd sql\localhost\default\jobserver\jobs; #hard-code the server name for each server
 "Starting: $_";
 (get-item ETLLoads.100).start();  #hard-code the categoryID for the job on each server
 "Done: $_";


 cd \;
 cd sql\server2\default\jobserver\jobs;
 "Starting: $_";
 (get-item ETLLoads.101).start();
 "Done: $_";

# Step 9 - Workaround 2.  Use sp_start_job in msdb and the $a variable

PS SQLSERVER:\sql\localhost\default\jobserver\jobs> $a = "localhost", "xu-acer-pc"
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>$a | % {cd \; cd sql\$_\default\databases\msdb; "Starting: $_"; invoke-sqlcmd "sp_start_job 'ETLLoads'"; "Done: $_";}


################ How to Manage DB Files (a large number of files) in a Database #################

# Step 1 - In our example, we have only one filegroup and file. We need to assume multiple FGs and multiple Files to see the advantage
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\Filegrojups\Primary\files> dir | gm 
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\Filegrojups\Primary\files> gci | ft name,growth, growthtype -autosize

# Step 2 - now let's change the growth to None
# Option 1: Using GrowthType property
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\filegroups\primary\files> dir | %{$_.GrowthType="None"; $_.Alter()}

# Option 2: Using Set_GrowthType() method. This method is not displayed with gm, but displayed with "gm -force" since PS 2.0

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\filegroups\primary\files> dir | %{$_.Set_GrowthType("None"); $_.Alter()}

################ How to Get All of the Table Statistics in a Database #################

# Step 1 - Do we have a get_statistics method? Yes,we do!
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\tables> gci | gm -force

# Step 2 - Using the methodto get all of the statistic for all tables in a database and output them to a file, pickup the attributes or column names you need

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\tables> dir | %{$a=$_.get_Statistics(); $a | format-table @{Label="Table"; Expression={$_.Parent}}, name, LastUpdated, IsAutoCreated -auto |Out-File c:\TableStats.txt -append; "$a is finished!!!"}

###################### Watching Audit Service Accounts on multiple servers with Powershell#####################
# Step 1 - get the informatio you need, display them on the console
PS C:\> gwmi win32_service |?{$_.DisplayName -match "SQL"} | ft SystemName, DisplayName, StartMode,State,StartName -auto

# Step 2 - get all of the required information into a SQL Server table
# First, we need to create a SQL Server table in SSMS
<#
USE [MyTestDB]
GO

CREATE TABLE [dbo].[ServiceInfo](
       [ServerName] [varchar](200) NULL,
       [DisplayName] [varchar](200) NULL,
       [StartMode] [varchar](20) NULL,
       [StartName] [varchar](200) NULL
)
#>

# Then we use the following script to populate the data into the table. It's better to use ISE to take advantage of the intellisense (for ISE 3.0 and 4.0)
$a= "Localhost","Xu-Acer-PC"
#$a=get-content C:\Servers.txt #Another way if you like to keep the servers in a text file

$a | % {`
        $ServerName = $_;
        gwmi win32_service -ComputerName $ServerName |?{$_.DisplayName -match "SQL"} | % { `
                                                                                          $DisplayName=$_.DisplayName;
                                                                                          $StartMode=$_.StartMode;
                                                                                          $StartName=$_.StartName;
                                                                                          Invoke-Sqlcmd -ServerInstance $ServerName -Database "MyTestDB" -Query "Insert ServiceInfo Select'$ServerName', '$DisplayName','$StartMode','$StartName'"
                                                                                         }
        }


###################### Watching Audit Backups on multiple servers with Powershell#####################
# Step 1 - use the following script, saving it as auditbackups.ps1 in C:\Users\XU-Acer\Documents>
$a= "Localhost","Xu-Acer-PC\I2"
$a | % `
     {
        $ServerName = $_;
        cd sqlserver:\

        # I have two instances on a single box, so the path is different
        If ($ServerName -eq "localhost" )
        {
            cd sql\$ServerName\default\databases;
                          
         
          <##### If you really want to use Powershell instead of SQL Server to create a table, see below:
            $ns = 'Microsoft.SqlServer.Management.Smo'
            $svr = new-object ("$ns.Server") $inst
            #Create reusable datatype objects
            $dtint = [Microsoft.SqlServer.Management.Smo.Datatype]::Int
            $dtvchar100 = [Microsoft.SqlServer.Management.Smo.Datatype]::NVarChar(100)
            $dtdatetm = [Microsoft.SqlServer.Management.Smo.Datatype]::DateTime

            #Reference your database.
            $db = $svr.Databases["MyTestDB"]

            #Create the table in the dbo schema
            $tbcomp = new-object ("$ns.Table") ($db, "BackupInfo2", "dbo")

            #Create the ServerName column
            $colconame = new-object ("$ns.Column") ($tbcomp, "ServerName", $dtvchar100)
            $colconame.Nullable = $false
            $tbcomp.Columns.Add($colconame)
       
            #Create the LastFullDate column
            $colconame = new-object ("$ns.Column") ($tbcomp, "LastFullDate", $dtvchar100)
            $colconame.Nullable = $false
            $tbcomp.Columns.Add($colconame)

            #Create the LastDiffDate column
            $colconame = new-object ("$ns.Column") ($tbcomp, "LastDiffDate", $dtvchar100)
            $colconame.Nullable = $false
            $tbcomp.Columns.Add($colconame)

            #Create the LastLogDate column
            $colconame = new-object ("$ns.Column") ($tbcomp, "LastLogDate", $dtvchar100)
            $colconame.Nullable = $false
            $tbcomp.Columns.Add($colconame) 
      
            #Create the ExecutionTime column
            $colconame = new-object ("$ns.Column") ($tbcomp, "ExecutionTime", $dtdatetm)
            $colconame.Nullable = $false
            $tbcomp.Columns.Add($colconame)            


            #Create the table
            $tbtcomp.Create();
           
            Done with the table creation ####################>
        }
        Else
        {
            cd sql\$ServerName\databases;
       
        }
                                     
    
        $DBs=Dir;
        $DBs | `
                 % {
                     $LastBackupDate=$_.LastBackupDate;
                     $LastDifferentialDate=$_.LastDifferentialBackupDate;
                     $LastLogDate=$_.LastLogBackupDate;
                     $ExecutionTime= Get-Date;
                     Invoke-Sqlcmd -ServerInstance localhost -Database "MyTestDB" -Query "Insert BackupInfo Select '$ServerName','$LastBackupDate', '$LastDifferentialDate','$LastLogDate','$ExecutionTime'" -suppressprovidercontextwarning
                     "Processing Server: "+ $ServerName + " Database: " +$_.Name + " === Done!!!"
                    
                     #If you use PS for the table creation, use the code below:
                     #Invoke-Sqlcmd -ServerInstance localhost -Database "MyTestDB" -Query "Insert BackupInfo2 Select '$ServerName','$LastBackupDate', '$LastDifferentialDate','$LastLogDate','$ExecutionTime'" -suppressprovidercontextwarning
                   
                    }
                    cd C:\Users\XU-Acer\Documents
     }

# Step 2 - execute the from the script file location:
PS C:\Users\XU-Acer\Documents> .\auditbackups.ps1

###################### Replace sp_MSForEachDB with Powershell #####################
# Step 1 - Basics, using the -query parameter
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases>  dir | %{$DBName=$_.Name;invoke-sqlcmd -database $DBName -query "create schema NoMoreForEachDB";$DBName}
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases>  dir | %{$DBName=$_.Name;invoke-sqlcmd -database $DBName -query "drop schema NoMoreForEachDB";$DBName}

# Step 2- Use an input file, using the inputfile parameter (put the normalT-SQL codes into a file)
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases>  dir | %{$DBName=$_.Name;invoke-sqlcmd -database $DBName -InputFile "C:\users\Xu-Acer\documents\createtable.sql";$DBName}

# step 3 - more easily extensible than sp_MSForEachDB based on your filtering requirementsh


###################### Sum All DB Sizes with Powershell #####################
# Option 1 - be aware of the $TotalSize is not reset after each execution.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases> dir | %{$TotalSize +=$_.Size}
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases>$TotalSize

# Option 2 - create a function as below to initialize the $TotalSize:

function TestReset
{
$TotalSize=0
cd SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases
dir | %{$TotalSize +=$_.Size}
$TotalSize
}

# save it in C:\users\XU-Acer\Documents as Test.ps1
# run it as below:
PS C:\users\XU-Acer\Documents> . .\Test.ps1
# Then run the function as below. It always shows the same number.
PS C:\users\XU-Acer\Documents> testreset
# 927.8125

# Option 3 - you can manipulate $a
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases> $a=dir | measure -prop Size -sum
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases> $a.sum/1kb # make the default MB to GB

# Others - If you want to filter database
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases> $a=(dir | ? {$_.name-eq "AdventureWorks2014"} | measure -prop size -sum)

###################### Check and Change SQL Server Settins #####################
# Step 1 - Check the server settings. Be aware Of the location
PS SQLSERVER:\sql\xu-acer-pc> $a=dir
PS SQLSERVER:\sql\xu-acer-pc> $a.edition
# Enterprise Evaluation Edition (64-bit)
# Developer Edition (64-bit)

#Step 2 - for the next level of settings
PS SQLSERVER:\sql\xu-acer-pc> $a.settings
<#
AuditLevel            : Failure
BackupDirectory       : C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup
DefaultFile           : C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
DefaultLog            : C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
LoginMode             : Integrated
MailProfile           :
NumberOfLogFiles      : -1
PerfMonMode           : None
TapeLoadWaitTime      : -1
Parent                : [XU-ACER-PC]
OleDbProviderSettings : {ADsDSOObject, Microsoft.ACE.OLEDB.12.0, Microsoft.ACE.OLEDB.15.0, MSDAOSP...}
Urn                   : Server[@Name='XU-ACER-PC']/Setting
Properties            : {Name=AuditLevel/Type=Microsoft.SqlServer.Management.Smo.AuditLevel/Writable=True/Value=Failure,
                        Name=BackupDirectory/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup,
                        Name=DefaultFile/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\,
                        Name=DefaultLog/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\...}
UserData              :
State                 : Existing

AuditLevel            : Success
BackupDirectory       : C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\Backup
DefaultFile           : C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\DATA\
DefaultLog            : C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\DATA\
LoginMode             : Integrated
MailProfile           :
NumberOfLogFiles      : -1
PerfMonMode           : None
TapeLoadWaitTime      : -1
Parent                : [XU-ACER-PC\I2]
OleDbProviderSettings : {ADsDSOObject, Microsoft.ACE.OLEDB.12.0, Microsoft.ACE.OLEDB.15.0, MSDAOSP...}
Urn                   : Server[@Name='XU-ACER-PC\I2']/Setting
Properties            : {Name=AuditLevel/Type=Microsoft.SqlServer.Management.Smo.AuditLevel/Writable=True/Value=Success,
                        Name=BackupDirectory/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\Backup,
                        Name=DefaultFile/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\DATA\,
                        Name=DefaultLog/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL12.I2\MSSQL\DATA\...}
UserData              :
State                 : Existing
#>

#Step 3 - I want to change the AuditLevel for Instance I2 back to Failure
PS SQLSERVER:\sql\xu-acer-pc> $a=dir |? {$_.Instancename-eq "I2"} # select the insance, you cannot set setting property for multiple instances at one time
PS SQLSERVER:\sql\xu-acer-pc> $a.settings.AuditLevel="Failure" # set it to a new value
PS SQLSERVER:\sql\xu-acer-pc> $a.settings # make sure it has been changed


###################### Change Agent Job Properties in Powershell (e.g.,retry attempt for a job step #####################
# Step 1 - you may need to load the assembly if you do not use SQLPS
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> [reflection.assembly]::loadwithpartialname("microsoft.sqlserver.smo")

#Step 2 - create a job step object
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js=new-object microsoft.sqlserver.management.smo.agent.jobstep

#Step 3 - see all of the job steps for all jobs
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js=dir | %{$_.EnumJobStepsByID()} | ft parent,id,name -auto
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js

#Step 4 - change the job step properties such as retry attempts
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js | %{$_.retryAttempts=5;$_.Alter()}

#Step 5 - What if you just want to change the property for some jobs(e.g., jobs begin with P)?
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js=dir |? {$_.Name -like "P*"}| %{$_.EnumJobStepsByID()} | ft parent,id,name -auto #note 1: The filter part is ? {$_. }, not ? %{$_.}; note 2: filtering on the job name,not on job step name
PS SQLSERVER:\sql\XU-ACER-PC\DEFAULT\JobServer\Jobs\> $js


###################### Cycle Error Log on all Servers (e.g.,hundreds) using Powershell #####################
# Ideas - SQL Server does the job. PowerShell is used as the carrier for multiple servers.
# Create a job on one instance, script it, and then save the T-SQL script in a file (txt or whatever as long as the content can be read)
# In Powershell, execute the following script:

# If you want the intellisense, you may need to import the module
#import-module sqlps -disableNameChecking
 
# Option 1- for a few servers
# $a="Localhost","LocalHost\I2"

# Option 2 - retrieve the server name from a txt file
$a=get-content "C:\users\xu-acer\documents\Servers.txt"

# Option 3 - retrieve the server name from a SQL Server table
# invoke-sqlcmd -ServerInstance "localhost" -database "MyDB" -query "Select ServerName from dbo.Servers"

$a | % {
    $Server=$_;
    $Server
    invoke-sqlcmd -ServerInstance $Server -database "msdb" -InputFile "C:\users\xu-Acer\documents\PSErrorLog.txt"
  }

###################### Read Error Log on all Servers (e.g.,hundreds) using Powershell #####################

# Option 1 - read error logs for all instances on a server
PS SQLSERVER:\sql\xu-acer-pc> $a=dir
PS SQLSERVER:\sql\xu-acer-pc> $a.readErrorLog()
PS SQLSERVER:\sql\xu-acer-pc> $a.readErrorLog()|Out-file "C:\users\xu-acer\documents\errorlogs.cvs"


# Option 2 - read error logs for all instances on a server

PS SQLSERVER:\sql\xu-acer-pc> $a= dir sqlserver:\sql\Xu-acer-pc
PS SQLSERVER:\sql\xu-acer-pc> $a
<#
Instance Name
-------------
DEFAULT
I2
#>

PS SQLSERVER:\sql\xu-acer-pc> $a | %{$_.ReadErrorLog()} ############## ReadErrorLog ( ) without a parameter read all current logs
PS SQLSERVER:\sql\xu-acer-pc> $a | %{$_.InstanceName; $_.ReadErrorLog()} #output the instance name


# Option 3 - read error logs for the selected instances on a server
PS SQLSERVER:\sql\xu-acer-pc> $a=dir |? {$_.InstanceName -eq "I2" }
PS SQLSERVER:\sql\xu-acer-pc> $a.readErrorLog()|Out-file "C:\users\xu-acer\documents\errorlogs_I2.csv"

# Option 4 - what if you want to read all error logs including the current and archived ones for all instances on a server

$a=dir sqlserver:\sql\localhost

$a | % {
        $_.InstanceName;
        $Logs=$_.EnumErrorLogs();
        ForEach ($log in $Logs)
            {
                write-host ("=====The name for the cuurent log, by default, is 0 =============================")
                $_.name
                $log.name
                write-host ("=================================================================================")
            
                $_.ReadErrorLog($log.Name) # note ReadErrorLog has an overload method with one parameter
            }

        }
###################### Change SQL Server Permissions in Powershell #####################

# Requirement: removing permissions from individual SPs and put the permission at the schema level.
# I am in a sysadmin role. There is a login called PSTestand a db user in Adventureworks 2014, also called PSTest.
# Option 1

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$SP=$_.Schema+"."+$_.Name; $SP; Invoke-sqlcmd -query "revoke execute on $sp
 PSTest" -SuppressProviderContextWarning}

# Option 2 - the SMO approach. Let's check the revoke method in SMO first
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | gm |? {$_.name -eq "revoke"} | fl
# One of the overloaded method for revoke is void Revoke(Microsoft.SqlServer.Management.Smo.ObjectPermissionSet permission, string[] granteeNames)

PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a=new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a


Alter              : False
Control            : False
Connect            : False
Delete             : False
Execute            : False
Impersonate        : False
Insert             : False
Receive            : False
References         : False
Select             : False
Send               : False
TakeOwnership      : False
Update             : False
ViewDefinition     : False
ViewChangeTracking : False
CreateSequence     : False

# Change the property for Execute
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> $a.Execute=1

# Grand the permission for all SPs
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$_.name;$_.grant($a,"PSTest");$_.Alter()}

# Revoke the permission for all SPs
PS SQLSERVER:\sql\xu-acer-pc\default\databases\adventureworks2014\storedprocedures> dir | %{$_.name;$_.revoke($a,"PSTest");$_.Alter()}

# How to trap the meaningful error message (The internal error message from SQL Server. Execute the script in PowerShellISE

cd SQLServer:\sql\localhost\default\databases\Adventureworks2014\StoredProcedures
$a=new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionSet
$a.Execute=1
#$a.Execute=0 # For testing the detailed messsage
#$a.Insert=1  # Fortesting the detailed messsage - Insert is not applicable to a sp

$acct = "PSTest"
dir | % {$_.Revoke($a,$acct) ###########$_.Grant($a,$acct)
         $_.Name;
         $_.Alter();

         Trap {
                $err=$_.Exception
                $err.message

                while ($err.InnerException)
                {
                    $err=$err.InnerException
                    write-output $err.message
                }
                Continue

               }

        }
cd c:\


# Or save the script as TrapTheError.ps1 and execute the file from the right location
PS C:\users\xu-acer\documents> ./TrapTheError.ps1

###################### Change Schema in Powershell #####################
# Change all tables whose name is T100 to 199 with a new schema name. The $ at the end of T1[0-9][0-9]$ is to eliminate T1[0-9][0-9][0-9]...
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases\MyTestDB\Tables> dir |? {$_.Name -match "T1[0-9][0-9]$"} | %{$_.Name; $_.ChangeSchema("MyNewSchema");$_.Alter()}

###################### Calling Detach Methods in Powershell #####################

# Let's see an error message first
PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir
PS SQLSERVER:\SQL\XU-ACER-PC> $a.DetachDatabase("MyTestDB",$False) #I try to detach MyTestDB and get the error below.
<#
Exception calling "DetachDatabase" with "2" argument(s): "Detach database failed for Server 'XU-ACER-PC'. "
At line:1 char:1
+ $a.DetachDatabase("MyTestDB",$False)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException
#>

# Horrible error message, no ideas at all. The I try to execute the script below in PS ISE
$a=dir
$a.DetachDatabase("MyTestDB",$False)
         Trap {
                $err=$_.Exception
                $err.message

                while ($err.InnerException)
                {
                    $err=$err.InnerException
                    write-output $err.message
                }
                Continue

               }

<# Now the error message is: Detach database failed for Server 'XU-ACER-PC'.
An exception occurred while executing a Transact-SQL statement or batch.
Cannot detach the database 'MyTestDB' because it is currently in use.
#>

# Now I create a TestDB with 3 data files and two log files. The 2nd and 3rd data files are in the FG2 file group.
# Also note I have two instances on the server, the TestDB is on the default instance.
PS SQLSERVER:\SQL\XU-ACER-PC> dir
<#
Instance Name
-------------
DEFAULT
I2

# Select the right instance first
PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir | ? {$_.INSTANCEName -eq "" } ### Note: For the default instance, the instance name is empty, not DEFAULT
PS SQLSERVER:\SQL\XU-ACER-PC> $a.DetachDatabase("TestDB",$False)

#How many DB files for the DB? You can find them out by using the EnumDetachedDatabaseFiles method, you need to know the master mdf file name and location

PS SQLSERVER:\sql\localhost> dir

Instance Name
-------------
DEFAULT
I2


PS SQLSERVER:\sql\localhost> $a=dir | ? {$_.INSTANCEName -eq "" }
PS SQLSERVER:\sql\localhost> $a

Instance Name
-------------
DEFAULT


PS SQLSERVER:\sql\localhost> $a.EnumDetachedDatabaseFiles("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf")
# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf
# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB2.ndf
# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB3.ndf

#How many log files for the DB? You can find them out by using the EnumDetachedLogFiles method, you also need to know the master mdf file name and location

PS SQLSERVER:\sql\localhost> $a.EnumDetachedLogFiles("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB.mdf")

# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf
# C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestDB_log2.ldf


###################### Get IP and DNS in Powershell #####################
#Step 1 - Make sure PING works in PS. For a single server, you can find the IP address as below:
#By the way,if your PING shows IP6 Addreess, you can prefer IPv4 over IPv6 in prefix policies. See https://support.microsoft.com/kb/929852?wa=wsignin1.0 for details.
PS SQLSERVER:\> ping xu-acer-pc
<#
Pinging XU-Acer-PC [192.168.0.39] with 32 bytes of data:
Reply from 192.168.0.39: bytes=32 time<1ms TTL=128
Reply from 192.168.0.39: bytes=32 time<1ms TTL=128
Reply from 192.168.0.39: bytes=32 time<1ms TTL=128
Reply from 192.168.0.39: bytes=32 time<1ms TTL=128

Ping statistics for 192.168.0.39:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms
#>
# Or you can use a variable.
PS SQLSERVER:\> $server="Xu-Acer-PC"
PS SQLSERVER:\> ping $Server

# Step 2 - It's hard to retrieve the IP Address from the above result. But you can use the test-connection command as below:
PS SQLSERVER:\> $a=(test-connection "Xu-Acer-PC" -count 1).IPV4Address
PS SQLSERVER:\> $a.IPAddressToString
192.168.0.39

# Step 3 - How to find the server's DNS
PS SQLSERVER:\> $DNS=([System.Net.DNS]::GetHostEntry("Xu-Acer-PC")).HostName
PS SQLSERVER:\> $DNS
#XU-Acer-PC

# Step 4 - How to get the IP Address from the DNS(a convoluted way)

PS SQLSERVER:\> $DNS=([System.Net.DNS]::GetHostEntry("Xu-Acer-PC")).HostName
PS SQLSERVER:\> $DNS
# XU-Acer-PC
PS SQLSERVER:\> $DNS=([System.Net.DNS]::GetHostEntry("Xu-Acer-PC")).AddressList
PS SQLSERVER:\> $DNS

<#
Address            : 654354624
AddressFamily      : InterNetwork
ScopeId            :
IsIPv6Multicast    : False
IsIPv6LinkLocal    : False
IsIPv6SiteLocal    : False
IsIPv6Teredo       : False
IsIPv4MappedToIPv6 : False
IPAddressToString  : 192.168.0.39

Address            :
AddressFamily      : InterNetworkV6
ScopeId            : 0
IsIPv6Multicast    : False
IsIPv6LinkLocal    : False
IsIPv6SiteLocal    : False
IsIPv6Teredo       : False
IsIPv4MappedToIPv6 : False
IPAddressToString  : ::1
#>

# returns an array

PS SQLSERVER:\> $a=$DNS.IPAddressToString ##### you may need to use $DNS | %{$_.IPAddressToString} in earlier version of PowerShell#####
PS SQLSERVER:\> $a
192.168.0.39
::1
PS SQLSERVER:\> $a.getType()
<#
IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Object[]                                 System.Array
#>

# retrieve the IPV4 addressonly

PS SQLSERVER:\> $a[0]
192.168.0.39

###################### Attach a Database in Powershell #####################
# Enter the script below in PowerShell ISE, or save it in a ps file and execute it from the right location

# point to the right location
CD SQLSERVER:\SQL\Localhost

# I have two instances.Need to limit it to the default instance
$a=dir | ? {$_.INSTANCEName -eq "" }

# create a variable for filelist
$sc=new-object System.Collections.Specialized.StringCollection
$sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB.mdf")
# You do not need the following files uncless their locations have changed
#sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB2.ndf")
#sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB3.ndf")
#sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB_log.ldf")
#sc.Add("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\TestAttachDB_log2.ldf")

<# The next step is required due to a bug in provider.
   For the 1st time attachment, it is not required. But after the 1st successful attachment, you will get the following error message even though the database is detached:
   =====================================================================
   Attach database failed for Server 'XU-ACER-PC'.
   Cannot attach a database with the same name as an existing database.
   =====================================================================
#>
$a.databases.refresh()

###Overload 1
$a.AttachDatabase("TestAttachDB",$sc) #### DB Name, FileList

###Overload 2
# $a.AttachDatabase("TestDetachDB",$sc, [Microsoft.SqlServer.Management.Smo.AttachOptions]::None) #### DB Name, FileList, Attach Options

###Overload 3
# $a.AttachDatabase("TestDetachDB",$sc, "sa") #### DB Name, FileList, DBOwner

###Overload 4
# $a.AttachDatabase("TestDetachDB",$sc,"sa", [Microsoft.SqlServer.Management.Smo.AttachOptions]::None) #### DB Name, FileList,DBOwner, Attach Options

Trap {
                $err=$_.Exception
                $err.message

                while ($err.InnerException)
                {
                    $err=$err.InnerException
                    write-output $err.message
                }
                Continue

       }

#$a.DetachDatabase("TestDetachDB",$False)


###################### Fix AutoShrink in Powershell #####################
# Enter the script below in PowerShell ISE, or save it in a ps file and execute it from the right location


$a=("localhost","Xu-Acer-PC\I2")

$a | % {
        If ($_ -eq "localhost" )
        {
       
            $Server="Localhost\Default"
            "`r`nProcessing Server: $Server`r`n"
        }
        else
        {
            $Server=$_
            "`r`nProcessing Server: $Server`r`n"
        }
      
        CD sqlserver:\sql\$Server\Databases;
       
        $b=dir;
       
        $b | % {
                $_.Name
                $_.AutoShrink=0;
                $_.Alter()
        }
       
    Trap {
            $err=$_.Exception
            $err.message

            while ($err.InnerException)
            {             
                $err=$err.InnerException
                write-output $err.message
            }
            Continue

       }
  
}

###################### Get SQL Server Root Dir in Powershell #####################
# Enter the script below in PowerShell ISE, or save it in a ps file and execute it from the right location


$a=("localhost","Xu-Acer-PC")

$a | % {
          <# Option 1:

            $Server=$_  
            CD sqlserver:\sql\$Server;

            "`r`nThe root directory for $Server is: `r `n"
     
            $b=dir;
            $b.RootDirectory
     
         
         }#>
           

            # Option 2
    
                $Server=$_  
                CD sqlserver:\sql\$Server;
 
                $b=dir;
  
                "$Server : $($b.RootDirectory)"
            #>
         }
   
       
     Trap {
            $err=$_.Exception
            $err.message

            while ($err.InnerException)
            {             
                $err=$err.InnerException
                write-output $err.message
            }
            Continue

       }
  

###################### Drop Tables in Powershell #####################
# drop the tables in the MyTestDB database beginning with T
PS SQLSERVER:\SQL\Localhost\default\databases\mytestdb\tables> dir | ? {$_.Name -match "^T"} | %{$_.Name; $_.drop()}

# drop the tables in the MyTestDB database with 11 or more rows
PS SQLSERVER:\SQL\Localhost\default\databases\mytestdb\tables>  dir | ? {$_.rowcount -gt 10} | %{$_.Name; $_.drop()}

###################### Server Configuration Settings in Powershell like sp_configure in SQL Server #####################
# go to the server level
PS SQLSERVER:\SQL\XU-ACER-PC> dir
<#
Instance Name
-------------
DEFAULT
I2
#>
# Limit to the default instance

PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir | ? {$_.InstanceName -eq ""}
PS SQLSERVER:\SQL\XU-ACER-PC> $a
<#
Instance Name
-------------
DEFAULT
#>

# Find all of members at the server level

PS SQLSERVER:\SQL\XU-ACER-PC> $a | gm

# We are interested in "configuration  Property     Microsoft.SqlServer.Management.Smo.Configuration Configuration {get;}"
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration

# We are further interested in the next level of properties such as ShowAdvancedOptions or XPCmdShellEnabled
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration | GM
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.properties | ft DisplayName, ConfigValue,RunValue, IsDynamic -auto

# We can change these properties
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.ShowAdvancedOptions.ConfigValue=1
PS SQLSERVER:\SQL\XU-ACER-PC> $a.Alter()
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.ShowAdvancedOptions | ft DisplayName, ConfigValue,RunValue, IsDynamic -auto
<#
DisplayName           ConfigValue RunValue IsDynamic
-----------           ----------- -------- ---------
show advanced options           1        0      True
#>

# But we will get an error for the one below
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.ShowAdvancedOptions.xp_cmdshell=1
<#
The property 'xp_cmdshell' cannot be found on this object. Verify that the property exists and can be set.
At line:1 char:1
+ $a.configuration.ShowAdvancedOptions.xp_cmdshell=1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException
#>

# The error above is because xp_cmdshell is the display name, not the name. We need to find the name for the property first. It is called XPCmdShellEnabled.
# In the ShowAdvancedOption example, the name and display name happen to the same.
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.XPCmdShellEnabled.ConfigValue=1
PS SQLSERVER:\SQL\XU-ACER-PC> $a.Alter()
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.xpcmdshellEnabled | ft DisplayName, ConfigValue,RunValue, IsDynamic -auto
<#
DisplayName ConfigValue RunValue IsDynamic
----------- ----------- -------- ---------
xp_cmdshell           1        1      True
#>

PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.XPCmdShellEnabled.ConfigValue=0
PS SQLSERVER:\SQL\XU-ACER-PC> $a.configuration.xpcmdshellEnabled | ft DisplayName, ConfigValue,RunValue, IsDynamic -auto
<#
DisplayName ConfigValue RunValue IsDynamic
----------- ----------- -------- ---------
xp_cmdshell           0        1      True
#>

###################### Truncate Tables in Powershell #####################
# Let's say we want to truncate all tables in the dbo schema
# Step 1 - find them
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases\MyTestDB\Tables> dir | ? {$_.Schema -eq "dbo"}

# Step 2- use the TruncateData method
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\Databases\MyTestDB\Tables> dir | ? {$_.Schema -eq "dbo"} | %{$_.TruncateData()}

###################### Change Startup Params in Powershell #####################
# Step 1 - create an object instance at the server level
PS SQLSERVER:\SQL\XU-ACER-PC> $a=new-object("Microsoft.sqlserver.management.smo.wmi.managedcomputer")
PS SQLSERVER:\SQL\XU-ACER-PC> $a

# Step 2 - Find all of the SQL Server Services on the server
PS SQLSERVER:\SQL\XU-ACER-PC> $a.services

# Step 3 - Find the sql server service
PS SQLSERVER:\SQL\XU-ACER-PC> $b=$a.services| ? {$_.Name -eq "mssqlserver"}
PS SQLSERVER:\SQL\XU-ACER-PC> $b

# Step 4 - check all startup parameters
PS SQLSERVER:\SQL\XU-ACER-PC> $b.StartupParameters
<#
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC
:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
#>

# Step 5 - change the startup parameters
PS SQLSERVER:\SQL\XU-ACER-PC> $b.StartupParameters ="-dC:\YourNewPath\master.mdf; -eC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC
:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
PS SQLSERVER:\SQL\XU-ACER-PC> $b.Alter() # do not forget this.

###################### Check Object Properties in Powershell #####################

#Step 1 - go to the right location to see the available propeties

PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir
PS SQLSERVER:\SQL\XU-ACER-PC> $a

<#
Instance Name
-------------
DEFAULT
I2
#>

PS SQLSERVER:\SQL\XU-ACER-PC> $a | gm
<#
   TypeName: Microsoft.SqlServer.Management.Smo.Server

Name                               MemberType   Definition
----                               ----------   ----------
PropertyChanged                    Event        System.ComponentModel.PropertyChangedEventHandler PropertyChanged(System.Object, System.ComponentModel.PropertyCh...
PropertyMetadataChanged            Event        System.EventHandler`1[Microsoft.SqlServer.Management.Sdk.Sfc.SfcPropertyMetadataChangedEventArgs] PropertyMetadat...
Alter                              Method       void Alter(), void Alter(bool overrideValueChecking), void IAlterable.Alter()
AttachDatabase                     Method       void AttachDatabase(string name, System.Collections.Specialized.StringCollection files, string owner), void Attac...
CompareUrn                         Method       int CompareUrn(Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn1, Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn2)
DeleteBackupHistory                Method       void DeleteBackupHistory(datetime oldestDate), void DeleteBackupHistory(int mediaSetId), void DeleteBackupHistory...
Deny                               Method       void Deny(Microsoft.SqlServer.Management.Smo.ServerPermissionSet permission, string[] granteeNames), void Deny(Mi...
DesignModeInitialize               Method       void IAlienRoot.DesignModeInitialize()
DetachDatabase                     Method       void DetachDatabase(string databaseName, bool updateStatistics), void DetachDatabase(string databaseName, bool up...
DetachedDatabaseInfo               Method       System.Data.DataTable DetachedDatabaseInfo(string mdfName)
Discover                           Method       System.Collections.Generic.List[System.Object] Discover(), System.Collections.Generic.List[System.Object] IAlienO...
EnumActiveCurrentSessionTraceFlags Method       System.Data.DataTable EnumActiveCurrentSessionTraceFlags()
EnumActiveGlobalTraceFlags         Method       System.Data.DataTable EnumActiveGlobalTraceFlags()
EnumAvailableMedia                 Method       System.Data.DataTable EnumAvailableMedia(), System.Data.DataTable EnumAvailableMedia(Microsoft.SqlServer.Manageme...
EnumClusterMembersState            Method       System.Data.DataTable EnumClusterMembersState()
EnumClusterSubnets                 Method       System.Data.DataTable EnumClusterSubnets()
EnumCollations                     Method       System.Data.DataTable EnumCollations()
EnumDatabaseMirrorWitnessRoles     Method       System.Data.DataTable EnumDatabaseMirrorWitnessRoles(), System.Data.DataTable EnumDatabaseMirrorWitnessRoles(stri...
EnumDetachedDatabaseFiles          Method       System.Collections.Specialized.StringCollection EnumDetachedDatabaseFiles(string mdfName)
EnumDetachedLogFiles               Method       System.Collections.Specialized.StringCollection EnumDetachedLogFiles(string mdfName)
EnumDirectories                    Method       System.Data.DataTable EnumDirectories(string path)
EnumErrorLogs                      Method       System.Data.DataTable EnumErrorLogs()
EnumLocks                          Method       System.Data.DataTable EnumLocks(), System.Data.DataTable EnumLocks(int processId)
EnumMembers                        Method       System.Collections.Specialized.StringCollection EnumMembers(Microsoft.SqlServer.Management.Smo.RoleTypes roleType)
EnumObjectPermissions              Method       Microsoft.SqlServer.Management.Smo.ObjectPermissionInfo[] EnumObjectPermissions(), Microsoft.SqlServer.Management...
EnumPerformanceCounters            Method       System.Data.DataTable EnumPerformanceCounters(), System.Data.DataTable EnumPerformanceCounters(string objectName)...
EnumProcesses                      Method       System.Data.DataTable EnumProcesses(), System.Data.DataTable EnumProcesses(int processId), System.Data.DataTable ...
EnumServerAttributes               Method       System.Data.DataTable EnumServerAttributes()
EnumServerPermissions              Method       Microsoft.SqlServer.Management.Smo.ServerPermissionInfo[] EnumServerPermissions(), Microsoft.SqlServer.Management...
EnumStartupProcedures              Method       System.Data.DataTable EnumStartupProcedures()
EnumWindowsDomainGroups            Method       System.Data.DataTable EnumWindowsDomainGroups(), System.Data.DataTable EnumWindowsDomainGroups(string domain)
EnumWindowsGroupInfo               Method       System.Data.DataTable EnumWindowsGroupInfo(), System.Data.DataTable EnumWindowsGroupInfo(string group), System.Da...
EnumWindowsUserInfo                Method       System.Data.DataTable EnumWindowsUserInfo(), System.Data.DataTable EnumWindowsUserInfo(string account), System.Da...
Equals                             Method       bool Equals(System.Object obj)
GetActiveDBConnectionCount         Method       int GetActiveDBConnectionCount(string dbName)
GetConnection                      Method       Microsoft.SqlServer.Management.Common.ISfcConnection ISfcHasConnection.GetConnection(), Microsoft.SqlServer.Manag...
GetDefaultInitFields               Method       System.Collections.Specialized.StringCollection GetDefaultInitFields(type typeObject)
GetDomainRoot                      Method       Microsoft.SqlServer.Management.Sdk.Sfc.ISfcDomainLite IAlienObject.GetDomainRoot()
GetHashCode                        Method       int GetHashCode()
GetLogicalVersion                  Method       int ISfcDomainLite.GetLogicalVersion()
GetParent                          Method       System.Object IAlienObject.GetParent()
GetPropertyNames                   Method       System.Collections.Specialized.StringCollection GetPropertyNames(type typeObject)
GetPropertySet                     Method       Microsoft.SqlServer.Management.Sdk.Sfc.ISfcPropertySet ISfcPropertyProvider.GetPropertySet()
GetPropertyType                    Method       type IAlienObject.GetPropertyType(string propertyName)
GetPropertyValue                   Method       System.Object IAlienObject.GetPropertyValue(string propertyName, type propertyType)
GetSmoObject                       Method       Microsoft.SqlServer.Management.Smo.SqlSmoObject GetSmoObject(Microsoft.SqlServer.Management.Sdk.Sfc.Urn urn)
GetStringComparer                  Method       System.Collections.IComparer GetStringComparer(string collationName)
GetType                            Method       type GetType()
GetUrn                             Method       Microsoft.SqlServer.Management.Sdk.Sfc.Urn IAlienObject.GetUrn()
Grant                              Method       void Grant(Microsoft.SqlServer.Management.Smo.ServerPermissionSet permission, string[] granteeNames), void Grant(...
Initialize                         Method       bool Initialize(), bool Initialize(bool allProperties)
IsDetachedPrimaryFile              Method       bool IsDetachedPrimaryFile(string mdfName)
IsWindowsGroupMember               Method       bool IsWindowsGroupMember(string windowsGroup, string windowsUser)
JoinAvailabilityGroup              Method       void JoinAvailabilityGroup(string availabilityGroupName)
KillAllProcesses                   Method       void KillAllProcesses(string databaseName)
KillDatabase                       Method       void KillDatabase(string database)
KillProcess                        Method       void KillProcess(int processId)
PingSqlServerVersion               Method       Microsoft.SqlServer.Management.Common.ServerVersion PingSqlServerVersion(string serverName, string login, string ...
ReadErrorLog                       Method       System.Data.DataTable ReadErrorLog(), System.Data.DataTable ReadErrorLog(int logNumber)
Refresh                            Method       void Refresh(), void IRefreshable.Refresh()
Resolve                            Method       System.Object IAlienObject.Resolve(string urnString)
Revoke                             Method       void Revoke(Microsoft.SqlServer.Management.Smo.ServerPermissionSet permission, string[] granteeNames), void Revok...
Script                             Method       System.Collections.Specialized.StringCollection Script(), System.Collections.Specialized.StringCollection Script(...
SetConnection                      Method       void ISfcHasConnection.SetConnection(Microsoft.SqlServer.Management.Common.ISfcConnection connection)
SetDefaultInitFields               Method       void SetDefaultInitFields(type typeObject, System.Collections.Specialized.StringCollection fields), void SetDefau...
SetObjectState                     Method       void IAlienObject.SetObjectState(Microsoft.SqlServer.Management.Sdk.Sfc.SfcObjectState state)
SetPropertyValue                   Method       void IAlienObject.SetPropertyValue(string propertyName, type propertyType, System.Object value)
SetTraceFlag                       Method       void SetTraceFlag(int number, bool isOn)
SfcHelper_GetDataTable             Method       System.Data.DataTable IAlienRoot.SfcHelper_GetDataTable(System.Object connection, string urn, string[] fields, Mi...
SfcHelper_GetSmoObject             Method       System.Object IAlienRoot.SfcHelper_GetSmoObject(string urn)
SfcHelper_GetSmoObjectQuery        Method       System.Collections.Generic.List[string] IAlienRoot.SfcHelper_GetSmoObjectQuery(string queryString, string[] field...
ToString                           Method       string ToString()
Validate                           Method       Microsoft.SqlServer.Management.Sdk.Sfc.ValidationState Validate(string methodName, Params System.Object[] argumen...
DisplayName                        NoteProperty System.String DisplayName=DEFAULT
PSChildName                        NoteProperty System.String PSChildName=DEFAULT
PSDrive                            NoteProperty System.Management.Automation.PSDriveInfo PSDrive=SQLSERVER
PSIsContainer                      NoteProperty System.Boolean PSIsContainer=True
PSParentPath                       NoteProperty System.String PSParentPath=Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQL\XU-ACER-PC
PSPath                             NoteProperty System.String PSPath=Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQL\XU-ACER-PC\DEFAULT
PSProvider                         NoteProperty System.Management.Automation.ProviderInfo PSProvider=Microsoft.SqlServer.Management.PSProvider\SqlServer
ActiveDirectory                    Property     Microsoft.SqlServer.Management.Smo.ServerActiveDirectory ActiveDirectory {get;}
AffinityInfo                       Property     Microsoft.SqlServer.Management.Smo.AffinityInfo AffinityInfo {get;}
AuditLevel                         Property     Microsoft.SqlServer.Management.Smo.AuditLevel AuditLevel {get;set;}
Audits                             Property     Microsoft.SqlServer.Management.Smo.AuditCollection Audits {get;}
AvailabilityGroups                 Property     Microsoft.SqlServer.Management.Smo.AvailabilityGroupCollection AvailabilityGroups {get;}
BackupDevices                      Property     Microsoft.SqlServer.Management.Smo.BackupDeviceCollection BackupDevices {get;}
BackupDirectory                    Property     string BackupDirectory {get;set;}
BrowserServiceAccount              Property     string BrowserServiceAccount {get;}
BrowserStartMode                   Property     Microsoft.SqlServer.Management.Smo.ServiceStartMode BrowserStartMode {get;}
BuildClrVersion                    Property     version BuildClrVersion {get;}
BuildClrVersionString              Property     string BuildClrVersionString {get;}
BuildNumber                        Property     int BuildNumber {get;}
ClusterName                        Property     string ClusterName {get;}
ClusterQuorumState                 Property     Microsoft.SqlServer.Management.Smo.ClusterQuorumState ClusterQuorumState {get;}
ClusterQuorumType                  Property     Microsoft.SqlServer.Management.Smo.ClusterQuorumType ClusterQuorumType {get;}
Collation                          Property     string Collation {get;}
CollationID                        Property     int CollationID {get;}
ComparisonStyle                    Property     int ComparisonStyle {get;}
ComputerNamePhysicalNetBIOS        Property     string ComputerNamePhysicalNetBIOS {get;}
Configuration                      Property     Microsoft.SqlServer.Management.Smo.Configuration Configuration {get;}
ConnectionContext                  Property     Microsoft.SqlServer.Management.Common.ServerConnection ConnectionContext {get;}
Credentials                        Property     Microsoft.SqlServer.Management.Smo.CredentialCollection Credentials {get;}
CryptographicProviders             Property     Microsoft.SqlServer.Management.Smo.CryptographicProviderCollection CryptographicProviders {get;}
Databases                          Property     Microsoft.SqlServer.Management.Smo.DatabaseCollection Databases {get;}
DefaultFile                        Property     string DefaultFile {get;set;}
DefaultLog                         Property     string DefaultLog {get;set;}
DefaultTextMode                    Property     bool DefaultTextMode {get;set;}
DomainInstanceName                 Property     string DomainInstanceName {get;}
DomainName                         Property     string DomainName {get;}
Edition                            Property     string Edition {get;}
Endpoints                          Property     Microsoft.SqlServer.Management.Smo.EndpointCollection Endpoints {get;}
EngineEdition                      Property     Microsoft.SqlServer.Management.Smo.Edition EngineEdition {get;}
ErrorLogPath                       Property     string ErrorLogPath {get;}
Events                             Property     Microsoft.SqlServer.Management.Smo.ServerEvents Events {get;}
FilestreamLevel                    Property     Microsoft.SqlServer.Management.Smo.FileStreamEffectiveLevel FilestreamLevel {get;}
FilestreamShareName                Property     string FilestreamShareName {get;}
FullTextService                    Property     Microsoft.SqlServer.Management.Smo.FullTextService FullTextService {get;}
HadrManagerStatus                  Property     Microsoft.SqlServer.Management.Smo.HadrManagerStatus HadrManagerStatus {get;}
Information                        Property     Microsoft.SqlServer.Management.Smo.Information Information {get;}
InstallDataDirectory               Property     string InstallDataDirectory {get;}
InstallSharedDirectory             Property     string InstallSharedDirectory {get;}
InstanceName                       Property     string InstanceName {get;}
IsCaseSensitive                    Property     bool IsCaseSensitive {get;}
IsClustered                        Property     bool IsClustered {get;}
IsDesignMode                       Property     bool IsDesignMode {get;}
IsFullTextInstalled                Property     bool IsFullTextInstalled {get;}
IsHadrEnabled                      Property     bool IsHadrEnabled {get;}
IsSingleUser                       Property     bool IsSingleUser {get;}
IsXTPSupported                     Property     bool IsXTPSupported {get;}
JobServer                          Property     Microsoft.SqlServer.Management.Smo.Agent.JobServer JobServer {get;}
Language                           Property     string Language {get;}
Languages                          Property     Microsoft.SqlServer.Management.Smo.LanguageCollection Languages {get;}
LinkedServers                      Property     Microsoft.SqlServer.Management.Smo.LinkedServerCollection LinkedServers {get;}
LoginMode                          Property     Microsoft.SqlServer.Management.Smo.ServerLoginMode LoginMode {get;set;}
Logins                             Property     Microsoft.SqlServer.Management.Smo.LoginCollection Logins {get;}
Mail                               Property     Microsoft.SqlServer.Management.Smo.Mail.SqlMail Mail {get;}
MailProfile                        Property     string MailProfile {get;set;}
MasterDBLogPath                    Property     string MasterDBLogPath {get;}
MasterDBPath                       Property     string MasterDBPath {get;}
MaxPrecision                       Property     byte MaxPrecision {get;}
Name                               Property     string Name {get;}
NamedPipesEnabled                  Property     bool NamedPipesEnabled {get;}
NetName                            Property     string NetName {get;}
NumberOfLogFiles                   Property     int NumberOfLogFiles {get;set;}
OleDbProviderSettings              Property     Microsoft.SqlServer.Management.Smo.OleDbProviderSettingsCollection OleDbProviderSettings {get;}
OSVersion                          Property     string OSVersion {get;}
PerfMonMode                        Property     Microsoft.SqlServer.Management.Smo.PerfMonMode PerfMonMode {get;set;}
PhysicalMemory                     Property     int PhysicalMemory {get;}
PhysicalMemoryUsageInKB            Property     long PhysicalMemoryUsageInKB {get;}
Platform                           Property     string Platform {get;}
Processors                         Property     int Processors {get;}
ProcessorUsage                     Property     int ProcessorUsage {get;}
Product                            Property     string Product {get;}
ProductLevel                       Property     string ProductLevel {get;}
Properties                         Property     Microsoft.SqlServer.Management.Smo.SqlPropertyCollection Properties {get;}
ProxyAccount                       Property     Microsoft.SqlServer.Management.Smo.ServerProxyAccount ProxyAccount {get;}
ResourceGovernor                   Property     Microsoft.SqlServer.Management.Smo.ResourceGovernor ResourceGovernor {get;}
ResourceLastUpdateDateTime         Property     datetime ResourceLastUpdateDateTime {get;}
ResourceVersion                    Property     version ResourceVersion {get;}
ResourceVersionString              Property     string ResourceVersionString {get;}
Roles                              Property     Microsoft.SqlServer.Management.Smo.ServerRoleCollection Roles {get;}
RootDirectory                      Property     string RootDirectory {get;}
ServerAuditSpecifications          Property     Microsoft.SqlServer.Management.Smo.ServerAuditSpecificationCollection ServerAuditSpecifications {get;}
ServerType                         Property     Microsoft.SqlServer.Management.Common.DatabaseEngineType ServerType {get;}
ServiceAccount                     Property     string ServiceAccount {get;}
ServiceInstanceId                  Property     string ServiceInstanceId {get;}
ServiceMasterKey                   Property     Microsoft.SqlServer.Management.Smo.ServiceMasterKey ServiceMasterKey {get;}
ServiceName                        Property     string ServiceName {get;}
ServiceStartMode                   Property     Microsoft.SqlServer.Management.Smo.ServiceStartMode ServiceStartMode {get;}
Settings                           Property     Microsoft.SqlServer.Management.Smo.Settings Settings {get;}
SmartAdmin                         Property     Microsoft.SqlServer.Management.Smo.SmartAdmin SmartAdmin {get;}
SqlCharSet                         Property     int16 SqlCharSet {get;}
SqlCharSetName                     Property     string SqlCharSetName {get;}
SqlDomainGroup                     Property     string SqlDomainGroup {get;}
SqlSortOrder                       Property     int16 SqlSortOrder {get;}
SqlSortOrderName                   Property     string SqlSortOrderName {get;}
State                              Property     Microsoft.SqlServer.Management.Smo.SqlSmoState State {get;}
Status                             Property     Microsoft.SqlServer.Management.Smo.ServerStatus Status {get;}
SystemDataTypes                    Property     Microsoft.SqlServer.Management.Smo.SystemDataTypeCollection SystemDataTypes {get;}
SystemMessages                     Property     Microsoft.SqlServer.Management.Smo.SystemMessageCollection SystemMessages {get;}
TapeLoadWaitTime                   Property     int TapeLoadWaitTime {get;set;}
TcpEnabled                         Property     bool TcpEnabled {get;}
Triggers                           Property     Microsoft.SqlServer.Management.Smo.ServerDdlTriggerCollection Triggers {get;}
Urn                                Property     Microsoft.SqlServer.Management.Sdk.Sfc.Urn Urn {get;}
UserData                           Property     System.Object UserData {get;set;}
UserDefinedMessages                Property     Microsoft.SqlServer.Management.Smo.UserDefinedMessageCollection UserDefinedMessages {get;}
UserOptions                        Property     Microsoft.SqlServer.Management.Smo.UserOptions UserOptions {get;}
Version                            Property     version Version {get;}
VersionMajor                       Property     int VersionMajor {get;}
VersionMinor                       Property     int VersionMinor {get;}
VersionString                      Property     string VersionString {get;}
#>

# Step 2 - Let's say we are interested in Writable and Dirty. Writable - you have both 'get' and 'set' methods for the property. Dirty - the value has been changed in the buffer, but not on the server yet.

PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto

# Step 3 - Witness how alter() affect Dirty
PS SQLSERVER:\SQL\XU-ACER-PC> $a=dir | ? {$_.INSTANCEName -eq "" }
PS SQLSERVER:\SQL\XU-ACER-PC> $a.BackupDirectory="C:\users\xu-acer-pc\documents"
PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto
<#
Name                        Writable Dirty
----                        -------- -----
AuditLevel                      True False
BackupDirectory                 True  True
BuildNumber                    False False
DefaultFile                     True False
DefaultLog                      True False
ErrorLogPath                   False False
.............
#>
PS SQLSERVER:\SQL\XU-ACER-PC> $a.Alter()
PS SQLSERVER:\SQL\XU-ACER-PC> $a.properties | ft name, writable,dirty -auto
<#
Name                        Writable Dirty
----                        -------- -----
AuditLevel                      True False
BackupDirectory                 True False
#>

# Step 3 - look at another one: SQL Server Job Properties
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> dir
<#
Name
----
Cycle Error Log
syspolicy_purge_history
#>

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a=dir
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a.properties | ft name, writable, dirty -auto

<#
Name                   Writable Dirty
----                   -------- -----
Category                   True False
CategoryID                 True False
CategoryType               True False
CurrentRunRetryAttempt    False False
CurrentRunStatus          False False
CurrentRunStep            False False
DateCreated               False False
DateLastModified          False False
DeleteLevel                True False
Description                True False
EmailLevel                 True False
EventLogLevel              True False
HasSchedule               False False
#>
#If enounting an error because of multiple instances of jobs, try below
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a | % {$_.properties | ft name, writable, dirty -auto}

# All job properties are the same, you should need only one.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\JobServer\Jobs> $a |Select -first 1 | % {$_.properties | ft name, writable, dirty -auto}


###################### Script PK/FKs in Powershell #####################

#Step 1 - go to the right location to see the available propeties

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a=dir
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | gm

#Step 2 - script all FKs to the screen
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.ForeignKeys}|%{$_.Script()}

#Step 3 - script all indexes to the screen
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script()}

#Step 4 - script all indexes to a file
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script() | Out-file "C:\indexes.sql" -append}

#Step 5 - script all indexes to a file, adding "GO" to make each index creation as a separate batch, instead of treating all of the index creation as a single batch.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script() | Out-file "C:\indexes.sql" -append; "GO" |Out-file c:\indexes.sql -append; $_.name}

#Step 6 - Change and use the scripting options
# Since we use SQLPS from SQL Server, we can create a scripting object without loading the assembly
# The cmdletsbelow create a new scripting option object
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $SObj=new-object Microsoft.sqlserver.management.smo.scriptingoptions

#List all of the properties for scripting options:

PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $Sobj

<#
FileName                              :
Encoding                              : System.Text.UnicodeEncoding
DriWithNoCheck                        : False
IncludeFullTextCatalogRootPath        : False
BatchSize                             : 1
ScriptDrops                           : False
TargetServerVersion                   : Version120
TargetDatabaseEngineType              : Standalone
AnsiFile                              : False
AppendToFile                          : False
ToFileOnly                            : False
SchemaQualify                         : True
IncludeHeaders                        : False
IncludeIfNotExists                    : False
WithDependencies                      : False
DriPrimaryKey                         : False
#>

# Now we can change these scripting options such as Scriptdrops, and use them. The cmdlets below first change the scriptdrop to true,then call the value to create the drops of the indexes.
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $Sobj.ScriptDrops=1
PS SQLSERVER:\SQL\XU-ACER-PC\DEFAULT\databases\adventureworks2014\tables> $a | %{$_.indexes}|%{$_.Script($SObj) | Out-file "C:\indexesdrop.sql" -append; "GO" |Out-fi
le c:\indexesdrop.sql -append; $_.name}