SQLPSX 1.5 Release

I completed Release 1.5 of SQLPSX which adds 31 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 ‘Z002SqlExpress’ 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 ‘Z002SqlExpress’

#Return log and data directory information:
Get-SqlDefaultDir ‘Z002SqlExpress’

#Create a new database
Add-sqldatabase ‘Z002SqlExpress’ test

#Remove a database
Remove-sqldatabase ‘Z002SqlExpress’ test

#Add a WindowsGroup login
add-sqllogin ‘Z002SqlExpress’ ‘Z002TestGrp1’ -logintype ‘WindowsGroup’

#Add a SqlLogin
add-sqllogin ‘Z002SqlExpress’ test5 test5 -logintype ‘SqlLogin’

#Add a Windowsuser login
add-sqllogin ‘Z002SqlExpress’ ‘Z002testuser1’ -logintype ‘WindowsUser’

#Add a User
add-sqluser ‘Z002SQLEXPRESS’ pubs test5

#Add Windows user
add-sqluser ‘Z002SQLEXPRESS’ pubs ‘testuser1’ ‘Z002testuser1’

#Remove a user
remove-sqluser ‘Z002SQLEXPRESS’ pubs ‘testuser1’

#Remove a login
remove-sqllogin ‘Z002SqlExpress’ test6

#Add a role member to the bulkadmin server role
add-sqlserverrolemember ‘Z002SqlExpress’ ‘test5’ bulkadmin

#Remove a role member from the bulkadmin server role
remove-sqlserverrolemember ‘Z002SqlExpress’ ‘test5’ bulkdmin

#Add a database role
add-sqldatabaserole ‘Z002SqlExpress’ pubs testrole3

#Remove a database role
remove-sqldatabaserole ‘Z002SqlExpress’ pubs testrole3

#Add a database role member
add-sqldatabaserolemember ‘Z002SqlExpress’ pubs test5 testrole3

#Remove a database role member
remove-sqldatabaserolemember ‘Z002SqlExpress’ pubs test5 testrole3

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

#Return current processes
Get-SqlProcess ‘Z002SqlExpress’ | ft

#Return active transaction in the tempdb database
get-sqltransaction ‘Z002SqlExpress’ tempdb

#Return the current ErrorLog
get-sqlerrorlog ‘Z002SqlExpress’

#Set server level permission
set-sqlserverpermission ‘Z002SqlExpress’ AlteAnyLogin test5 Grant

#Set database level permission
set-sqldatabasepermission ‘Z002SqlExpress’ pubs CreateTable test5 Grant

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

$server = Get-SqlServer ‘Z002SqlExpress’
invoke-sqlbackup ‘Z002SqlExpress’ ‘pubs’ $($server.BackupDirectory + "pubs.bak")
invoke-sqlrestore ‘Z002SqlExpress’ ‘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
        Performs the equivalent of a DBCC CHECKDB
        Performs a reindex
        Returns index fragmentation similar to DBCC SHOWCONTIG
        Defragments an index. Performs the equivalent of a DBCC INDEXDEFRAG
        Updates statistics
        Adds a new database to a SQL Server
        Removes a database from a SQL Server
        Adds a new filegroup to a database
        Adds a new datafile to a filegroup
        Adds a new logfile to a database
       Returns the default location for data and log files for a SQL Server
        Adds a new user to a database
        Removes a user from a database
        Adds a login to a SQL Server
        Removes a login from a SQL Server
        Adds a login to a server role
        Removes a login from a server role
        Adds a new database role to a database
        Removes a database roel from a database
        Adds a user or role to a database role
        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
        Returns the SQL Server Errorlog
        Returns a SMO Schema object with additional properties
        Returns the current proccesses on a SQL Server. Equivalent to sp_who
        Returns the current open transactions for a database
        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.  

This entry was posted in PowerShell. Bookmark the permalink.