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"

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 "

}