SQLPSX

Resources for my SQL Server PowerShell Extensions webcast

Oct 5th, 2011

Comments are off for this post

I did another webcast for the Secrets of SQL Server webcast series today. In the today’s webcast we built on some things I had demoed in a prior webcast and showed how PowerShell in conjunction with SQLPSX can really help you solve some common problems DBAs face.

I showed how you could pull size and free space information from all of your data files, then used the same technique to pull size information from all of your tables across every database on an instance. Later we created a database and some tables, and then showed how to do some basic login comparisons, all with SQLPSX.

In addition, we stored the results of the frees pace and table size information in a database using the Out-DataTable and Write-DataTable functions. Finally we wrapped up with a quick demo on how the new SQL Server cmdlets inside of the SQLPS Module in Denali combined with some new functionality in PowerShell v3 CTP1 can really make backing up a database with PowerShell a lot easier.

Here’s what you’ll need to recreate today’s demos;

Download and install SQLPSX
The Out-DataTable function from the TechNet script repository
The Write-DataTable function from the TechNet script repository
and these scripts
Oh, and here are the handful of slides that I had

I hope I see you next week at the PASS Summit or next month during another webcast, this time on customizing your own functions to work with the SMO.

PowerShell Week at SQL University – Post 1

May 18th, 2010

by Aaron Nelson.

4 comments

Getting Started With PowerShell Variables

Yesterday we laid the ground work for PowerShell Week. Today we will learn about Variables in PowerShell. You will notice that PowerShell variables work very differently from SQL Server variables.

The evolution of variables in SQL Server is pretty straight forward. In SQL Server 2000 you had to DECLARE a variable as a specific type in one statement and then SET the value of the variable in another statement, like this: DECLARE @SQLvariable VARCHAR(12) SET @SQLvariable = ‘OldFeature’. By the time SQL 2008 hit the streets we could DECLARE and SET a variable in one statement like this: DECLARE @SQLvariable VARCHAR(12) = ‘NewFeature’ . PowerShell evolves variables to the next logical step. You no longer need to declare or type your variable. Instead PowerShell uses the value of the variable to infer it’s type. If a variable does not alreayd exist PowerShell simply creates it for you. In PowerShell our statement looks like this: $SQLvariable = ‘NewFeature’. (Beginer tip: $ identifies variables in PowerShell just like @ identifies them in SQL Server.)

Since it doesn’t work in SQL let’s try it in PowerShell: $SQLvariable = ‘NewFeature’. OK so we loaded data into a variable, ‘big deal’ right? How do we know that we actually loaded it and more importantly what datatype is it? Well in PowerShell you can simply call the variable to get its value. (Another beginer tip: you don’t need to use a keyword like SELECT for this, just the variable name: $SQLvariable.) What if you just want to determine the datatype? All you have to do is tack on the .GetType() Method: $SQLvariable.GetType().

$SQLvariable = 'NewFeature'
$SQLvariable
$SQLvariable.GetType()

When you run this here’s what your result will be:

As I alluded to earlier, another feature of PowerShell is that it remembers your variables for you, even between executions. Basically as long as you don’t end your session the variable, it’s datatype, and its value will persist. You will see later than you can also set a variable in one script and consume it in another.

Let’s try this again with a new variable and set it to a different value:

$PoShvariable = 123
$PoShvariable
$PoShvariable.GetType()

What happens if once the datatype is set we try to set it to a different, incompatible datatype? It’s going to explode right? Nope, you’ll have to throw something more difficult than that at it. Give this a try:

$PoShvariable.GetType()
$PoShvariable = 'NewFeature'
$PoShvariable
$PoShvariable.GetType()

Since PowerShell relies on .NET a variable could be any datatype in .NET so if you’re sitting inside the AdventureWorks database you can see for yourself, just run this:

$MyTable = get-item TABLESProduction.TransactionHistory
$MyTable.GetType()            

$MyStoredProc = get-item StoredProceduresHumanResources.uspUpdateEmployeePersonalInfo
$MyStoredProc.GetType()

Now earlier I mentioned that PowerShell keeps track of and remembers the variables that you previously declared. So how do you see what variables are already there? Just run this command and you can see every variable you have running in the session, even the internal ones.

Variable

In the list of items that get returned you’ll see one called Error that holds the last error message that occurred in your session. This handy feature can help you when you have to debug your own work.

