This is another one of the code examples that I did in my PowerShell for Data Professionals session last week. If you give it your computer name and then run it you will get back a stream or properties like I mentioned during the session. get-wmiobject -query ` “Select DeviceID,Size,Freespace from Win32_logicaldisk where drivetype=3″ ` -computer “YourComputerName” Now this runs fine but it brings back quite a bit more information than we really need. I mean we specified columns in our WQL statement and it brought back the kitchen sink almost as if we had given it a SELECT *. Now I don’t know why this happens I just know that it does (I have emailed a few people but haven’t heard back yet). To get around this problemfeature all you have to do is take the output from the command you executed and pipe it to the Select-Object cmdlet and specify the columns that you want again. get-wmiobject -query ` “Select DeviceID,Size,Freespace from Win32_logicaldisk where drivetype=3″ ` -computer “YourComputerName” | select-object DeviceID, Size, FreeSpace And voilà you have now taken something from the pipeline and done something with it that hopefully any SQL person in the world can understand. So what’s next? Well just change -computer again to the name of your favorite Dev server and see how much disk space you have. *Don’t forget the disclaimer about running other people’s scripts! Here’s the link to the msdn article that will have more info on those properties and the methods that you can use against http://msdn.microsoft.com/en-us/library/aa394173(VS.85).aspx
February, 2010:
Speaking at SQLSaturday #33 in Charlotte, NC
I will be speaking at SQLSaturday #33 in Charlotte, NC on March 6. I’m bringing two new sessions with me, “Virtualize This!” and “PowerShell Awesomesauce”. Virtualize This! is going to be sort-of an overview of what options are available with virtualization. I say sort-of because I’m not big on PowerPoint, in fact I’ve only used it twice [in my life]; both times to create slide decks for sessions. While I plan to go through what various options are available I plan to lay it out in relation to a Development or Test environment scenario that is likely to happen at just about any shop around the world if they choose to use virtualization. I had originally planned to deliver this session at SQL Saturday #30 in Richmond but that got snowed out. I guess about the only other thing I can say about this session is that I promise it’s not a sales pitch for virtualization; it’s more like walking through a likely use case for it. PowerShell Awesomesauce is the name that I’m inflicting on people because I just couldn’t come up with anything. I totally blame Jen McCown over at Midnight DBA for getting that word stuck in my head earlier in the day. So anyways I’ve found so many new things to cram into my PowerShell for Data Professionals session and it was overflowing with so much PoSh goodness that I had to break it out into a second session. I haven’t actually picked yet what all will go into this session besides talking about the Snapins but you can expect to see a few of the demos from my session earlier this week plus a few new ones. PowerShell just has so much to offer so I am going to have to figure out what I can make flow together in just the one hour slot that I have. After that (and lunch) I will be doing my PowerShell for Data Professional session. About a third of the content from this session will be split off into my new session, which should hopefully leave enough time to actually finish everything that I wanted to get to this time. In between my own sessions I likely will be catching Is Virtualization is a good choice for SQL Server? by Denny Cherry as well as Automate SQL Server Administration with PowerShell & Gather SQL Server Performance Data with PowerShell both by Allen White. That about wraps it up for this post and I hope to see you there in Charlotte!!
Get More Done with SQLPSX
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 !=0×01″ ` -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 * Get-GroupUser * Get-ShowMbrs * New-ShowMbrs * Set-ShowMbrs * Out-SqlScript * Test-SqlScript * Add-SqlDatabase * Add-SqlDatabaseRole * Add-SqlDatabaseRoleMember * Add-SqlDataFile * Add-SqlFileGroup * Add-SqlLogFile * Add-SqlLogin * Add-SqlServerRoleMember * Add-SqlUser * Get-Sql * Get-SqlCheck * Get-SqlColumn * Get-SqlConnection * Get-SqlData * Get-SqlDatabase * Get-SqlDatabasePermission * Get-SqlDatabaseRole * Get-SqlDataFile * Get-SqlDefaultDir * Get-SqlEdition * Get-SqlErrorLog * Get-SqlForeignKey * Get-SqlIndex * 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-SqlPort * Get-SqlProcess * Get-SqlSchema * Get-SqlScripter * Get-SqlServer * Get-SqlServerPermission * Get-SqlServerRole * Get-SqlShowMbrs * Get-SqlStatistic * Get-SqlStoredProcedure * Get-SqlSynonym * Get-SqlSysDatabases * Get-SqlTable * Get-SqlTransaction * Get-SqlTrigger * Get-SqlUser * 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 * New-ISApplication * New-ISItem * Remove-ISItem * Rename-ISItem * Set-ISConnectionString * Set-ISPackage * Test-ISPath
How Big Were Last Nights Backups?
Last week one of the Sys Admin’s asked me “How big were last night’s backups?” I was like “Why are you asking me? We drop all of the backup files onto one of your arrays so why don’t you tell me.” Background: We’re doing a Data Center move and they wanted to move a copy of all of the dbs over to the new DC to test the applications. In some shops this might be easy because they just delete backups more than a day old. For us, we are blessed with enough room to keep several days worth of backups for some of our critical systems. This is further complicated by the fact that we have over 100 databases to move and they have different retention policies based on importance. I know that the databases take up 1.8 TB thanks to another PowerShell script I wrote but since we use backup compression our data is about a fifth of that size. But what is the exact number?! I’m glad you asked. I threw together a script I had used previously and modified it so that would recurse down the directory structure of our db archive server and pickup on .bak files that were made in the last day. Now, because nothing is simple where I work, there is a software product – which will remain nameless for now – that also backs up transaction log files with the .bak extension; but luckily also throw in _TLOG_ so I was able to filter them out pretty easily. 1: $c=0 2: $l=0 3: foreach ($f in dir -include *.bak -exclude *TLOG* -recurse | 4: where{ $_.LastWriteTime -gt [datetime]::Now.AddDays(-1) } ) 5: {$c += 1; $l += $f.length} 6: “Total File Count: $c and total size of .bak files in Bytes: $l” 7: >> c:\Temp\MyBackupFileSizes.txt Now I was already in a directory where I wanted to get all the files in all of the sub-directories when I ran this so don’t forget to do that or this could run for a long time. For you that might look like this (but probably won’t): C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Backup. I went and checked a bunch of these file sizes manually and they came out exactly correct If you’re new to PowerShell I hope you give this a try and come up with a way that it could be useful to you.
Basic SQL Querying From PowerShell
I promise to make this worth your time. (Oops, I better make this quick.) Alright so with PowerShell if you open it up and just run this code you’ll be able to query your database right from the start. Anyone who has worked with data systems for a while will notice right away the first line of this script kinda looks like a connection string for an application. I’m sure the fact that PowerShell was built on .Net has something to do with that. (But I’m not a developer so I don’t really know, it could be just a coincidence for all I know. $conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=YourMachineName\YourInstanceName; InitialCatalog=master; Integrated Security=SSPI”) $conn.Open() $cmd1 = $conn.CreateCommand() $cmd1.CommandType = [System.Data.CommandType]::StoredProcedure $cmd1.CommandText =“sp_databases” $data = $cmd1.ExecuteReader() $dt = new-object “System.Data.DataTable” $dt.Load($data) $dt | format-table #PipeLining is Awesome! $conn.Close() But this code is so much Nicer! If you’ve got SSMS 2008 installed on your machine all you need to do is fire up the ISE and add the SnapIn like I talked about in my last blog. Then you can use invoke-sqlcmd and pass it your query. As you see this is a lot easier and cleaner than all of the code above was. #Go ahead and add the SQL Snapins add-pssnapin SqlServerCmdletSnapin100 #sp_databases | format-table invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table Conclusion: Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the Snapin and use invoke-sqlcmd.
Bite Sized Blog Posts
For the next several weeks (about 9) I will be really busy with a data center move and doing some presentations on PowerShell. During that time I’m sure I’ll find all kinds of cool uses for PowerShell and SQL Server and I’ll post them as quick as I can in a small bite-sized format. This will make it quick and easy for people to read and for me it will mean much less time to get a post ready. Sometimes when getting a blog post ready I will take as many a 8 screen shots of the same thing just trying to make sure that I got everything easy to understand, syntactically correct, and useful. Once I’m done with all this (around May) I will come back and thread these posts together into a bigger story and show you how much time we saved from end to end. So far I already have about 3 of the bite-sized posts that I need to write and we’ve saved at least 4 hours on tasks that previously would have required loads of manually clicking around. I hope this format helps people easily take advantage of what I’ve found useful between leveraging SQL and PowerShell. btw… If you’re looking to learn more about PowerShell during your daily commute I HIGHLY recommend that you subscribe to the PowerScripting Podcast. My coworker Shyam and I are catching up on back episodes and listening to it nearly every day on the way into work. They don’t talk about SQL Server too much but what they do talk about is still very useful to us.
SQLServer:\SQL\Databases\Tables> Dir
Getting started talking to your SQL Servers in the PowerShell 2.0 ISE By now hopefully you’ve noticed that SQL Server 2008 comes with PowerShell all over the place. Just about anything you click on in Object Explorer has “Start PowerShell” and you can even execute PowerShell steps in SQL Agent Jobs. You may be wondering why on Earth you would want to “Start PowerShell” in the first place. I’ll show you one thing today and then expand on that in later blog posts but today I wanted to tell you how to run PowerShell in something a little more organized than a command prompt window. The first thing I’d like you to do is open up SSMS, connect to an instance and Right-Click the Databases folder > then select Start PowerShell. A command prompt window should open up and from there you can talk to your SQL Server instance as if was a drive on your machine: The SQLPS window that just opened up is PowerShell 1.0 with a special ‘expansion pack’ that allows it to talk to SQL Server in a way that PowerShell alone can’t. So now that we’ve got that covered I want to show you how to do the same thing in PowerShell 2.0’s ISE. Go to Start > All Programs > Accessories > Windows PowerShell > Windows PowerShell ISE Copy the code from the bottom and step through it like I did in this picture. When you’re done you will be able to drill down to tables like they were just folders directories on your hard drive. If you aren’t running Windows 7 (upgrade already!) check this post and see if you’re operating system is eligible. When you get to the part where you do “get-psdrive” for the second time you should now see the SQL Server on your local machine. If you haven’t figured out yet, this is only going to work if you have SQL Server installed on your local machine. #Before get-psdrive #Snapins that are running get-pssnapin #Snapins that you can load get-pssnapin -registered #Go ahead and add the SQL Snapins add-pssnapin SqlServerCmdletSnapin100 add-pssnapin SqlServerProviderSnapin100 #New Resource get-psdrive #What can we do with that? cd SQLSERVER:\SQL\YourComputerName\YourInstanceName\DATABASES\ADVENTUREWORKS\TABLES Now that we have done all of that we can do something that I have seen mentioned several places but I think that Allen White’s blog post explains it the best (here’s my version of it). You can now create a variable and populate it with one the DDL structures in your database and from there generate a Create script for it like so: $PTH = get-item Production.TransactionHistory $PTH.Script() Your output should look something like this: PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory $PTH.Script() SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE TABLE [Production].[TransactionHistory]( [TransactionID] [int] IDENTITY(100000,1) NOT NULL, [ProductID] [int] NOT NULL, [ReferenceOrderID] [int] NOT NULL, [ReferenceOrderLineID] [int] NOT NULL, [TransactionDate] [datetime] NOT NULL, [TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Quantity] [int] NOT NULL, [ActualCost] [money] NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] And that’s it for this edition of how to do something when you open up the PowerShell 2.0 ISE. Next we’ll do something more useful I promise.
PASS Virtual Chapter Late February Presentations
PASS AppDev Presents: PowerShell for Data Professionals For this session we have partnered with SQL User Groups on the other side of the planet to do multiple live sessions in time slots that are convenient for them. If you would like your User Group to partner with us on a time slot for a future session please contact us here: appdev_DL@sqlpass.org Date: Start Time: End Time: Time Zone: Add to your calendar 2/23/2010 12:00 PM 1:00 PM GMT+13:00 Click Here 2/23/2010 12:30 PM 1:30 PM GMT+10:30 Click Here 2/23/2010 12:00 PM 1:00 PM GMT-5 Click Here Event Description: In this session we will learn to perform several everyday data tasks including user database backups, scripting table objects and evaluating disk space usage with PowerShell. For each task we will explore the benefits of using PowerShell over the standard method. Only a basic understanding of PowerShell or DOS is needed. This session should serve as a good introduction to PowerShell for database users. The goal is to help everyone get their feet wet using PowerShell. Aaron Nelson: Aaron is a Senior SQL Server Architect with over 10 years experience in architecture, business intelligence, development, and performance tuning of SQL Server. He has experience managing enterprise-wide data needs in both transactional and data warehouse environments. He also holds certifications for MCITP: Business Intelligence Developer, Database Administrator, Database Developer; as well as MCTS: Windows Server Virtualization, Configuration (meaning Hyper-V). Aaron blogs at SQLvariations How do I view the presentation? Attendee URL:� Live Meeting Link (Auckland, NZ) Live Meeting link (Australia) Live Meeting link (Eastern US Time Zone) ____________________________________________________________________________________________________ PASS DBA VC Presents: SQL Server Locking & Blocking Made Simple Date: Start Time: End Time: Time Zone: Add to your calendar 2/10/2010 12:00 PM 1:00 PM GMT-5 Event Description: A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times. Joe Webb: Joe Webb, a Microsoft SQL Server MVP, serves as Chief Operating Manager for WebbTech Solutions, a Nashville-based consulting company. He has over 14 years of industry experience and has consulted extensively with companies in the areas of software development, database design, and technical training. Live Meeting Information: Attendee URL: Live Meeting Link