PowerShell T-SQL Scripting

/ PowerShell Scripting /

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

 

MSSQL Move Tempdb Files

/ MSSQL Server /

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.

MSCLUSTER 2008 Cluster Commandline

/ MS Windows Server /

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

MSSQL Check TransactionLog Backup

/ MSSQL Server /

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 = 0

MSSQL Restore Torn Page

/ MSSQL Server /

To 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.

MSSQL Truncate TransactionLog Without Backup

/ MSSQL Server /

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

 

LINUX Loop Device

/ GNU/Linux /

You can create a loopback or iSCSI file using dd. The following example creates a file with an initial size o 0 bytes and a allocated size of 8Gb.

dd if=/dev/zero of=LUN3 bs=1000 count=0 seek=$[1000*1000*8]

Mount the file using the following command.

mount -o loop /path/to/file /path/to/mountpoint