Is that all there is to know about variables in PowerShell? Not hardly, but I think that’s a good stopping point for this post. More on variables later.

del.icio.us Tags: SQL Server 2008,PowerShell,SQL University,Modules,Execution Policy,SQLPSX

PowerShell Week at SQL University – Post 0

May 17th, 2010

by Aaron Nelson.

10 comments

Welcome to PowerShell Week at SQL University. For regulars at SQLvariant, SQL University is something Jorge Segarra organized to get industry experts together to create learning courses around SQL Server. These learning courses are in one week segments that focus on a specific area.

When I began blogging about PowerShell there were some complications based on your operating system. There were so many people blogging about these issues that I didn’t address it initially. Since this is an introductory course I decided to go ahead and spin up a brand new Windows 7 VM so that I can walk you through these small but vital details. If you don’t have Windows 7 this would be a great time to upgrade. If you can’t upgrade then download PowerShell for you operating system from this link. Now, onto getting started:

Disclaimer:
PowerShell opens in a protected state as a security measure. To enjoy the full power of PowerShell (and run scripts you downloaded off of the internet) you need to run without these safeguards. In today’s lesson we are simply launching PowerShell and priming it for the scripts and work we will interact with in the next several lessons.

To get started with PowerShell just click the Start button and type in ISE to locate the Integrated Script Editor in your program listing. Right click and launch PowerShell in Run as Adminsitrator mode:

Enter Get-ExecutionPolicy as a command. If you haven’t used PowerShell yet you will most likely see that the Execution Policy is set to “Restricted”. You’ll need to run the command Set-ExecutionPolicy RemoteSigned to be able to run the scripts and follow along with the lesson. You’ll also get a popup that you’ll have to say yes to.

Your output should look very non-informative like this:

Now that we have that set let’s go to SQLPSX.CodePlex.com and and download the SQLPSX project. It’s currently in version 2.2.1 and consists of 9 modules containing 133 advanced functions, only 2 cmdlets and 7 scripts. It’s for working with ADO.NET, SMO, SQL Agent, RMO, SSIS, SQL script files and using the PowerShell ISE as a SQL query tool:

NOTE: Update at bottom with workaround:

During the installation we will run it without modifying the profile (just yet).

Once you’ve installed it successfully you should now have a WindowsPowerShell directory under your My Documents if you didn’t have one already (which is really important):

After it’s installed we should be able to open up the ISE again and run this command :

Get-Module -ListAvailable

And finally we’re going to get to the step where we load something up. Run import-module SQLServer to load up the main SQL Server module. After you run that command it won’t give you any output telling you that it successfully loaded so you can run this command write-host -foregroundcolor DarkGreen “Modules Loaded: $(get-module)” to have it tell you what Modules it currently has loaded.

Alright that pretty much wraps it up for today. I know we didn’t do a lot with PowerShell and SQL Server yet but this gets some important ground work out of the way so that hopefully everyone is able to script along for the rest of the week. (If you’re dying to read ahead have a look at this post: Basic Querying From PowerShell)

If you run into any issues please leave a comment and I’ll be glad to lend any help that I can. See all you back here tomorrow for the next post in this series Post 1.

UPDATE:

There seems to be an issue with loading the SQLServer Module as I had described. I have found this to be a work-around: Go to \\~\My Documents\WindowsPowerShell\Modules\SQLIse\Modules\ and copy everything except for the SQLISE folder, then go to the \\~\My Documents\WindowsPowerShell\Modules\ folder and paste all of those items. At this point you should now be able to run the command and get it to load up properly.

I will post more when I found out other alternatives.

del.icio.us Tags: SQL Server 2008,PowerShell,SQL University,Modules,Execution Policy,SQLPSX

Get More Done with SQLPSX

Feb 25th, 2010

by Aaron Nelson.

Comments are off for this post

In my LiveMeeting session for the AppDev Virtual Chapter of PASS yesterday I talked about building on top of tools that others had already built for you to use. A great one for any DBA to use is the SQL PowerShell Extensions known as SQLPSX. Even if you’ve never used PowerShell before you should take a look at this. Heck even Sys Admins in shops that don’t have a DBA should have a look at this. I have found the commands I have worked with very easy to use; even easier than T-SQL in some cases.

After you download the files and RTFM you can then you can follow along on this next part. Oh wait, first a little warning straight from Buck Woody:

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

get-module -listAvailable

import-module SQLServer

