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 thoroughly tested.
#Require -Version 5
#Require -Modules SqlPS, FailoverClusters, ActiveDirectory
function Deploy-Database{
param(
[Parameter(Mandatory=$true)]
[string]$DestServer,
[Parameter(Mandatory=$true)]
[string]$SourceServer,
[Parameter(Mandatory=$true)]
[string]$SourceDatabase,
[ValidateSet(2050,2060,2070,1433)]
[int]$Port = 1433,
[string]$BackupLocation = "\\NetworkLocation\Backups"
)
begin{
}
process{
[string]$backupFile = "$BackupLocation\$SourceDatabase.bak"
if([System.IO.File]::Exists($backupFile)){
[System.IO.File]::Delete($backupFile);
}
#ensure source is pingable
if(!(Test-Connection -ComputerName $SourceServer -Count 2 -Quiet)){
throw "Source server $SourceServer could not be contacted. Please verify that this server is correct or is reachable."
}
#ensure destination is pingable
if(!(Test-Connection -ComputerName $DestServer -Count 2 -Quiet)){
throw "Destination server $DestServer could not be contacted. Please verify that this server is correct or is reachable."
}
#ensure db exist on source and is online and can be backed up
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$SourceServer,$Port"
$db = $srv.Databases | ?{$_.Name -eq $SourceDatabase -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal}
if(!$db){
throw "Database $SourceDatabase on server $SourceServer does not exist, or is not in a state that allows backups."
}
#backup source database
$srv.ConnectionContext.StatementTimeout = 0;
Backup-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile -CompressionOption On
$srv.ConnectionContext.Disconnect();
$srv = New-Object Microsoft.SqlServer.Management.smo.Server "$DestServer,$Port"
$cluster = gwmi -class "MSCluster_Cluster" -namespace "root\mscluster" -computername $DestServer -Authentication PacketPrivacy -ErrorAction SilentlyContinue
if($cluster -and $srv.IsHadrEnabled){
$sqlNodes = New-Object System.Collections.Generic.List[object]
$agGroups = New-Object System.Collections.Generic.List[object]
#if multiple ag groups, restore to the ag group with the least number of databases
foreach($ag in $srv.AvailabilityGroups){
[void]$agGroups.Add([PSCustomObject]@{
Name = $ag.Name
PrimaryName = $ag.PrimaryReplicaServerName
DatabaseCount = $ag.AvalabilityDatabases.Count
});
}
$agGroup = $agGroups | sort DatabaseCount | select -First 1
$srv.ConnectionContext.Disconnect();
#get nodes in cluster to restore the db onto
Get-Cluster -Name $cluster.Name | Get-ClusterNode | %{
$nodeName = $_.Name
[void]$sqlNodes.Add([PSCustomObject]@{
ServerName = $nodeName
});
}
#test the sql connectivity on the nodes before trying any restores. If any fail, boot.
#additionally, test to make sure that the database doesn't already exist on any of the nodes.
$sqlNodes | %{
$serverName = $_.ServerName
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection "$serverName,$Port"
try{
$srvConn.Connect();
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
if($srv.Databases[$SourceDatabase]){
throw "Database $SourceDatabase already exists on server $serverName. Restore will not continue."
}
}
catch{
throw "Could not connect to server $ServerName. Database will not be restored. Exception is:`r`n$($_ | fl -force | out-string)"
}
finally{
$srvConn.Disconnect();
}
}
#restore with norecovery to all nodes
$sqlNodes | %{
$serverName = $_.ServerName
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$serverName,$Port"
$srv.ConnectionContext.StatementTimeout = 0;
Restore-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile -NoRecovery
$srv.ConnectionContext.Disconnect();
}
#connect to primary of group and recover just that database
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$($agGroup.PrimaryName),$Port"
$db = $srv.Databases[$SourceDatabase] #just another check...
if(!$db){
throw "Database $SourceDatabase on server $($agGroup.PrimaryName) does not exist. If the database $SourceDatabase exists on servers $($sqlNodes -join ',') they will need to be dropped before another deploy can be attempted."
}
$srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$SourceDatabase] WITH RECOVERY;");
$srv.ConnectionContext.Disconnect();
#add primary to AG
Add-SqlAvailabilityDatabase -Database $SourceDatabase -Path "SQLSERVER:\SQL\$($agGroup.PrimaryName)`,$Port\DEFAULT\AvailabilityGroups\$($agGroup.Name)"
#add subsequent nodes to AG
$sqlNodes | ?{$_.ServerName -ne $agGroup.PrimaryName} | %{
$serverName = $_.ServerName
Add-SqlAvailabilityDatabase -Database $SourceDatabase -Path "SQLSERVER:\SQL\$ServerName`,$Port\DEFAULT\AvailabilityGroups\$($agGroup.Name)"
}
}
else{
#just a single instance restore....
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$DestServer,$Port"
$srv.ConnectionContext.StatementTimeout = 0;
if($srv.Databases[$SourceDatabase]){
throw "Database $SourceDatabase already exists on server $DestServer."
}
Restore-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile
$srv.ConnectionContext.Disconnect();
}
}
end{
}
}
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 find the required dll’s under sql 2012…). Edit 20170410: The 64 bit dll’s are located in ‘C:\Program Files\Microsoft SQL Server\120\Shared’ for sql server 2014. I’m assuming they’re in the 130 directory for Sql Server 2016.
$path = '\\ServerName\C$\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log\AuditCapture_0_131300599577540000.xel'
#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XE.Core.dll'
#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XEvent.Linq.dll'
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SqlServer.XE.Core.dll'
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SqlServer.XEvent.Linq.dll'
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($path)
$sb = New-Object System.Text.StringBuilder
$events | select -First 10 | %{
$event = $_
[void]$sb.Append("$($event.Timestamp);;");
for($i=0;$i-lt($event.Fields.Count-1);$i++){
[void]$sb.Append("$($event.Fields[$i].Value.ToString().Replace("`r`n", ''));;");
}
$event.Actions | %{
$action = $_
[void]$sb.Append("$($action.value.ToString().Replace("`r`n", ''));;");
}
[void]$sb.Append("ServerName;;");
[void]$sb.AppendLine();
}
$sb.ToString();
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.
Import-Module SqlPS -DisableNameChecking
$destServers = @('Server1', 'Server2', 'Server3')
$srcPath = "\\sharepath\Backups"
try{
$destServers | %{
$serverName = $_
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$serverName,1433"
gci -Path $srcPath | ?{$_.Extension -eq '.bak'} | %{
$dbName = $_.BaseName
$backupFile = $_.FullName
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$restore.Devices.Add($backupDevice);
$restore.NoRecovery = $true;
$restore.ReplaceDatabase = $true
$restore.Database = $dbName
$backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($backupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$restore.Devices.Add($backup);
$dbFiles = $restore.ReadFileList($srv);
$moveFiles = @();
foreach($dbFile in $dbFiles){
$fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
switch($dbFile.Type){
'D'{
$moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "E:\MSSQL\Data\$fileName")
}
'L'{
$moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "G:\MSSQL\TranLog\$fileName")
}
}
}
$srv.ConnectionContext.StatementTimeout = 0;
restore-sqldatabase -InputObject $srv -Database $dbName -BackupFile $backupFile -RelocateFile $moveFiles -ReplaceDatabase
}
}
}
catch{
$_ | fl -Force
}
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 Name=PowerShell_BCP");
$srcCommand = New-Object System.Data.SqlClient.SqlCommand($query, $srcConn);
$destBulk = New-Object System.Data.SqlClient.SqlBulkCopy($destConn);
$destBulk.BulkCopyTimeout = 0;
$destBulk.BatchSize = 1000
$destBulk.DestinationTableName = 'DestTable'
$srcConn.Open();
$destConn.Open();
$results = $srcCommand.ExecuteReader();
$destBulk.WriteToServer($results);
$srcConn.Close();
$destConn.Close();
}
catch{
$_ | fl -Force
}
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 the Server object. We then go through all the databases on the server and print out the name of the databases:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
foreach($database in $Server.Databases){
$database.Name
}
$srvConn.Disconnect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
Lets step it up a bit and work on more than one server at a time. Create a text file named Servers.txt and put it on the root of your c:\ drive. In said file, put the name of a few servers that you want to query:
If you don’t have more than one server, just go into your ConfigurationManager in windows and add a few alias’ in that point to your local instance (that’s what I did…). Make sure to add them to both the 32-bit and 64-bit Sql Native Client alias’.
So, let’s iterate through all databases on all our servers:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
get-content -Path C:\Servers.txt | %{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $_
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
Write-Host $_ -ForegroundColor Red
foreach($database in $Server.Databases | where{!$_.IsSystemObject}){
"Database name is: $($database.Name)";
}
$srvConn.Disconnect();
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
The ‘%’ is an alias for ‘foreach-object’ for the list of servers in the text file. The ‘$_’ is just a reference to the current item in the list of servers in the text file.
- Use Get-Content to retrieve the contents of our c:\Servers.txt file.
- Iterate through the servers in the file, each time changing the server name ($_) in the ServerConnection object.
- Filter using the where-object to only show non-system databases (where{!$_.IsSystemObject})
The $database.name is surrounded by $() so it can evaluate the database name. If it weren’t there, the string would print out the database name with .Name after it, as shown below:
Even though I’m using a ServerConnection object to connect via the Server object, you don’t have to do this. You can just as easily pass the servername string into the Server object and it will work just fine.
$Server = new-object Microsoft.SqlServer.Management.smo.Server 'bacon'
So, the server object is the object you’ll most likely be using the most when administering the server. Under the server object, you’ll find logins, databases, server roles, the sql agent job server, etc…
In upcoming posts, we’ll go through examples of adminstering the most common objects that hang off the Server object.
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 data into the database using a ServerConnection object. Now, we’re going to execute a query to get that data back out. When you call the ExecuteReader method of the ServerConnection object and pass it in a SELECT statement, it will return you a SqlDataReader object:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
while($sdr.Read()){
$sdr.GetInt32(0)
$sdr.GetString(1)
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
This does a while loop on the SqlDataReaders’ Read() method, which will return $true as long as there are records to be read.
While using the SqlDataReader is fast and efficient, it’s not the most user-friendly object to use for reading data in powershell. For ease of use, you can’t beat a DataTable.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | ft -AutoSize
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
In this code, we’re still executing the same statement and loading the SqlDataReader object, but then we create a new System.Data.Datatable object and call the Load method on the DataTable and pass in the SqlDataReader. This object is probably the easiest object to work with for reading data from Sql Server with powershell. Here’s another example using a DataTable to export data to a csv file:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | Export-Csv -Path "$env:windir\temp\TestCSV1.csv" -Force -NoTypeInformation
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
The –Force will overwrite the file if it already exists. The –NoTypeInformation does what it says, it omits type information from the file. If you don’t specify this, you’ll see extra type information output into the csv file that you probably don’t want.
You can also filter on the values in the DataTable via the where-object clause in powershell as well:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$dataTable = New-Object System.Data.DataTable
$srvConn.Connect();
$sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
$dataTable.Load($sdr);
$dataTable | where{$_.ID -gt 3} | %{
$_
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
31 Posts of using Sql Server with Powershell — Post 2: ServerConnection
The first post consisted of getting the powershell cmdlets installed/imported into your powershell environment (in my case, powershell_ise). For day 2, we’re going to look at how we can connect to a Sql Server instance.
ServerConnection
The serverconnection is just what it sounds like; a connection to your sql server instance. In the code below, all we’re doing is importing the SqlPS module, then creating a ServerConnection object and passing in the name of the server instance that we want to connect to, then opening said connection.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.Connect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
I’ll explain a couple of the details the details here. The Push-Location at the beginning of the code is used to store your current location, and the Pop-Location at the end just returns you to that location when we’re done running this code.
You don’t NEED to do this, but the Import-Module SqlPS will switch you to the SQLSERVER drive, which can then interfere with other cmdlets you may be running. It’s just a good habit to get into when using the SqlPS provider, sadly not one I’ve consistently formed.
Secondly, in the catch statement I’m taking the current exception object, doing a format-list on it and doing a –Force. The reason why I’m doing this is so you can see the details of the exception further down than the first error. Best explained with an example. Take the following code:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.LoginSecure = $false;
$srvConn.Login = 'snewman'
$srvConn.Password = 'Test'
$srvConn.Connect();
}
catch{
$_ #| fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
Notice I have the ‘ | fl –Force’ commented out in the catch statement. When I run this code, I get the following error:
So, what is this error really telling me? All I know is that it failed to connect, not what the problem really was. Is my server name incorrect? Was my password incorrect?
Now, run the same code again, this time without the ‘| fl –Force’ commented out:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.LoginSecure = $false;
$srvConn.Login = 'snewman'
$srvConn.Password = 'Test'
$srvConn.Connect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
After running this code, I get much, much more information about the error:
I can see that the issue was that the login was indeed incorrect.
Okay, now that that is all explained, let’s get back to the ServerConnection object. If you want to use AD authentication with the ServerConnection object, there is a property named LoginSecure that will set this. It is true by default, so to use AD auth, just open the connection on the ServerConnection object and it will use it by default.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.Connect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
If you want to use Sql Server authentication, you have to set a few more properties.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.LoginSecure = $false;
$srvConn.Login = 'LoginName';
$srvConn.Password = 'P@55w0rd'
$srvConn.Connect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
First, you have to set the LoginSecure property to $false. If you try to set the Login when the LoginSecure is not $false, you will receive an error:
There is also another property on the ServerConnection object named SecurePassword.
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.LoginSecure = $false;
$srvConn.Login = 'LoginName';
$srvConn.SecurePassword = (ConvertTo-SecureString -String 'P@$$w0rd' -AsPlainText -Force)
$srvConn.Connect();
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
This property exists so you can encrypt your passwords via a secure string (most likely held in a file) and use that to more securely handle passwords. This same type of scenario also exists for the ConnectionString property & SecureConnectionString property.
The most common methods for this object are the Execute* properties for executing various types of Sql statements.
Here is a quick example of using the ExecuteNonQuery to create a table:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
$srvConn.Connect();
$srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
Now, let’s have some fun with inserting data into the table:
Push-Location
Import-Module SqlPS -DisableNameChecking
try{
$srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'Bacon'
$srvConn.Connect();
#$srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");
1..1000 | %{
$srvConn.ExecuteNonQuery("INSERT INTO dbo.Test(ID, Value) SELECT $($_), 'Test $($_)'") | Out-Null
}
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location
Essentially, what this is doing is for 1 to 1000 (1..1000) execute the ExecuteNonQuery method and insert the current number ($_) into the ID column and insert ‘Test_[current number]’ into the Value column. Now, this 1..1000 could be values read from a file, read from another database, a custom psobject, you get the idea.
For the next post, we’ll look at getting data out of a database.
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 most database administrators when it comes to dealing with powershell. Do yourself a favor and learn it. You’ll wonder how you ever got by without it before.
So here we go with post 1.
Importing Sql Server Cmdlets
In order to begin this series, you’ll need to import the sql server cmdlets that will enable you to work with the smo objects that you’ll use to work with sql server. If you have sql server management studio 12 and above, you’re in luck. All you need to do to import the sql server cmdlets is this:
Import-Module SqlPS -DisableNameChecking;
The –DisableNameChecking is optional, but if omitted you’ll get a warning that some of the verbs in the cmdlets are unapproved:
A word of warning; this module loads extremely slow. This has been fixed in Sql Server 2016, but as noted in the closing notes of this post, that requires that SSMS for Sql Server 2016 be installed.
If you’ve got Sql Server 2008 R2 and below, you’ll need to install a few things in order to get the sql server cmdlets working correctly. More or less, the SqlPS module requires the use of Sql Server 2012 Shared Management Objects, so you’ll need to download 3 components from the Sql Server 2012 (or 2014) feature pack:
First, you’ll need download and install the SystemCLR Types for Sql Server 2012/14:
Next, you’ll need to download and install the Sql Server Shared Management objects:
And finally, download and install the Windows Powershell Extensions for Sql Server:
Once you have all three of these installed and restart your powershell environment, you should be able to run the Import-Module SqlPS command correctly.
Okay, post 1 complete. Sort of. As of Sql Server 2016, the SqlPS module is being replaced by a new SqlServer module, but that requires that the Sql Server 2016 SSMS must be installed to use it (at the time of this writing), so we’re just going to plod along using SqlPS for now. Most of the functionality provided in the SqlServer module will still work.
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 difference. I could connect whether or not the IP1 value reflected the correct IP address or not. But, having a different IP listed in the configuration manager as opposed to what it really is just feels dishonest.
Import-Module SqlPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
cls
try{
$srvName = "servername"
$Instance = "mssqlserver"
$urn = "ManagedComputer[@Name='$srvName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"
$wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $srvName
$tcp = $wmi.GetSmoObject($urn);
$tcp.IPAddresses["IP1"].IPAddressProperties["IpAddress"].Value = '10.10.1.1';
$tcp.Alter();
}
catch{
$_ | fl -Force
}
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 use powershell to stop the service, then restart the service in single-user mode and grab a serverconnection to it immediately in powershell_ise:
Import-Module SqlPS -DisableNameChecking cls Stop-Service -Name "MSSQL`$SQL2012" -Force net start "MSSQL`$SQL2012" /m $sqlcmd = New-Object Microsoft.SqlServer.Management.Common.ServerConnection '.\sql2012' $sqlcmd.Connect();
I can then reference the $sqlcmd object in other tabs in powershell_ise to work in a more pleasant environment:
Notice, I can’t connect in SSMS:
Just a helpful tip.
