SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

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.

SQLPSX

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

* 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

Comments are closed.