Invoke-SqlBackup -sqlserver "WINX64ULT7\Kilimanjaro" -dbname "AdventureWorks" `

-filepath "C:\Temp\AdventureWorks_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"

Now this little script here (above) will backup a db for you and even include the current YearMonthDayHourMinute in the file string. This one below will backup all of the non-system databases on your instance. If you’re like me you’re thinking this doesn’t do anything that you can’t already do today with a maintenance plan. That’s true and maybe I should have titled this post “Get Something Done with SQLPSX” but I will build on how you can leverage this more tomorrow. For now, why don’t you add an AND clause only backup all the databases that start with ‘A’

foreach ($dbn in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" `

-database master -serverinstance WIN7\Kilimanjaro )

$k="C:\Temp\" + $($dbn.name) + "_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"WIN7\Kilimanjaro" -dbname $($dbn.name)  -filepath $k

$dbn; Invoke-SQLBackup -sqlserver "

I’ve gone ahead and included the SQLPSX help items here. Please see if there’s one that catches your eye and see if it might work for you.

* Get-AgentAlert

* Get-AgentAlertCategory

* Get-AgentJob

* Get-AgentJobHistory

* Get-AgentJobSchedule

* Get-AgentJobServer

* Get-AgentJobStep

* Get-AgentOperator

* Get-AgentOperatorCategory

* Get-AgentProxyAccount

* Get-AgentSchedule

* Get-AgentTargetServer

* Get-AgentTargetServerGroup

* Set-AgentJobHistoryFilter

* Get-ReplArticle

* Get-ReplEnumLogReaderAgent

* Get-ReplEnumPublications

* Get-ReplEnumPublications2

* Get-ReplEnumSnapshotAgent

* Get-ReplEnumSubscriptions

* Get-ReplEnumSubscriptions2

* Get-ReplLightPublication

* Get-ReplMonitor

* Get-ReplPublication

* Get-ReplPublicationMonitor

* Get-ReplPublisherMonitor

* Get-ReplScript

* Get-ReplServer

* Get-ReplSubscriberSubscription

* Get-ReplSubscription

* Get-ReplTransPendingCommandInfo

* New-ReplMergePublication

* New-ReplScriptOptions

* New-ReplTransPublication

* Add-SqlDatabaseRole

* Add-SqlDatabaseRoleMember

* Add-SqlServerRoleMember

* Get-SqlDatabasePermission

* Get-SqlDatabaseRole

* Get-SqlIndexFragmentation

* Get-SqlInformation_Schema.Columns

* Get-SqlInformation_Schema.Routines

* Get-SqlInformation_Schema.Tables

* Get-SqlInformation_Schema.Views

* Get-SqlLinkedServerLogin

* Get-SqlLogFile

* Get-SqlLogin

* Get-SqlObjectPermission

* Get-SqlServerPermission

* Get-SqlServerRole

* Get-SqlShowMbrs

* Get-SqlStatistic

* Get-SqlStoredProcedure

* Get-SqlSynonym

* Get-SqlSysDatabases

* Get-SqlUserDefinedDataType

* Get-SqlUserDefinedFunction

* Get-SqlVersion

* Get-SqlView

* Invoke-SqlBackup

* Invoke-SqlDatabaseCheck

* Invoke-SqlIndexDefrag

* Invoke-SqlIndexRebuild

* Invoke-SqlRestore

* New-SqlScriptingOptions

* Remove-SqlDatabase

* Remove-SqlDatabaseRole

* Remove-SqlDatabaseRoleMember

* Remove-SqlLogin

* Remove-SqlServerRoleMember

* Remove-SqlUser

* Set-SqlData

* Set-SqlDatabasePermission

* Set-SqlObjectPermission

* Set-SqlServerPermission

* Update-SqlStatistic

* Copy-ISItemFileToSQL

* Copy-ISItemSQLToFile

* Copy-ISItemSQLToSQL

* Get-ISData

* Get-ISItem

* Get-ISPackage

* Get-ISRunningPackage

* Get-ISSqlConfigurationItem

* Set-ISConnectionString

* Set-ISPackage

* Test-ISPath

SQLPSX

Resources for my SQL Server PowerShell Extensions webcast

PowerShell Week at SQL University – Post 1

Getting Started With PowerShell Variables

PowerShell Week at SQL University – Post 0

NOTE: Update at bottom with workaround:

UPDATE:

Get More Done with SQLPSX

Search

Categories

Archives

Search