Set Database Owner using Powershell

Quick script to set the database owner using powershell. Sure, you could do this in t-sql by just using EXEC sp_changedbowner, but where’s the fun in that? Also, you can do multiple databases with this one. Take that T-SQL!

$SQLAdminUser = 'AdminUser'
$SQLAdminPwd = get-content "PathToSecuredCredentials.txt" | convertto-securestring 

$srvConn = New-Object('Microsoft.SqlServer.Management.Common.ServerConnection') ('ServerName', $SQLAdminUser, $SQLAdminPwd)
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $srvConn

$srv.Databases | where{$_.Name -eq 'DatabaseName'} | 
foreach {
	$_.SetOwner('sa')
	$_.Alter();
}

Use at your own risk.

Set All Database Recovery using Powershell

Quick one. How to set all your databases to a specified recovery model using powershell. You’ll need SQLPSX to run this.

$srv = Get-SqlServer -sqlserver 'ServerName'

$dbs = $srv.Databases | where {$_.Name -ne "DBNotFullRecovery" -and (!($_.IsSystemObject))} 
foreach($db in $dbs)
{ 
	$db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full
	$db.Alter()
}

LOB Columns & sys.partitions

On the table and index organization page in the sql server documentation link here, under the Allocation Units section, the documentation states:

Each table that has at least one LOB column also has a row in sys.partitions with index_id > 250.

Whilst looking for sparsely populated image columns in one of my tables that’s taking up waay too much room I noticed that said table does indeed contain a LOB column (image data type), yet has no index listed in sys.partitions with an index_id > 250.

Here is the results from querying the sys.partitions table:

Here’s the allocations from the sys.system_internals_allocation_units table. Obviously a bunch of unused space in this table.

Is the documentation wrong? Why you sys.partitions no have index_id > 250 for image column?

Y U No - sys.partitions why u no idx gt 250?

Start Instance & Assign ElasticIP on AWS with Powershell

Here’s a script to start an EC2 instance and assign an elastic ip to said instance. I knocked this together from a couple of other scripts, to which I don’t have the link to anymore.

param(
	[string]$instanceID = "InstanceID",  
	[string]$keyID = "KeyID",
	[string]$accessKeyID = "SecretAccessKeyID",
	[string]$EIP = "xx.xx.xx.xx",
	[string]$ServiceURL="https://ec2.us-east-1.amazonaws.com"
)

cls

Add-Type -Path "C:\Program Files (x86)\AWS SDK for .NET\bin\AWSSDK.dll"

$config=New-Object Amazon.EC2.AmazonEC2Config
$config.ServiceURL = $ServiceURL

$ec2 = [Amazon.AWSClientFactory]::CreateAmazonEC2Client($keyID,$accessKeyID, $config)
$startReq = New-Object amazon.EC2.Model.StartInstancesRequest
$startReq.InstanceId.Add($instanceID);
$startResponse = $ec2.StartInstances($startReq)
$startResult = $startResponse.StartInstancesResult;
$startResult

$request = New-Object -TypeName Amazon.EC2.Model.DescribeAddressesRequest
[void]$request.WithPublicIp($EIP)
 
$result = New-Object -TypeName Amazon.EC2.Model.DescribeAddressesResponse
try 
{
    $result = $client.DescribeAddresses($request)
}
catch 
{
    echo "Failed to validate EIP $instanceEIP, ensure that it is allocated and associated with your account.  Aborting."
    exit 2
}

# See if an instanceID is already assigned to this EIP
$xml = $result.ToXML()
$assignedInstanceID = [string]($xml.DescribeAddressesResponse.DescribeAddressesResult.Address.InstanceId)

# Run this block if an Instance already has this EIP associated to it... just in case changes would
# result in downtime (i.e. if we are launching a test system from a cloned production one).
if ($assignedInstanceID) 
{
  echo "Address $EIP already assigned to: $assignedInstanceID, aborting."
  exit 1
}

# If we get here, the IP is free and clear, go ahead and associate it.
$request = New-Object -TypeName Amazon.EC2.Model.AssociateAddressRequest
[void]$request.WithInstanceId($instanceID)
[void]$request.WithPublicIp($EIP)

$result = $client.AssociateAddress($request)
if ($result) 
{
  echo "Address $EIP assigned to $instanceID successfully."
  exit 0
}
else 
{
  echo "Failed to assign $EIP to $instanceID."
  exit 3
}

Here is the code to stop an instance.

param(
	[string]$instanceID = "InstanceID",
	[string]$keyID = "KeyID",
	[string]$accessKeyID = "AccessKeyID"
)

Add-Type -Path "C:\Program Files (x86)\AWS SDK for .NET\bin\AWSSDK.dll"

$ec2 = [Amazon.AWSClientFactory]::CreateAmazonEC2Client($keyID,$accessKeyID)

$stopReq = New-Object amazon.EC2.Model.StopInstancesRequest
$stopReq.InstanceId.Add($instanceID);
$stopResponse = $ec2.StopInstances($stopReq)
$stopResult = $stopResponse.StopInstancesResult;