Sql Server Powershell

Category: Powershell


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

Change Sql Server Configuration Manager IP Address

To change the IP address in sql servers’ configuration manager, you can use the following code.  Of note, when I changed the IP address in configuration manager under IP1 manually and tested connections, it really didn’t make a lick of…

Read More

Powershell & Sql Server Single User Mode

Sometimes (nigh, always) when you have to start a sql server in single user mode (the ‘/m’ after the net start…), executing queries in the sqlcmd prompt can be a bit of a painful endeavor.  What I usually do is…

Read More

Synchronize Sql Users and Logins with Powershell

I have a previous post about syncing users and logins via powershell, but seeing as that one uses the soon to be deprecated (if not already…no listing for this proc for sql server 2014 on MSDN…) ‘sp_change_users_login’, I thought I’d re-do…

Read More