header image

Archive for PowerShell and SQL Server

My capacity planning series on the Scripting Guy blog finished last week. Didn’t get chance to post about it as I was at Microsoft in Seattle.

Full series and associated powertip postings:

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/18/powertip-compare-the-contents-of-files-with-powershell.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/18/the-admin-s-first-steps-capacity-planning-part-3.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/11/powertip-use-powershell-to-format-dates.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/11/the-admin-s-first-steps-capacity-planning-part-2.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/04/powertip-view-network-statistics-with-powershell.aspx
http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/04/the-admin-s-first-steps-capacity-planning.aspx

Enjoy

under: Office 2013, PowerShell and SQL Server, PowerShell and WMI, PowerShell original

Capacity Planning part 2

Posted by: | November 11, 2013 | No Comment |

My capacity planning mini series on the Scripting Guy blog continues with the second part – dealing with storing data in SQL Server -  available today

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/11/the-admin-s-first-steps-capacity-planning-part-2.aspx

I’ll repeat the URL for the first part for reference

http://blogs.technet.com/b/heyscriptingguy/archive/2013/11/04/the-admin-s-first-steps-capacity-planning.aspx

under: PowerShell and SQL Server, PowerShell original

Loading the SQLPS module

Posted by: | September 10, 2013 | No Comment |

Loading the SQLPS module gives you quick access to the sql cmdlets, sql provider and the SMO assemblies. It also, infuriatingly, moves your location into the sql provider.

I (really) *  (lots ) hate things that assume what I want to do.

You can use PowerShell to reverse this annoying, and arrogant action:

Push-Location

Import-Module SQLPS

Pop-Location

Will get you back where you were.

SQLPS is slowly becoming a civilised member of the PowerShell eco-system especially when compared to its first incarnation.  A few more versions and it might behave properly.

under: PowerShell and SQL Server

SMO with SQL Server 2012

Posted by: | September 9, 2013 | No Comment |

I’ve been working quite a bit with PowerShell and SQL Server 2012 just recently. In earlier versions of SQL Server you had to load the SMO assemblies to get access to the admin functionality.

In SQL Server 2012 –

import-module sqlps

gives you the SQL Server provider, the cmdlets and loads the SMO assemblies for you.

Nice and easy so I don’t have to do anything extra. That’s a good module

under: PowerShell and SQL Server

Dropping a database

Posted by: | June 26, 2013 | No Comment |

A question came up on the forum regarding dropping a database & I realised it was something I hadn’t done before.

SMO provides a set of classes for managing SQL Server. You get SMO when you install the SQL Server management tools

Import the module to load SMO assemblies

import-module sqlps

get the server object and view the databases
$server = New-Object  Microsoft.SqlServer.Management.Smo.Server("w12scorc")
$server.Databases

view the target database
$server.databases["mydb"]

drop the database and view the databases again
$server.databases["mydb"].Drop()
$server.databases

under: PowerShell and SQL Server

Manning have PowerShell in Practice on a half price offer today. Go to www.manning.com and use code dotd0330cc when ordering

under: Books, PowerShell and Active Directory, PowerShell and Exchange 2007, PowerShell and IIS, PowerShell and SQL Server, PowerShell and WMI, PowerShell original

The recording, slides and demo scripts from tonight’s PowerShell and SQL Server session are available as a single zip file for download from

https://skydrive.live.com/#cid=43CFA46A74CF3E96&id=43CFA46A74CF3E96%212943

The file is created with jzip but any zip handling program should be able to unzip it

Thank you to the attendees and especially for the questions – I’ve posted the answer to the question regarding accessing named instances using a port number here

http://msmvps.com/blogs/richardsiddaway/archive/2012/02/28/connecting-via-smo-to-a-named-instance.aspx

Next meeting will be 28 March 2012

Topic will probably be CIM in PowerShell v3

under: PowerShell and SQL Server, PowerShell User Group

A question came up in tonight’s User group session regarding connecting to SQL server instances using SMO

If you have just a default instance – just give the server name

$server = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList “W08R2SQl12”

 

If you have a named instance the give the instance name as well

$serverI = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList “W08R2SQl12\instance_name

 

These work as long as the SQL Server browser service is running. 

If it isn’t this is what I think you have to do.

This is untested and a best guess. I will try and test.

$cons = “server=W08R2SQL12\instance_name,port_number;Trusted_Connection=true;multipleactiveresultsets=false”

$cn = New-Object -TypeName “System.Data.SqlClient.SqlConnection” -ArgumentList $cons
$serverZ = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” -ArgumentList $cn
If anyone manages to test this please let me know – I’ll test as soon as I can

under: PowerShell and SQL Server

 

The UK PowerShell group presents a Live Meeting tomorrow on using PowerShell with SQL Server

Details from

http://msmvps.com/blogs/richardsiddaway/archive/2012/02/09/february-powershell-group-meeting-sql-server-and-powershell.aspx

under: PowerShell and SQL Server, PowerShell User Group

UG meeting reminder

Posted by: | February 20, 2012 | No Comment |

First reminder for the UG meeting on 28February – PowerShell and SQL Server

details from

http://msmvps.com/blogs/richardsiddaway/archive/2012/02/09/february-powershell-group-meeting-sql-server-and-powershell.aspx

under: PowerShell and SQL Server, PowerShell User Group

Older Posts »

Categories