Sql Server Powershell

Category: Powershell


Get Index Key Length via Powershell

Yep. Advertisements

Read More

Capture Sql Messages output to Variable

To capture the sql output messages (what you see in the “Messages” tab in SSMS) you can use this:

Read More

SqlAgent not Capturing Errors from Powershell

Some strange behavior I noticed with sql server agent not capturing thrown errors from powershell.  Take this code: This will most certainly error, and if you run it in powershell, yep: Right then, let’s call it from an agent job…

Read More

Restore Database to AvailabilityGroups

Code more for me than you.  Backup a database and restore it to an availability group (picks the AG that has the least number of databases).  If the server isn’t clustered or doesn’t have HADR enabled, just restores it.  Not…

Read More

Read an Extended Events File via Powershell

Admittedly, I don’t know that much about this, I just started fooling around with this tonight (via this post).  As stated in said post, this works for only under x86 and AFAIK, needs at least Sql Server 2014 (I couldn’t…

Read More

Restore Database with Move Files

More for me than you.  Something I type up all the time and forget where the heck I put it.  Not tested, but should give you the general idea.

Read More

BCP via Powershell

Quick post on how to use BCP with powershell.  Adjust options to suit your needs. Import-Module SqlPS -DisableNameChecking $query = “SELECT * FROM TABLE” try{ $srcConn = New-Object System.Data.SqlClient.SqlConnection(“PersistSecurityInfo = true;Server=Server1;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP”); $destConn = New-Object System.Data.SqlClient.SqlConnection(“PersistSecurityInfo = true;Server=Server2;Database=DBName;Integrated Security=True;Application…

Read More

31 Posts of using Sql Server with Powershell — Post 4: Server Object

For the 4th post, we’re going to get go into managing Sql Server itself rather than the data.  For that, we’re going to need a new object, the Server object.  In this example, we’re using the ServerConnection object to connect to…

Read More

31 Posts of using Sql Server with Powershell — Post 3: Retrieving Data

For the 3rd post, we’re going to look at retrieving data from Sql Server.  It’s not much more complicated than the second post, but it does involve some new objects. On the last post, we finished off with inserting some…

Read More

31 Posts of using Sql Server with Powershell: Modules

This isn’t a 31 days series, as I’ll undoubtedly miss a day here and there, so instead, this will just be a ’31 posts’ series instead.  I’m pragmatic.  And lazy. I have noticed there is a bit of discomfort amongst…

Read More