SQLPSX Release 1.5

Coordinator
Mar 28, 2009 at 5:48 PM
Edited Apr 8, 2009 at 12:51 PM
I completed Release 1.5 of SQLPSX which adds 30 new functions for working with database maintenance (CHECKDB, Index rebuilds, backup and restore) as well as login, user, role and permission management. With this release there are now 104 total functions, 2 cmdlets and 12 scripts around SMO, Agent, RMO, and SSIS.
 
Here's a few examples working with database maintenance functions:


#Get a database object

$db = get-sqldatabase 'Z002\SqlExpress' pubs

#Run a checkdatabse
invoke-sqldatabasecheck $db
$db | invoke-sqldatabasecheck

#Get index defrag information for all indexes
$db | get-sqltable | get-sqlindex | get-sqlindexfragmentation

#Run an index defrag operation against all indexes
$db | get-sqltable | get-sqlindex | invoke-sqlindexdefrag

#Run an reindex operation against all indexes
$db | get-sqltable | get-sqlindex | invoke-sqlindexrebuild

#Run an update statistics operations against all statistics
$db | get-sqltable | get-sqlstatistic | update-statistic

#Get a server object
$server = Get-SqlServer 'Z002\SqlExpress'

#Return log and data directory information:
Get-SqlDefaultDir 'Z002\SqlExpress'

#Create a new database
Add-sqldatabase 'Z002\SqlExpress' test

#Remove a database
Remove-sqldatabase 'Z002\SqlExpress' test

#Add a WindowsGroup login
add-sqllogin 'Z002\SqlExpress' 'Z002\TestGrp1' -logintype 'WindowsGroup'

#Add a SqlLogin
add-sqllogin 'Z002\SqlExpress' test5 test5 -logintype 'SqlLogin'

#Add a Windowsuser login
add-sqllogin 'Z002\SqlExpress' 'Z002\testuser1' -logintype 'WindowsUser'

#Add a User
add-sqluser 'Z002\SQLEXPRESS' pubs test5

#Add Windows user
add-sqluser 'Z002\SQLEXPRESS' pubs 'testuser1' 'Z002\testuser1'

#Remove a user
remove-sqluser 'Z002\SQLEXPRESS' pubs 'testuser1'

#Remove a login
remove-sqllogin 'Z002\SqlExpress' test6

#Add a role member to the bulkadmin server role
add-sqlserverrolemember 'Z002\SqlExpress' 'test5' bulkadmin

#Remove a role member from the bulkadmin server role
remove-sqlserverrolemember 'Z002\SqlExpress' 'test5' bulkdmin

#Add a database role
add-sqldatabaserole 'Z002\SqlExpress' pubs testrole3

#Remove a database role
remove-sqldatabaserole 'Z002\SqlExpress' pubs testrole3

#Add a database role member
add-sqldatabaserolemember 'Z002\SqlExpress' pubs test5 testrole3

#Remove a database role member
remove-sqldatabaserolemember 'Z002\SqlExpress' pubs test5 testrole3

#Get schemas from a database
$db | get-sqlschema
$db | get-sqlschema -name dbo

#Return current processes
Get-SqlProcess 'Z002\SqlExpress' | ft

#Return active transaction in the tempdb database
get-sqltransaction 'Z002\SqlExpress' tempdb

#Return the current ErrorLog
get-sqlerrorlog 'Z002\SqlExpress'

#Set server level permission
set-sqlserverpermission 'Z002\SqlExpress' AlteAnyLogin test5 Grant

#Set database level permission
set-sqldatabasepermission 'Z002\SqlExpress' pubs CreateTable test5 Grant

#Set object level permission
$db | get-sqlschema -name dbo | set-sqlobjectpermission -permission Select -name test5 -action Grant

#Backup/restore
$server = Get-SqlServer 'Z002\SqlExpress'
invoke-sqlbackup 'Z002\SqlExpress' 'pubs' $($server.BackupDirectory + "\pubs.bak")
invoke-sqlrestore 'Z002\SqlExpress' 'pubs' $($server.BackupDirectory + "\pubs.bak") -force

The complete list of new functions added in the 1.5 Release:

 
   Invoke-SqlBackup (Database,Log)
        Performs a SQL Backup
    Invoke-SqlRestore (Database, Log)
        Performs a SQL Restore
    Invoke-SqlDatabaseCheck
        Performs the equivalent of a DBCC CHECKDB
    Invoke-SqlIndexRebuild
        Performs a reindex
    Get-SqlIndexFragmentation
        Returns index fragmentation similar to DBCC SHOWCONTIG
    Invoke-SqlIndexDefrag
        Defragments an index. Performs the equivalent of a DBCC INDEXDEFRAG
    Update-SqlStatistic
        Updates statistics
    Add-SqlDatabase
        Adds a new database to a SQL Server
    Remove-SqlDatabase
        Removes a database from a SQL Server
    Add-SqlFileGroup
        Adds a new filegroup to a database
    Add-SqlDataFile
        Adds a new datafile to a filegroup
    Add-SqlLogFile
        Adds a new logfile to a database
    Get-SqlDefaultDir
       Returns the default location for data and log files for a SQL Server
    Add-SqlUser
        Adds a new user to a database
    Remove-SqlUser
        Removes a user from a database
    Add-SqlLogin
        Adds a login to a SQL Server
    Remove-SqlLogin
        Removes a login from a SQL Server
    Add-SqlServerRoleMember
        Adds a login to a server role
    Remove-SqlServerRoleMember
        Removes a login from a server role
    Add-SqlDatabaseRole
        Adds a new database role to a database
    Remove-SqlDatabaseRole
        Removes a database roel from a database
    Add-SqlDatabaseRoleMember
        Adds a user or role to a database role
    Remove-SqlDatabaseRolemember
        Removes a user or role from a database role
    Set-SqlServerPermission (GRANT, REVOKE, DENY)
        Sets server level permissions to a login
    Set-SqlDatabasePermission (GRANT, REVOKE, DENY)
        Sets database level permissiosn to a user or role
    Set-SqlObjectPermission (GRANT, REVOKE, DENY)
        Sets database object level permissions to a user or role
    Get-SqlErrorLog
        Returns the SQL Server Errorlog
    Get-SqlSchema
        Returns a SMO Schema object with additional properties
    Get-SqlProcess
        Returns the current proccesses on a SQL Server. Equivalent to sp_who
    Get-SqlTransaction
        Returns the current open transactions for a database
    Get-SqlEdition
        Returns the SQL Server edition

SQLPSX 1.5 marks what I consider the first feature complete release. I believe the most common database administration tasks can be accomplished using the 107 functions/cmdlets provided in SQLPSX. If you feel something is missing please post to the discussion forum.

 

With Release 1.5 complete, I'm starting work on the 1.6 Release which will include two enhancements. First, re-implement LibraryShowmbrs as a compiled V1 cmdlet primarily to remove a dependency on WMIC. Second, replace LibrarySSIS with a proper PSProvider so that SSIS packages using a SQL Server store can be navigated and modified using a drive analogy. My goal is to the 1.6 release be the last Powershell V1 release. I’m considering using a mix of compiled and script based functions for Release 2.0 and will re-implement the remaining script-based functions as advanced functions in Powershell V2. I know the lack of help files has become an issue as the function libraries have grown and become more complex. The main benefit moving script-based functions to V2 will provide is the ability to have help files just like compiled cmdlets.