Simple script using dsquery to query groupmembers from an Active Directory group and return the results to screen. The script takes parameters from the commandline (groupmem.ps1 “DOMAIN” “GROUP_NAME”).
[string]$sADPath = dsquery group -domain "$($args[0])" -name "$($args[1])"
$sADPath = "LDAP://" + $sADPath | Foreach-Object {$_ -replace "`"", ""}
$oADGroup = [ADSI]("$sADPath")
$oADGroup.path
$oADGroup.member
Search for specific processes using WMI and then stop these processes.
$vResult1 = Get-WMIObject Win32_Process -filter "CommandLine like '%<argument>%'"
foreach ( $vProcess in $vResult1 ) {
Stop-Process -Id $vProcess.ProcessId
}
Article written in the Dutch language…
Met onderstaande commando’s kun je d.m.v. T-SQL gegevens opvragen uit een MSSQL database m.b.v. SQL PowerShell Modules of SQLPS.
$sResult = Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS Servername;" `
-QueryTimeout 3 `
-ServerInstance [SERVERNAME][INSTANCE] `
-Database master
Vervolgens vraag je de inhoud van bovenstaande voorbeeld d.m.v. variable $sResult.Servername. Bij een een resultaat van meer dan een record worden de values opgeslagen in een array. In dat geval geeft de variabele $sResult.count het aantal geretourneerde regels terug (-gt 1). Je kunt dan bijvoorbeeld de eerste regel van het resultaat opvragen d.m.v. $sResult[0].columnname. Je kunt m.b.v. onderstaande statement afvangen of het resultaat uit meer dan twee regels bestaat.
if ($sResult.count -gt 1) {
# TestVoorbeeld afhandelen Array...
write-host "Er zitten " $sResult.count " values in de array..."
foreach ($oResultItem in $sResult) {
$oResultItem.Servername
}
} else {
# Afhandelen string value...
$sResult.Servername
}
Ook is het mogelijk vanuit een specifieke context gegevens op te vragen.
cd SQLSERVER:SQL{servername}{instancename}Databasesmsdb
Invoke-Sqlcmd -Query "SELECT * FROM sysjobs;" -QueryTimeout 3
Move tempdb files to a different location.
USE MASTER GO ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'D:\Path\To\tempdatafile.mdf') GO ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'L:\Path\To\templogfile.ldf') GO
Restart the database engine and check if the new files are created. For more information on file locations you can use the stored procedure sp_helpfile. Remove the files from the original location.
Display cluster resources.
cluster [group | res]
PS> [Get-ClusterGroup | Get-ClusterResource] -Cluster "<CLUSTERNAME>"
PS> Get-ClusterGroup -Cluster "<CLUSTERNAME>" | Where-Object { $_.OwnerNode -eq "<CLUSTERNODE>" } | SELECT Name
Move a cluster group to a different node.
cluster group "Cluster Group" /moveto:<NODENAME> PS> Move-ClusterGroup -Name "Cluster Group" -Cluster "<CLUSTERNAME>" -Node "<CLUSTERNODE>"
Display all registered nodes for a resource.
cluster res "SQL Network Name (NETWORKNAME)" /listowners PS> Get-ClusterOwnerNode -Cluster "<CLUSTERNAME>" -Resource "<CLUSTERRESOURCE>
Bring cluster group online…
PS> Start-ClusterGroup -Name "<CLUSTERGROUPNAME>"
Switch a AlwaysOn Group to a secondary replica…
Switch-SqlAvailabilityGroup -Path SQLSERVER:\SQL\[SERVER]\[INSTANCE]|DEFAULT\AvailabilityGroups\[AOGROUP]
Change witness fileshare on a NodeAndFileMajority cluster. First change to NodeMajority and then back to NodeAndFileMajority using the new fileshare.
PS> Set-ClusterQuorum -NodeMajority PS> Set-ClusterQuorum -NodeAndFileShareMajority \\server\fswitness$\clustername
Check if the are database without recent (2 days) TransactionLog backups for all databases in FULL Recovery.
SELECT sys.databases.name
FROM sys.databases
WHERE sys.databases.name NOT IN
(SELECT DISTINCT sys.databases.name from sys.databases
INNER JOIN msdb..backupset ON sys.databases.name = msdb..backupset.database_name
WHERE (msdb..backupset.type = 'L' AND msdb..backupset.backup_start_date > (GetDate() -2)))
AND sys.databases.recovery_model != 3 and sys.databases.state = 0To restore a specific torn page first make a backup from the transaction log with NORECOVERY (tail backup).
RESTORE DATABASE Databasename PAGE='1:234' FROM DISK='X:\Path\To\Full\DatabaseBackupFile.bak' WITH NORECOVERY GO
Then restore all transaction log backups made sinds the Full backup with NORECOVERY. Then restore the tail backup WITH RECOVERY.
With the following command you can truncate the transaction log withouot making a backup. Don’t forget to make a full backup afterwards.
ALTER DATABASE [DATABASENAME] SET RECOVERY SIMPLE WITH NO_WAIT
You can then maby do a shrink of the log file and then set the database back to FULL recovery.
ALTER DATABASE [DATABASENAME] SET RECOVERY FULL WITH NO_WAIT
The script below connects an existing user in the database to an existing server login.
USE [DATABASE]
ALTER USER DbUser WITH LOGIN = [LoginUser]
GO