≡ Menu

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.

Comments on this entry are closed.

%d bloggers like this: