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 {


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

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. 

	[string]$instanceID = "InstanceID",  
	[string]$keyID = "KeyID",
	[string]$accessKeyID = "SecretAccessKeyID",
	[string]$EIP = "xx.xx.xx.xx",


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
$startResponse = $ec2.StartInstances($startReq)
$startResult = $startResponse.StartInstancesResult;

$request = New-Object -TypeName Amazon.EC2.Model.DescribeAddressesRequest
$result = New-Object -TypeName Amazon.EC2.Model.DescribeAddressesResponse
    $result = $client.DescribeAddresses($request)
    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

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

Here is the code to stop an instance.

	[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
$stopResponse = $ec2.StopInstances($stopReq)
$stopResult = $stopResponse.StopInstancesResult;